Confusing Aggregate Query

[pkahlo]

I need some help formulating a query. Here is the table structure:

Package Components
PackageComponentID
RequestID
ProjectID
ItemID
Quantity

Each record in the table will either have a RequestID or a ProjectID, but never both. I want to get the sum of the quantity for each item and separated by RequestID and ProjectID, and have them in the same row. Example of some rows I want returned:

SampleItem1, SUM of Quantity that have RequestID, SUM of Quantity that have ProjectID
SampleItem2, SUM of Quantity that have RequestID, SUM of Quantity that have ProjectID


Can anyone help me out here? I can't seem to get this to happen in the same row, this is all I can get:

SampleItem1, SUM of Quantity that have RequestID
SampleItem1, SUM of Quantity that have ProjectID
SampleItem2, SUM of Quantity that have RequestID
SampleItem2, SUM of Quantity that have ProjectID


Thanks!
Patrick

 

[lespaul]

Create and save these three queries:

qryRequestIDs

SELECT PackageComponentID, RequestID, Sum(Quantity) As RequestSum WHERE REQUESTID <> ''

qryProjectIDs

SELECT PackageComponentID, ProjectID, Sum(Quantity) As ProjectSum WHERE PROJECTID <> ''

qryTotals

SELECT * from qryRequestIDs INNER JOIN qryProjectIDs on qryRequestIDs.PackageComponentID = qryProjectIDs.PackageComponenID

You should be able to just open the qryTotals and get the correct results.

Leslie

 

[pkahlo]

Thanks Lespaul!

You seem to have me in the right direction, but I'm still not there yet.

qrtTotals did not return any results. When I removed the PackageComponentID field from the first two (this field is the primary key, so it was not aggregating the function correctly) and joined these two queries on the ItemID field I think I got the results I wanted. However, the results only show Items that both had records with ProjectIDs and RequestIDs. I have many items that would only show a total in one of those fields and they do not show.

Any suggestions?

Thanks,
Patrick

 

[pkahlo]

I've seemed to be able to do this using two correlated subqueries. But I'd welcome any other suggestions!!

Thanks!
Patrick