DSum Problems

[AccessIM]

Hello All-

I am having a real problem getting a running total column to work properly
in one of my queries.

I have a union query that pulls in attendance point information from three
different tables. I am using this union query to try to create a running
total of points in the far right column.

The union query has the following fields: EmployeeID, SSN, IncidentDate,
Type and Points. I would like a column that has a running total of the
Points field for each EmployeeID. Like this:

EmployeeID SSN IncidentDate Type Points TotalPoints
6 555332222 10/21/09 Neg Pts -.25 -.25
6 555332222 10/22/09 Absent 2.00 1.75
8 111223333 10/20/09 Late 0.50 0.50
8 111223333 10/21/09 Absent 2.00 2.50
8 111223333 10/22/09 Late 0.50 3.00

I have come the closest with the code below but it totals all records in the
query and shows 4.75 instead of breaking them by SSN or EmployeeID (either of
these field would work).

TotalPointsDSum("[qryTotalPointDetail].[Points]","qryTotalPointDetail","[SSN]<="
& [qryTotalPointDetail].[SSN] And "[IncidentDate]<=" &
[qryTotalPointDetail].[IncidentDate]))

I tried to take out the ...And "[IncidentDate]...part but that only returns
an #Error.

I have been searching through the threads and trying different variations
but just can't seem to figure it out.

Could someone please give me some assistance with this as I am working under
a dead line for this project? I would GREATLY appreciate it.

 

[Marshall Barton]

I am having a real problem getting a running total column to work properly
in one of my queries.

I have a union query that pulls in attendance point information from three
different tables. I am using this union query to try to create a running
total of points in the far right column.

The union query has the following fields: EmployeeID, SSN, IncidentDate,
Type and Points. I would like a column that has a running total of the
Points field for each EmployeeID. Like this:

EmployeeID SSN IncidentDate Type Points TotalPoints
6 555332222 10/21/09 Neg Pts -.25 -.25
6 555332222 10/22/09 Absent 2.00 1.75
8 111223333 10/20/09 Late 0.50 0.50
8 111223333 10/21/09 Absent 2.00 2.50
8 111223333 10/22/09 Late 0.50 3.00

I have come the closest with the code below but it totals all records in the
query and shows 4.75 instead of breaking them by SSN or EmployeeID (either of
these field would work).

TotalPointsDSum("[qryTotalPointDetail].[Points]","qryTotalPointDetail","[SSN]<="
& [qryTotalPointDetail].[SSN] And "[IncidentDate]<=" &
[qryTotalPointDetail].[IncidentDate]))

I tried to take out the ...And "[IncidentDate]...part but that only returns
an #Error.

I don't see why removing a condition would cause an error,
unless you removed too much or not enough.

I think the reason you are not getting the right result is
because you are using <= in the SSN condition. Try using:

TotalPoints: DSum("Points", "qryTotalPointDetail", "SSN=" &
qryTotalPointDetail.SSN And "IncidentDate<=" &
qryTotalPointDetail.IncidentDate)

 

[John Spencer]

Also, IF IncidentDate is a datetime field, then you need to include the #
delimiters around the date. Also small error with AND which should be inside
the quote marks

TotalPoints: DSum("Points", "qryTotalPointDetail", "SSN=" &
qryTotalPointDetail.SSN & " And IncidentDate<=#" &
qryTotalPointDetail.IncidentDate & "#")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am having a real problem getting a running total column to work properly
in one of my queries.

I have a union query that pulls in attendance point information from three
different tables. I am using this union query to try to create a running
total of points in the far right column.

The union query has the following fields: EmployeeID, SSN, IncidentDate,
Type and Points. I would like a column that has a running total of the
Points field for each EmployeeID. Like this:

EmployeeID SSN IncidentDate Type Points TotalPoints
6 555332222 10/21/09 Neg Pts -.25 -.25
6 555332222 10/22/09 Absent 2.00 1.75
8 111223333 10/20/09 Late 0.50 0.50
8 111223333 10/21/09 Absent 2.00 2.50
8 111223333 10/22/09 Late 0.50 3.00

I have come the closest with the code below but it totals all records in the
query and shows 4.75 instead of breaking them by SSN or EmployeeID (either of
these field would work).

TotalPointsDSum("[qryTotalPointDetail].[Points]","qryTotalPointDetail","[SSN]<="
& [qryTotalPointDetail].[SSN] And "[IncidentDate]<=" &
[qryTotalPointDetail].[IncidentDate]))

I tried to take out the ...And "[IncidentDate]...part but that only returns
an #Error.

I don't see why removing a condition would cause an error,
unless you removed too much or not enough.

I think the reason you are not getting the right result is
because you are using <= in the SSN condition. Try using:

TotalPoints: DSum("Points", "qryTotalPointDetail", "SSN=" &
qryTotalPointDetail.SSN And "IncidentDate<=" &
qryTotalPointDetail.IncidentDate)