query that will total multiple expenditure amounts in several different accounts.

[DianneBeck]

We are using Access 2010 at work. I have developed a table with my budget figures for each account even those that have a zero value. In another table I am posting the daily expenditures. This will go from July 1st of each year through June 30th of the next year. There can be multiple postings to each expenditure account on different dates. I have written a query that will pull out the the month to date figures >03/31/2015 and < 05/01/2015. The problem is that it will not return a total for each expenditure account unless the date is the same. I would like the expenditure table to reflect the actual processing date. Is there a way I can get it to sum the amount column, with different processing dates?

[orange]

Please show readers your table(s) designs -- your fields, names, data types...
It would also be helpful to show the query(sql view) you are using.

[DianneBeck]

Expenditure Table fields:
Date
Vendor Name
Voucher number
Fund
Program
Account
Amount

Query design, pulls from this table:
Date (does not show in results) Criteria: >03/31/2015 and < 05/01/2015 (to pull mtd totals only)
Fund
Program
Account
Amount (Sum)

When you run the query if there are multiple postings for the same account it does not return one total for the account and that is what I need it to do. The only way it will return one total is if the date is the same and I would prefer not to change the date to one date for the whole month.

[DianneBeck]

ID	DATE	VENDOR NAME	VOUCHER NUMBER	FUND	PROGRAM	ACCOUNT	AMOUNT
1	4/1/2015	APPALACHIAN POWER	1	01000	398015	5015420	$35,700.00
2	4/10/2015	APPALACHIAN POWER	2	01000	398015	5015420	$1,500.00
3	4/10/2015	APPALACHIAN POWER	3	01000	398015	5015420	$500.00

FUND	PROGRAM	ACCOUNT	SumOfAMOUNT
01000	398015	5015420	$35,700.00
01000	398015	5015420	$2,000.00

I want the query to return a total of $37,700.00

[orange]

Please show the SQL for th that is different in the source data is the e query you did run. The only value I see Date value, so it seems you have some grouping on Date which is not needed, and is causing the aggregate records to appear as they do. Also DATE is a reserved word in Access.

[DianneBeck]

I realize Date is a reserved word in Access, however, the date is required since I will be posting items for 12 mos.

I have tried and tried to copy my query into this post but it won't do it. I have not written any other SQL other than the sort on the date.

[orange]

Please excuse my previous post--seems the edits were misplaced?? I have adjusted it below.

Please show the SQL for the query you did run. The only value I see that is different in the source data is value, so it seems you have some grouping on Date which is not needed, and is causing the aggregate records to appear as they do. Also DATE is a reserved word in Access.

You can rename Date as MyDate or whatever.
When you use the query wizard, go to SQL View and copy the code, then paste it into your post.
Good luck.

[DianneBeck]

Okay I tried changing the date field to Post Date and it still does the same thing.

I added a few more items to my table:

ID	POST DATE	VENDOR NAME	VOUCHER NUMBER	FUND	PROGRAM	ACCOUNT	AMOUNT
1	4/1/2015	APPALACHIAN POWER	1	01000	398015	5015420	$35,700.00
2	4/10/2015	APPALACHIAN POWER	2	01000	398015	5015420	$1,500.00
3	4/10/2015	APPALACHIAN POWER	3	01000	398015	5015420	$500.00
4	3/5/2015	JDM SERVICES	4	01000	398015	5015430	$2,000.00
5	3/7/2015	APPALACHIAN	5	01000	398015	5015420	$33,000.00
6	4/15/2015	DOC FARMERS MKT	6	01000	398007	5013620	$3,500.00
7	4/17/2015	DOC AGRIBUSINESS	7	01000	398007	5013620	$2,000.00

Here's the SQL view
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, [mtd expenditures].[POST DATE]
HAVING ((([mtd expenditures].[POST DATE])>#3/31/2015# And ([mtd expenditures].[POST DATE])<#5/1/2015#));

Here are the results I get:

FUND	PROGRAM	ACCOUNT	SumOfAMOUNT
01000	398015	5015420	$35,700.00
01000	398015	5015420	$2,000.00
01000	398007	5013620	$3,500.00
01000	398007	5013620	$2,000.00

I need the query return a total of $37,700 for account 5015420
and an amount of $7,500 for account number 5013620. All accounts will not have multiple postings for the month but some do.

[orange]

I don't think you need to do the

Group By mtd expenditures].[POST DATE]

I believe this is what is giving you the separate amounts.

[DianneBeck]

I took out the group by as you instructed. I get the following error message

Your query does not include the specified '[mtd expenditures,[POST DATE]> #03/31/2015# AND [mtd expenditures],[POST DATE] #< 05/01/2015# as part of an aggregate function.

SQL VIEW:
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
HAVING ((([mtd expenditures].[POST DATE])>#3/31/2015# And ([mtd expenditures].[POST DATE])<#5/1/2015#));

[orange]

Try thisuntested)
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;

[DianneBeck]

It works!!!!!!!!!!!!!!!!!!!!!!!!!

Thank you....Thank you!!!!!!!!!!!!!

[orange]

You're welcome. Happy to help.