Don't add up

[Mike Hughes]

I have this query and when run it returns the incorrect total for DISTRICT OFFICE. Here is the query and below that are the results for each line. Would someone tell me what I'm doing wrong please?

SELECT
[CASE TYPE].DO,
Format(Now(),"mmmm yyyy") AS [MONTH],
[TANF EST O].[TANF O],
[TANF EST P].[TANF P],
[TANF EST S].[TANF S],
[TANF TOTALS].[TANF TOTALS],
[UNKNOWN CASE TYPE EST O].[UNKNOWN O],
[UNKNOWN CASE TYPE EST P].[UNKNOWN P],
[UNKNOWN CASE TYPE EST S].[UNKNOWN S],
[UNKNOWN TOTALS].[UNKNOWN TOTALS],
[FC EST O].[FC O],
[FC EST P].[FC P],
[FC EST S].[FC S],
[FOSTER CARE TOTALS].[FOSTER CARE TOTALS],
[MEDICAL EST O].[MEDICAL O],
[MEDICAL EST P].[MEDICAL P],
[MEDICAL EST S].[MEDICAL S],
[MEDICAL TOTALS].[MEDICAL TOTALS],
[NON TANF TYPE EST O].[NON TANF O],
[NON TANF TYPE EST P].[NON TANF P],
[NON TANF TYPE EST S].[NON TANF S],
[NON TANF TOTALS].[NON TANF TOTALS],
SUM ([TANF EST O].[TANF O]+
[TANF EST P].[TANF P]+
[TANF EST S].[TANF S]+
[UNKNOWN CASE TYPE EST O].[UNKNOWN O]+
[UNKNOWN CASE TYPE EST P].[UNKNOWN P]+
[UNKNOWN CASE TYPE EST S].[UNKNOWN S]+
[FC EST O].[FC O]+
[FC EST P].[FC P]+
[FC EST S].[FC S]+
[MEDICAL EST O].[MEDICAL O]+
[MEDICAL EST P].[MEDICAL P]+
[MEDICAL EST S].[MEDICAL S]+
[NON TANF TYPE EST O].[NON TANF O]+
[NON TANF TYPE EST P].[NON TANF P]+
[NON TANF TYPE EST S].[NON TANF S]) AS [DISTRICT TOTAL] INTO REPORT

FROM [TANF EST O], [TANF EST P], [TANF EST S], [TANF TOTALS], [CASE TYPE], [UNKNOWN CASE TYPE EST O], [UNKNOWN CASE TYPE EST P], [UNKNOWN CASE TYPE EST S], [UNKNOWN TOTALS], [FOSTER CARE TOTALS], [FC EST O], [FC EST P], [FC EST S], [MEDICAL TOTALS], [MEDICAL EST O], [MEDICAL EST P], [MEDICAL EST S], [NON TANF TOTALS], [NON TANF TYPE EST O], [NON TANF TYPE EST P], [NON TANF TYPE EST S]

GROUP BY
[CASE TYPE].DO,
Format(Now(),"mmmm yyyy"),
[TANF EST O].[TANF O],
[TANF EST P].[TANF P],
[TANF EST S].[TANF S],
[TANF TOTALS].[TANF TOTALS],
[UNKNOWN CASE TYPE EST O].[UNKNOWN O],
[UNKNOWN CASE TYPE EST P].[UNKNOWN P],
[UNKNOWN CASE TYPE EST S].[UNKNOWN S],
[UNKNOWN TOTALS].[UNKNOWN TOTALS],
[FC EST O].[FC O],
[FC EST P].[FC P],
[FC EST S].[FC S],
[FOSTER CARE TOTALS].[FOSTER CARE TOTALS],
[MEDICAL EST O].[MEDICAL O],
[MEDICAL EST P].[MEDICAL P],
[MEDICAL EST S].[MEDICAL S],
[MEDICAL TOTALS].[MEDICAL TOTALS],
[NON TANF TYPE EST O].[NON TANF O],
[NON TANF TYPE EST P].[NON TANF P],
[NON TANF TYPE EST S].[NON TANF S],
[NON TANF TOTALS].[NON TANF TOTALS]


RESULTS

