Union All SQL Totals Sum of...

[MackMan]

Hello All. I once again seek your knowledge... This time with SQL (Honestly, not one of my strong points, but getting there)

I have two tables, joined together with a Union query, and what I'd like out of it is a Sum of Categories & Sub Categories, based on all days this year.

When run on their own, they give me exactly what I need, but when used with UNION ALL, I have duplicate "Sub Category" Entries...

One from one side of the query, and one from the other...
Is there anyway this can be put on just the one line?

the SQL I'm using is this...

SELECT tblTopLines.Category, tblTopLines.SubCategory, Sum(tblTopLines.Credit) AS SumOfCredit, Sum(tblTopLines.Debit) AS SumOfDebit
FROM tblTopLines
WHERE (((tblTopLines.TransDate) Between #1/1/2015# And #12/31/2015#))
GROUP BY tblTopLines.Category, tblTopLines.SubCategory
HAVING (((tblTopLines.Category)="Car"))
UNION ALL
SELECT tblSplits.Category, tblSplits.SubCategory, Sum(tblSplits.Credit) AS SumOfCredit, Sum(tblSplits.Debit) AS SumOfDebit
FROM tblSplits
WHERE (((tblSplits.TransDate) Between #1/1/2015# And #12/31/2015#))
GROUP BY tblSplits.Category, tblSplits.SubCategory
HAVING (((tblSplits.Category)="Car"));

As I said, SQL isn't my thing, but every stone unturned is another feather and all that...

As always, Super thanks for your help.

 

[pbaldy]

Are you aware that UNION used alone (without ALL) eliminates duplicates?

 

[MackMan]

I didn't know that but do now, but the duplicate is still there

I did say SQL wasn't my strong point

 

[pbaldy]

Access isn't seeing them as duplicates I guess. What does the result look like? An option would be to build a totals query on top of the union query.

 

[MackMan]

here's what I'm getting..

So perhaps take out the totals query, and do this separately?

I'll do some investigating...

 

[pbaldy]

Looks like you deleted a follow-up post that said this was solved. Is it not?

 

[MackMan]

Sadly not. Think I jumped the gun a little bit there. I'm working on it still

I will post my resolution when I have it. I shall not be beaten!!

 

[MackMan]

Sorted. In the end, I did use UNION ALL, but modified the aggregate in the nested query as thus...

SELECT Q1.Category, Q1.SubCategory, Sum(Q1.Credit) AS SumOfCredit, Sum(Q1.Debit) AS SumofDebit
FROM (SELECT TransDate, Category, SubCategory, Credit, Debit
FROM TblTopLines
UNION ALL
SELECT TransDate, Category, SubCategory, Credit, Debit
FROM TblSplits
) AS Q1
WHERE (((Q1.TransDate) Between #1/1/2015# And #12/31/2015#))
GROUP BY Q1.Category, Q1.SubCategory
HAVING (((Q1.Category)="Car"));

It works.. I'm happy with that!

As always, Thanks PB for your help!

 

[pbaldy]

Glad you got it sorted.

 

[spikepl]

Errrr .. your criterion

HAVING (((Q1.Category)="Car"));

should be tagged onto the end of your WHERE clause.

WHERE selects the rows to operate on, HAVING selects the desired aggregate results.

Since you aren't aggregating Q1.Category then that is just a result of a quirk of the query designer. If you have a WHERE criterion in a aggregate query, then in teh qeury designer you need to drag the field onto the grid one more time, select WHERE and untick the checkbox. If you just add the criterion to the original column, you'll get the undesired HAVING.