Select Query Sum Problem 1

[rdgskier8]

Hey everyone,
I'm having trouble with my select queries. I have two queries - each with dollar values sorted by year and creditsection. I have another select query trying to add them together, but instead it adds every value to every value rather than adding queryA's 2004's credit section to queryB's 2004's credit section, etc.

The third select query has the Year and CreditSection as groupby, the two dollar amounts as Sum, and the addition of them as Expression... the only relation I put between the queries is Year-Year. If I put CreditSection-CreditSection, it only shows the CreditSection that appears in both (the years will always be the same in both queries).

If you have any suggestions, I'd really appreciate any help.

Thanks in advance,
Ryan

 

[PHV]

Any chance you could post your tables schemas and your actual SQL code with sample input and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rdgskier8]

Note: QueryA and QueryB are each dependant upon one different query.

QUERYA:

SELECT [qryP2-Dol-1].Year, Sum([qryP2-Dol-1].Dol) AS SumOfDol, [qryP2-Dol-1].CreditSection
FROM [qryP2-Dol-1]
GROUP BY [qryP2-Dol-1].Year, [qryP2-Dol-1].CreditSection;

QUERYB:

SELECT [qryP2-Dol-2].[Year], [qryP2-Dol-2].[CreditSection], Sum([qryP2-Dol-2].[Cash]) AS SumOfCash
FROM [qryP2-Dol-2]
GROUP BY [qryP2-Dol-2].[Year], [qryP2-Dol-2].[CreditSection];

QUERYC (Adds A & B):

SELECT [qryP2-Dol-1m].Year, [qryP2-Dol-1m].CreditSection, Sum([qryP2-Dol-1m].SumOfDol) AS SumOfSumOfDol, Sum([qryP2-Dol-2m].SumOfCash) AS SumOfSumOfCash, Sum([Sumofdol]+[sumofcash]) AS D
FROM [qryP2-Dol-1m] INNER JOIN [qryP2-Dol-2m] ON [qryP2-Dol-1m].Year = [qryP2-Dol-2m].Year
GROUP BY [qryP2-Dol-1m].Year, [qryP2-Dol-1m].CreditSection;

Table view in Query A:
YEAR SumOfDol CreditSection
2004 38 50 G/S Armatures
2005 40 50 G/S Armatures
2005 37 50 G/S Stators


Table view in Query B:
YEAR CreditSection SumofCash
2004 50 G/S Armatures 262.7
2004 Taylor St. 782.7
2005 50 G/S Armatures 480
2005 Taylor St. 431.7

**As you can see, there are different credit sections, and I'm trying to get the third query to differentiate them but still add them together if they're the same credit section. Here's what I WANT in Query C, but I can't get:

YEAR CreditSection DOLLAR
2004 50 G/S Armatures *sum*
2004 50 G/S Stators *sum*
2004 Taylor St. *sum*
2005 50 G/S Armatures *sum*
2005 50 G/S Stators *sum*
2005 Taylor St. *sum*



Does this help any? Thanks for your quick response.

 

[PHV]

Which sum do you want in the DOLLAR field of QueryC ?
I guess you want to aggregate QueryA UNION QueryB.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rdgskier8]

For example, in queryC, for 2004 50 G/S Armatures SUM, considering the data above, it would be 38+262.7 -> 300.7

Do I want to use a UNION, then? Thanks, PHV.

 

[PHV]

You may try something like this:
SELECT [YEAR], CreditSection, Sum(Amount) AS DOLLAR
FROM (
SELECT [YEAR], SumOfDol AS Amount, CreditSection FROM [Query A]
UNION ALL SELECT [YEAR], SumofCash, CreditSection FROM [Query B]
) AS U
GROUP BY [YEAR], CreditSection;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rdgskier8]

PHV, you're a pro. Thank you very much! I have to double check the rest of my queries to make sure I'm getting the right values, but I got 300.7!

Have a star... again. =)