SELECT
[CASE TYPE].DO,
Format(Now(),"mmmm yyyy") AS [MONTH],
[TANF EST O].[TANF O], RETURNED 806[TANF EST P].[TANF P], RETURNED 180[TANF EST S].[TANF S], RETURNED 16[TANF TOTALS].[TANF TOTALS], RETURNED 1002[UNKNOWN CASE TYPE EST O].[UNKNOWN O], RETURNED 123[UNKNOWN CASE TYPE EST P].[UNKNOWN P], RETURNED 16[UNKNOWN CASE TYPE EST S].[UNKNOWN S], RETURNED 1[UNKNOWN TOTALS].[UNKNOWN TOTALS], RETURNED 127[FC EST O].[FC O], RETURNED 57
[FC EST P].[FC P], RETURNED 10
[FC EST S].[FC S], RETURNED 29[FOSTER CARE TOTALS].[FOSTER CARE TOTALS], RETURNED 96
[MEDICAL EST O].[MEDICAL O], RETURNED 480[MEDICAL EST P].[MEDICAL P], RETURNED 86[MEDICAL EST S].[MEDICAL S], RETURNED 32[MEDICAL TOTALS].[MEDICAL TOTALS], RETURNED 598
[NON TANF TYPE EST O].[NON TANF O],RETURNED 1,769
[NON TANF TYPE EST P].[NON TANF P], RETURNED 80[NON TANF TYPE EST S].[NON TANF S], RETURNED 21
[NON TANF TOTALS].[NON TANF TOTALS],RETURNED 1,878SUM [TANF EST O].[TANF O]+
[TANF EST P].[TANF P]+
[TANF EST S].[TANF S]+
[UNKNOWN CASE TYPE EST O].[UNKNOWN O]+
[UNKNOWN CASE TYPE EST P].[UNKNOWN P]+
[UNKNOWN CASE TYPE EST S].[UNKNOWN S]+
[FC EST O].[FC O]+
[FC EST P].[FC P]+
[FC EST S].[FC S]+
[MEDICAL EST O].[MEDICAL O]+
[MEDICAL EST P].[MEDICAL P]+
[MEDICAL EST S].[MEDICAL S]+
[NON TANF TYPE EST O].[NON TANF O]+
[NON TANF TYPE EST P].[NON TANF P]+
[NON TANF TYPE EST S].[NON TANF S] AS [DISTRICT TOTAL] INTO REPORT


FROM [TANF EST O], [TANF EST P], [TANF EST S], [TANF TOTALS], [CASE TYPE], [UNKNOWN CASE TYPE EST O], [UNKNOWN CASE TYPE EST P], [UNKNOWN CASE TYPE EST S], [UNKNOWN TOTALS], [FOSTER CARE TOTALS], [FC EST O], [FC EST P], [FC EST S], [MEDICAL TOTALS], [MEDICAL EST O], [MEDICAL EST P], [MEDICAL EST S], [NON TANF TOTALS], [NON TANF TYPE EST O], [NON TANF TYPE EST P], [NON TANF TYPE EST S]

GROUP BY
[CASE TYPE].DO,
Format(Now(),"mmmm yyyy"),
[TANF EST O].[TANF O],
[TANF EST P].[TANF P],
[TANF EST S].[TANF S],
[TANF TOTALS].[TANF TOTALS],
[UNKNOWN CASE TYPE EST O].[UNKNOWN O],
[UNKNOWN CASE TYPE EST P].[UNKNOWN P],
[UNKNOWN CASE TYPE EST S].[UNKNOWN S],
[UNKNOWN TOTALS].[UNKNOWN TOTALS],
[FC EST O].[FC O],
[FC EST P].[FC P],
[FC EST S].[FC S],
[FOSTER CARE TOTALS].[FOSTER CARE TOTALS],
[MEDICAL EST O].[MEDICAL O],
[MEDICAL EST P].[MEDICAL P],
[MEDICAL EST S].[MEDICAL S],
[MEDICAL TOTALS].[MEDICAL TOTALS],
[NON TANF TYPE EST O].[NON TANF O],
[NON TANF TYPE EST P].[NON TANF P],
[NON TANF TYPE EST S].[NON TANF S],
[NON TANF TOTALS].[NON TANF TOTALS]

Query is returning a DISTRICT TOTAL of 13,697,401

 

[pbaldy]

I assume that the total is far in excess of what it should be? With no joins between your tables, you will get a Cartesian product. You likely need to be joining tables on whatever field(s) relate them.

 

[Mike Hughes]

OK, I'll look that way. Thanks