Crosstab Query/Running Totals/DSUM

[bjfriedman]

I am trying to create a crosstab query where the Value Field is a sum of all of the transactions in the Payments Table prior to header date for that column. So I am looking for a running total of sorts, to use in an average daily balance calculation

The Header Dates are driven by another table called "Date Range" where I upload the dates of 3 month quarters(if this can be handled differently please let me know). This was the only real way I could create a date range that would reflect the data I needed. Especially because it will need to be changed periodically.

The Column is Driven by APN numbers.

My SQL Looks like:
TRANSFORM DSum([Payments]![Amount],"Payments",[Date Range]![Date Range]=[Payments]![Date]) AS [Running Total]
SELECT Payments.APN
FROM Payments RIGHT JOIN [Date Range] ON Payments.Date = [Date Range].[Date Range]
GROUP BY Payments.APN
ORDER BY Payments.APN, [Date Range].[Date Range]
PIVOT [Date Range].[Date Range];

I am running into the "You are trying to execute a query that does not include the specified expression as a part of the aggreate function"

Any help or direction is greatly appreciated.

-Bryan

 

[bjfriedman]

After Include the Amount Field, and the Date Field in the query I no longer got the aggregate function error, but instead I get "Data Type Mismatch in Critera"

SQL:
TRANSFORM DSum(Payments!Amount,"Payments",[Date Range]![Date Range]=Payments!Date) AS [Running Total]
SELECT Payments.APN
FROM Payments RIGHT JOIN [Date Range] ON Payments.Date = [Date Range].[Date Range]
GROUP BY Payments.APN, Payments.Amount, Payments.Date
ORDER BY Payments.APN, [Date Range].[Date Range]
PIVOT [Date Range].[Date Range];

I have no clue where to go from here...

 

[lespaul]

What did you change in the query? I don't see any differences in what you posted.

The aggregate error comes from not having all the fields that are not an aggregate in the SELECT clause listed in the GROUP BY clause.

Let's say you have a table ORDERS wanted to see the total of orders by STATE:

SELECT STATE, SUM(TOTAL) FROM ORDERS GROUP BY STATE

this query will GROUP the results by state.
************************************************
Let's say you wanted to see the total BY MONTH BY STATE:

SELECT Month(OrderDate), STATE, SUM(TOTAL) FROM ORDERS GROUP BY Month(OrderDate), STATE

this query will GROUP the results by date and state.
***********************************************

To get the error you first listed you have an aggregate query that is MISSING fields from the GROUP BY clause.

If I take the second query above and change it to this (missing the STATE field in the GROUP BY clause):

SELECT Month(OrderDate), STATE, SUM(TOTAL) FROM ORDERS GROUP BY Month(OrderDate)

This query will get the aggregate error you listed above.
*******************************************


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[bjfriedman]

Hi Lespaul,

The change is on the Group by line. So basically I had changed what was initially wrong.

Now I need to figure out what is causing the Data Type Mismatch in critera error.

After that I'll probably have some issues with DSUM... any other hints on how to get a crosstab query to provide running totals based upon the date fields?

 

[dhookom]

As a WAG, change the DSum() to

TRANSFORM DSum("Amount","Payments","[Date]= #" & [Date Range]![Date Range] & "#") AS [Running Total]

BTW: you really should find a naming convention that doesn't allow field names that are also function names (Date) and the tables and fields can share the same name :-(

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bjfriedman]

Dhookom,

Thank you very much... I feel like I'm getting on the right track however now there is an error of:

Syntax Error in date in query expression '[Date]=##'

Could this be alleviated if i change Date to EffectiveDate ?

 

[dhookom]

What is your current SQL that generates this error and is the [Date Range]^2 potentially Null?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bjfriedman]

Current SQL:
TRANSFORM DSum("Amount","Payments","[EffectiveDate]= #" & [DateRange]![DateRange] & "#") AS [Running Total]
SELECT Payments.APN
FROM Payments RIGHT JOIN DateRange ON Payments.EffectiveDate = DateRange.DateRange
GROUP BY Payments.APN, Payments.Amount, Payments.EffectiveDate
ORDER BY Payments.APN, DateRange.DateRange
PIVOT DateRange.DateRange;

I don't believe [DateRange]^2 could be Null

[DateRange] is a one column table with dates from 6/1/06 to 9/30/06 in a Date/Time Format

I change some of my field names to get rid of Date and other ideas I was trying out. Hopefully it won't be confusing.

Thank you for all of your help I really appreciate it.

 

[dhookom]

I'm not sure how you would get "sum of all of the transactions in the Payments Table prior to header date for that column" when your DSum() uses [red]=[/red] rather than [red]<=[/red].

Also, apparently the crosstab doesn't resolve the DSum() expression so you need to first create a totals query and then use this totals query in a crosstab query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bjfriedman]

