Request is the parent table that contains high-level information about what country is associated with what request number.
LineItem is the next table down. One or more LineItem records can make up a Request.
SubItem is the last table in the hierarchy, and one or more SubItems can make up a LineItem. This is where the actual funding amounts are kept.
What I'm looking to do is set up a query that will total the funds allocated to each event. So if a country has 3 events scheduled, I want to be able to get the sum of all SubItems of all LineItems of each event.
For instance, Event 1 is made up of two LineItems (1 and 2). LineItem 1 is made up of two SubItems ($500 and $1000), and LineItem 2 is made up of one SubItem ($300). In my query I'm looking to return $1800 for Event 1, because it is the sum of all SubItems that make up Event 1.
Here's what I've set up so far:
1) I have one query set up so that it returns all results for Request_PK in table Request associated with a single country. Greece, for instance, displays 21 events in the resulting recordset.
SELECT [Request_PK] FROM [myDB].[dbo].[Request] WHERE [CountryCode] = 'Greece'
2) I have a separate query that uses the LineItem number to display the sum of all matching dollar amounts in the SubItem table. So if one of Greece's LineItem numbers is 1234, I'm going to see the dollar amount sum of the SubItems associated with LineItem 1234.
SELECT * FROM [myDB].[dbo].[SubItem] WHERE [LineItem_FK] = (SELECT [LineItem_PK] FROM [myDB].[dbo].[LineItem] WHERE [Request_FK] = 1234)
What I'm looking to do is find a way to return all dollar amounts for each event in the first query. So if the first query returns two event numbers-- 1234 and 1235-- I'll have a total dollar amount next to both numbers: the amount next to 1234 will be the sum of all its associated SubItems and LineItems, and the amount next to 1235 will be the sum of all its SubItems/LineItems. I'm not sure if this is going to require some more advanced SQL syntax like JOINs or UNIONs, so if you have any experience, I'll welcome it gratefully.
In short, right now I have one query that returns 1234 and one query that returns $1800. What I'd like to do is combine them so I have one query that returns 1234 for the first field and $1800 for the second field.
Many thanks, and I appreciate your help!
This post has been edited by rhett.moeller: 26 May 2009 - 07:48 PM