Running Sum - Microsoft KB290136

[Kimbertha]

Hello All:



Has anyone had any success reperforming Method 2 of KB290136 in Access 2007 with live data where the "Employee ID" is a date and the "Freight" is a value similar to the value used for freight in the Northwind example?

http://support.microsoft.com/kb/290136

Essentially I am looking for results similar to those displayed in Method 2 of KB290136 except substituting dates where you see names. Example below:

01/10/2005 10 10
01/15/2005 7 17
07/28/2007 4 21

I have successfully reperformed the syntax on the Northwind sample data base at home. But when I try it at work with my live data, it doesn't produce a running sum. The only real difference is that I am trying to group the sum on a date versus a text field.

Regards,

Kim

[pbaldy]

I believe you would just need the appropriate date syntax:

DLookup Usage Samples

[Kimbertha]

Hello P:

Could you post an example of the SQL or Expression Builder syntax? I am close, but I'm not there yet.

Kim

[pbaldy]

Why don't you post what you have so far that isn't working, and we'll fix it?

[Kimbertha]

Hello P:

Here is the syntax from KB290136 Option 2 that I am using as my guidance:

RunTot: Format(DSum("Freight","Orders","[EmployeeID]<=" & [EmpAlias] & ""),"$0,000.00")
-------> For Totals, choose Expression

Here is the SQL from my query:

SELECT Test.Date AS DateAlias, Sum(Test.Amount) AS SumOfAmount,
DSum("Amount","Test","[Date]<=" & [DateAlias] & "") AS RunningTotal
FROM Test
GROUP BY Test.Date
ORDER BY Test.Date;

Here is what the data table Test looks like:

Date Amount Desired Running Sum
01/10/2004 35 35
05/25/2005 10 40
06/01/2007 5 45
and so on

I have noticed that when the DateAlias and Date are values (dates, not text), I get a null return. But if I change the Date field in the data table to text, then I get a total for all items in the table, but it's not a running total. But I don't want to change the values to text, nor do I want a total over the entire table.

I've played around with the single and double quotes, pound # signs, brackets, etc. without success. There is some small thing that I'm just not getting.

Regards,

Kim

[pbaldy]

As the link describes, you need to surround a date value with #.

[slave138]

...
Here is the SQL from my query:

SELECT Test.Date AS DateAlias, Sum(Test.Amount) AS SumOfAmount,
DSum("Amount","Test","[Date]<=" & [DateAlias] & "") AS RunningTotal
FROM Test
GROUP BY Test.Date
ORDER BY Test.Date;

Here is what the data table Test looks like:

Date Amount Desired Running Sum
01/10/2004 35 35
05/25/2005 10 40 (45?)
06/01/2007 5 45 (50?)
and so on

...

I've played around with the single and double quotes, pound # signs, brackets, etc. without success. There is some small thing that I'm just not getting.

Kim, I know you say you've tried the pound signs already, but pbaldy is right.

Your query should be:

SELECT Test.Date AS DateAlias, Sum(Test.Amount) AS SumOfAmount, 
DSum("Amount","Test","[Date]<=#" & [DateAlias] & "#") AS RunningTotal
FROM Test
GROUP BY Test.Date
ORDER BY Test.Date;

See the attachment if you'd like to see it in action.

[Kimbertha]

Helle Slave:

I inserted the pound (#) signs where you indicated and the query worked perfectly! Never would I have guessed that the pound signs go where they go. I was putting them around the fields that had the date, as in #[Date]# or #[DateAlias]#, and obviously that didn't work.

The problem with some of the Microsoft guidance is that they allude to stuff like using # to specify date values, but they don't exactly tell you where they go. I am very good with "monkey see, monkey do" once I see a solution, but they don't always give us good solutions as our base.

Thanks a million.

Kim

[slave138]

My pleasure. Have a good weekend.