I have modified my DSUM Statement to include <= as that is what I had intended but it was an over sight.

However I still have issues with the crosstab query not running without the error:

Syntax Error in date in query expression '[Date]=##'

In addition I am not sure what you mean by running a prior query for totals. How would that look? How would it integrate into this Crosstab?

Overall would there be a better way for me to approach this problem?

Basically I need something like this:
Date1 Date2/Date3
APN1 Sum(Amount) for APN1 thru Date 1
APN2 Sum(Amount) for APN2 thru Date 1
APN3 Sum(Amount) for APN3 thru Date 1

This what I can establish a Daily Balance over Quarterly Periods to determine the Average Daily Balance, to then Charge Interest.

Any other ideas on where to proceed, any good tutorials on this? I can't be the first person to use Access for Daily Balances.

 

[dhookom]

As I stated, you can't use the DSum() in the value of a crosstab. So, you just change the crosstab to a totals query so you can see the DSum() results. Save this query with a name like "qtotForXTab". Then create a crosstab based on qtotForXTab.

I don't know how average daily balance would be calculated based on your transactions/data.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bjfriedman]

SELECT Payments.APN, DateRange.DateRange, DSum("Amount","Payments","[EffectiveDate]<= #" & DateRange!DateRange & "#") AS RunningTotal
FROM Payments RIGHT JOIN DateRange ON Payments.EffectiveDate = DateRange.DateRange
GROUP BY Payments.APN, DateRange.DateRange, Payments.Amount, Payments.EffectiveDate
ORDER BY Payments.APN, DateRange.DateRange;

So Dsum will give me a value but it is not the value I am looking for.

For Example:
APN 1470021030 6/1/06 gives me a total of 187543.00
However the transactions for this APN are:
1/1/06 +3539
2/1/06 -67
4/1/06 -67
6/1/06 -67

Therefore the DSUM value I should be getting for this APN is 3338.00 or do I misunderstand how DSUM should be used.

In addition, I have dates in my DateRange Table that have no corresponding transactions for an APN, so instead of showing the Summations of Transactions prior to this date all of these dates are associated with no APN. Very Strange. Do I need to include a critera to stipulate that the APN nos are unique, and how would i do that?

Sorry for being such a newb but this has been boggling my mind.

 

[dhookom]

Yes, your DSum() would need to filter for the APN

SELECT Payments.APN, DateRange.DateRange, DSum("Amount","Payments","[EffectiveDate]<= #" & DateRange!DateRange & "# AND APN=""" & Payments.APN & """") AS RunningTotal
FROM Payments RIGHT JOIN DateRange ON Payments.EffectiveDate = DateRange.DateRange
GROUP BY Payments.APN, DateRange.DateRange, Payments.Amount, Payments.EffectiveDate
ORDER BY Payments.APN, DateRange.DateRange;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bjfriedman]

Wooo HOoooo... Ok... I am soooo close now

Now here is where I am at:

I have dhookom's select query above and I have integrated that into a crosstab query. However information is only populating on dates where there were transactions, not on every date.

So for example

6/1/07 for APN1470021030 is 3338 as it needs to be, however 6/2/07 for the same APN is blank, is there anyway I can create a record for 6/2/07/APN1470021030 within the select query so that my crosstab will reflect this. 6/2/07 should also be 3338 as no transactions were processed.

I really appreciate all of your help!!!

 

[dhookom]

You may need to begin by creating a cartesian query like

== qcarAPNDates ===========
SELECT Payments.APN, DateRange.DateRange
FROM Payments, DateRange
GROUP BY Payments.APN, DateRange.DateRange;

Then use this query rather than your Payments table in the totals query. You may need to figure some of this on your own.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bjfriedman]

So just for an update this is what I have currently and for most purposes it works:

SELECT APNDate.APN, APNDate.DateRange, DSum("Amount","Payments","[EffectiveDate]<= #" & [APNDate]![DateRange] & "# AND APN=""" & [APNDate].[APN] & """") AS RunningTotal
FROM Payments RIGHT JOIN APNDate ON Payments.EffectiveDate = APNDate.DateRange
GROUP BY APNDate.APN, APNDate.DateRange, Payments.Amount, Payments.EffectiveDate
ORDER BY APNDate.APN, APNDate.DateRange;

My question is, why for days there are payments are to seperate lines generated?

I have a work around for this in my crosstab query though:
TRANSFORM First(Pmts2.RunningTotal) AS FirstOfRunningTotal
SELECT Pmts2.APN
FROM Pmts2
GROUP BY Pmts2.APN
PIVOT Pmts2.DateRange;

dhookom, I really really appreciate your help on this one, you have been awesome, extremely kind and patient.

 

[dhookom]

I would not group by Payments.Amount

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]