List Table's Multiple Row info as Column in Query

[goods]

I have a table with amounts associated with an account per vendor. Somewhat like this:

VendorID Amount Account
1 8.24 2
2 4.39 1
2 12.69 2
3 1.98 2
1 3.67 2
2 14.87 3

I need to create a report query that will order the data so:
Vendor Sum(Account1) Sum(Account2) Sum(Account3)
1 11.91
2 4.39 12.69 14.87
3 1.98

I have used the Group By clause to group the Vendors and sum up the amounts but I have not been able to get the multiple account information on one row per vendor. Is this possible in one query? Thanks

W. Goodson

 

[Amiel]

Show us the query you're using.

Amiel
amielzz@netscape.net

 

[goods]

SELECT VendorID, Account, Sum(Amount) AS Total
FROM TimeRecord
GROUP BY VendorID, Account1
ORDER BY VendorID

This gives me the information I need but puts the Total on multiple VendorID rows (1 row for each account). I need to have one row per vendor with the totals for each account. I hope this helps explain it. Thanks

W. Goodson

 

[Amiel]

The display of repeated (duplicated) 'left side' data for each record on the 'right side' of a join is the conventional and expected result.


Using the existing query:
SELECT VendorID, Account, Sum(Amount) AS Total
FROM TimeRecord
GROUP BY VendorID, Account1
ORDER BY VendorID

you might create another query...

[red]"SELECT Account, Total from TheQueryAbove"[/red]

Gets the results you wanted !
Amiel
amielzz@netscape.net