Sum of Prev Months Records

[OU18]

All,

I am trying to do the following.

Table: tblInvoiceTransactions
Fields:AccrualMonth,InvoiceAmount,AccountID.

What I need to do is generate a query that when the accrual month is entered (IE. 8/1/04.. the accrual month will always be the first of the month)the query will create 3 columns that will sum the invoice amount for each of the 3 previous months. So the data would look like the following

Prompt - Enter Accrual Month

AcctID AccrualMonth 1Mo Ago 2Mo Ago 3Mo Ago
27383 8/1/04 (Prompt) 15.00 12.88 13.44
28582 8/1/04 11.56 25.83 75.65

How can I do this?

Thanks for the help in advance.
Steve

 

[lupins46]

You have said that the date (acrrual month) is entered as a parameter; so where do you get the actual date for use in determining the prior months' values?

 

[OU18]

If I am understanding your question.

The query would request the user to enter the Initial Accrual Month. So in the case above, in the accrual month column, this date was passed via a prompt box at the running of the query. Then from that date, i would need to go back 1month, 2months and 3 months to sum the invoice amounts.

Hopefully that answers the question.

Thanks for your help

 

[lupins46]

But if ALl the dates say 8/1/04 then on what basis do you get a total for 1 month ago?

 

[lespaul]

OU18 - there must be a field in your table that has a date. How do you want to compare the date that is entered to the date in your table?

So the user enters the date and you want to run a query that gathers some information from a table based on the date that is entered. What does the raw data in the table look like? Where do your numbers 15.00, 12.88, 13.44 come from in regards to the date the user entered?

Leslie

 

[OU18]

Lupins46,

All the dates in the table are not 8/1/04. The reason for the prompt is to filter out the other information. The database is for month end accruals. So if my boss wants to see the accrual for 8/1/04(month of august) he also wants to see the sum of invoices for each acct id for the previous 3 months.

In the table there are dates all the way back to 2000. But he may only want to see for 8/1 and the prior 3 months.

Hopefully this clears up any miscommunications I may have made. Or maybe I am confused?

I can do it with one query for each month, but I was hoping to eliminate the need for 3 individual queries plus a query to bring it all back together.

 

[OU18]

LesPaul,

The data in the table is billing transactions from our carriers. We are a telecom company. So each month we get bills, the data is entered into the system.

Acct ID - AutoNumber
InvoiceAmount - From bill sent by Carrier
Accrual Month - Entered into table by User

There are more fields then this, but the information is irrelevant to the query i need. So each month we start a new accrual month for the bills that are recvd from the carrier.


What I am looking to do is take the accrual month entered at the prompt, have the query take the data for each of the previous 3 months for each acctid and sum the invoice amount for each of those months into their respective columns.

 

[lespaul]

SELECT [Acct ID], [Accrual Month], Sum([InvoiceAmount) from tblName WHERE [Accrual Month] between DateAdd('m', [Forms]![frmName]![datecontrol], -3) AND [Forms]![frmName]![datecontrol] Group By [Acct ID], [Accrual Month] Order by [Acct ID], [Accrual Month]

This will get you the basic information that you need, you will need to then create a cross tab query with the Accrual Month as your columns and acct id as the rows with the sum at the intersection.

You may want to check the help on the DateAdd, it's a different function in my "flavor" of SQL, so I may have the syntax a little wrong.

Leslie

 

[OU18]

LesPaul,

I appreciate the time and help. It is definitely much appreciated.

Thanks Steve

I will go and test what you have given me here.