one table from 3 queries

[DianneBeck]

I need to generate a report that reflects the budget amount, mtd expenditures and ytd expenditures.

Each query has a fund, program and account fields and then an amount field. The report has to verify that these fields equal each other. I ( with this websites help) have generated 3 queries that reflects one amount for each the fund program and account.

Query #1 sql (appropriations):
SELECT APPROPRIATIONS.FUND, APPROPRIATIONS.PROGRAM, APPROPRIATIONS.ACCOUNT, Sum(APPROPRIATIONS.[BUDGET AMOUNT]) AS [SumOfBUDGET AMOUNT]
FROM APPROPRIATIONS
GROUP BY APPROPRIATIONS.FUND, APPROPRIATIONS.PROGRAM, APPROPRIATIONS.ACCOUNT;

query #2 mtd expenditures (SQL):


SELECT [mtd expenditures].FUND, [mtd expenditures].PROGRAM, [mtd expenditures].ACCOUNT, Sum([mtd expenditures].AMOUNT) AS SumOfAMOUNT
FROM [mtd expenditures]
WHERE ([mtd expenditures].[POST DATE]>#3/31/2015#) And ([mtd expenditures].[POST DATE]<#5/1/2015#)
GROUP BY [mtd expenditures].FUND, [mtd expenditures].PROGRAM, [mtd expenditures].ACCOUNT;

query #3 ytd expenditures (SQL):
SELECT [mtd expenditures].FUND, [mtd expenditures].PROGRAM, [mtd expenditures].ACCOUNT, Sum([mtd expenditures].AMOUNT) AS SumOfAMOUNT
FROM [mtd expenditures]
GROUP BY [mtd expenditures].FUND, [mtd expenditures].PROGRAM, [mtd expenditures].ACCOUNT;

Someone from the report forum told me I need to generate one table from these 3 queries....but I don't know how to do that.

Thanks

[JoeM]

I don't think a table is necessary. You should be able to join/link these queries together in another query and compare the values.

[DianneBeck]

I know how to put all three queries in another query but I don't know how to compare the values.

[June7]

What do you mean by 'compare'? Just have the values display next to each other? Or maybe an expression in query or textbox:

IIf([field A] = [field B], "Equal", "Not Equal")

or just calculate the difference:

[field A] - [field B]

[DianneBeck]

Okay I am making progress. The problem I have now is that if there is a budget amount and zero mtd expenditures and zero ytd expenditures then I need it to return this figure and it's not.

SELECT [qry appropriations].FUND, [qry appropriations].PROGRAM, [qry appropriations].ACCOUNT, Sum([qry appropriations].[SumOfBUDGET AMOUNT]) AS [SumOfSumOfBUDGET AMOUNT], Sum([QRY MTD EXPENDITURES].SumOfAMOUNT) AS SumOfSumOfAMOUNT, Sum([qry ytd expenditures].SumOfAMOUNT) AS SumOfSumOfAMOUNT1
FROM ([qry appropriations] INNER JOIN [QRY MTD EXPENDITURES] ON ([qry appropriations].ACCOUNT = [QRY MTD EXPENDITURES].ACCOUNT) AND ([qry appropriations].PROGRAM = [QRY MTD EXPENDITURES].PROGRAM) AND ([qry appropriations].FUND = [QRY MTD EXPENDITURES].FUND)) INNER JOIN [qry ytd expenditures] ON ([QRY MTD EXPENDITURES].ACCOUNT = [qry ytd expenditures].ACCOUNT) AND ([QRY MTD EXPENDITURES].PROGRAM = [qry ytd expenditures].PROGRAM) AND ([QRY MTD EXPENDITURES].FUND = [qry ytd expenditures].FUND)
GROUP BY [qry appropriations].FUND, [qry appropriations].PROGRAM, [qry appropriations].ACCOUNT, [QRY MTD EXPENDITURES].FUND, [QRY MTD EXPENDITURES].PROGRAM, [QRY MTD EXPENDITURES].ACCOUNT, [qry ytd expenditures].FUND, [qry ytd expenditures].PROGRAM, [qry ytd expenditures].ACCOUNT
HAVING (((Sum([qry appropriations].[SumOfBUDGET AMOUNT]))>=0) AND ((Sum([QRY MTD EXPENDITURES].SumOfAMOUNT))>=0) AND ((Sum([qry ytd expenditures].SumOfAMOUNT))>=0));

[June7]

Arithmetic with null returns null. Need to handle the nulls. Where are you doing a calc that involves a Null?

Or are you saying the budget record is not retrieved? Probably because of the INNER JOINs. Change to "Include all records from [qry appropriations] and only those from..." - this will be a LEFT or RIGHT join.

[DianneBeck]

Sometimes there may be a budget record amount and zero dollars in mtd and ytd expenditures. Sometimes it's the other way around there may be amounts in mtd and/or ytd expenditures and zero budget record amount.

I had the join from left to right and switched it to right to left but it still returned the same results. Our initial budget is given in July but through out the year there are times when we are given additional monies and I will post them. The query has to be able to check the fund, program and account and make sure each matches before it returns a total or in cases where there are zero dollars return a $0.00.

I'm not sure where I need to put the change statement you suggested. I tried and got the following message "The select statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect".

SELECT [QRY APPROPRIATIONS].FUND, [QRY APPROPRIATIONS].PROGRAM, [QRY APPROPRIATIONS].ACCOUNT, Sum([QRY APPROPRIATIONS].SumOfAMOUNT) AS SumOfSumOfAMOUNT, Sum([QRY MTD EXPENDITURES].SumOfAMOUNT) AS SumOfSumOfAMOUNT1, Sum([QRY YTD EXPENDITURES].SumOfAMOUNT) AS SumOfSumOfAMOUNT2
INCLUDE ALL RECORDS FROM [QRY APPROPRIATIONS] AND ONLY THOSE FROM ([QRY MTD EXPENDITURES] AND ONLY THOSE FROM [QRY YTD EXPENDITURES] ON ([QRY MTD EXPENDITURES].ACCOUNT = [QRY YTD EXPENDITURES].ACCOUNT) AND ([QRY MTD EXPENDITURES].PROGRAM = [QRY YTD EXPENDITURES].PROGRAM) AND ([QRY MTD EXPENDITURES].FUND = [QRY YTD EXPENDITURES].FUND)) ON ([QRY APPROPRIATIONS].ACCOUNT = [QRY MTD EXPENDITURES].ACCOUNT) AND ([QRY APPROPRIATIONS].PROGRAM = [QRY MTD EXPENDITURES].PROGRAM) AND ([QRY APPROPRIATIONS].FUND = [QRY MTD EXPENDITURES].FUND)
GROUP BY [QRY APPROPRIATIONS].FUND, [QRY APPROPRIATIONS].PROGRAM, [QRY APPROPRIATIONS].ACCOUNT, [QRY APPROPRIATIONS].[POST DATE], [QRY MTD EXPENDITURES].FUND, [QRY MTD EXPENDITURES].PROGRAM, [QRY MTD EXPENDITURES].ACCOUNT, [QRY YTD EXPENDITURES].FUND, [QRY YTD EXPENDITURES].PROGRAM, [QRY YTD EXPENDITURES].ACCOUNT;

[June7]

What is purpose of this 'comparison', just to identify mismatch of budget and expenditures? That's what find Unmatched queries are for.

If data doesn't exist, the query isn't going to manufacture it for you.

If there is no budget record for an account then it won't show in the query.

Need a dataset of all possible accounts for all possible years. Then join the summary queries to that dataset with compound link on account and year.

[DianneBeck]

Let me work with this at work on Tuesday and I will get back with you if it doesn't work....

I don't need a dataset for numerous years. This just for 12 months.

Thanks for your help

[DianneBeck]

Just wanted to let you know that it works.!!!!!!!!!!!!!!!!!!!!!!!

Thanks for all your help