Select from where 2

[rzff]

If wonder if somebody can help me with two queries if have a table named holdings.


ID TotalCap Accname Accnumber
1 150000
1 175000
1 Brown
1 123
2 155000
2 195000
2 200000
2 Yellow
2 4567

The result should be

IDCapital(1) Capital(2) Capital(3) Accname Accnumber
1 150000 175000 Brown 123
2 155000 195000 200000 Yellow 4567

The second query will have to translate the Accountnumber to the New Account number

This tabel is named convertaccount

Accnumber NewAccount
123 321
4567 8453

The result should be

IDCapital(1) Capital(2) Capital(3) Accname Accnumber
1 150000 175000 Brown 321
2 155000 195000 200000 Yellow 8453

 

[Steve101]

You'll struggle doing the first bit in a query. Writing some code would be a better option. Post back if you need some help on doing this.

Might I ask however, what it is that you are trying to achieve? On the face of things, I would question the general architecure of your first table. It is not 'normalised', and this is the reason you'd be struggling to determine a query to re-organise the data.

Suggest you consider re-organising the way the data is held, if this is an option. If its not (eg. you've inherrited an existing system or spreadsheet), then post back, and we'll help out with some code. If you need further hints on an improved design (if this is an option), then also post back,

Cheers

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[rzff]

The tabel I am showing is just an example. This is the result when I import an XML after I imported it in excel.
I would like to have a query so I can make the adjustments myself.

But if you have any suggestions please let me know. I will post you the a sample spreadsheet. The imported sheet helds to much data.

regards

rzff

 

[rzff]

Also this result would be perfect

IDCapital Accname Accnumber
1 150000 Brown 321
1 175000 Brown 321
2 155000 Yellow 8453
2 195000 Yellow 8453

Or any other result which shows the information in a more elegant way

Regards

RZFF

 

[jobo123]

Hi RZFF,

I built your tables and added the data. I made the following query (where I named your first table 'Table', and the second table 'convertaccount' as you indicated).

In the SQL below, Table_1, Table_2 and Table_3 are not new tables, just the original table, linked 3 times in the same query (with left and right joins). I did this to bridge the gaps across the rows in the data:

SELECT Table.ID, Table_1.TotalCap, Table_3.Accname, convertaccount.NewAccount
FROM (

AS Table_3 RIGHT JOIN ((

LEFT JOIN

AS Table_1 ON Table.ID = Table_1.ID) LEFT JOIN

AS Table_2 ON Table.ID = Table_2.ID) ON Table_3.ID = Table.ID) LEFT JOIN convertaccount ON Table_2.Accnumber = convertaccount.Accnumber
GROUP BY Table.ID, Table_1.TotalCap, Table_2.Accnumber, Table_3.Accname, convertaccount.NewAccount
HAVING (((Table_1.TotalCap) Is Not Null) AND ((Table_2.Accnumber) Is Not Null) AND ((Table_3.Accname) Is Not Null));

This gives the result you suggested in your 3rd posting:

1 150000 Brown 321
1 175000 Brown 321
2 155000 Yellow 8453
2 195000 Yellow 8453

plus this row that I think you might have left out:

2 200000 Yellow 8453

Cheers


John Davy-Bowker
www.dbLetterWriter.com

 

[rzff]

PERFECT


I tried it and it worked. I will do some more testing tonight.

Thanks