Access 2003 Query: Dsum function Help

[raquellr89]

I am using the dsum function in a query to keep a running total of a cost
column. But basically I need the running total to restart when the
organization changes.
How do I change the function fomula to work for organizations after the
first set?

This is what I want it to look like:

Organization: Cost: Running total:
xyz 5 5
xyz 5 10
abc 6 6
abc 6 12
123 1 1


This is what it looks like:

Cost: Running total:
xyz 5 5
xyz 5 10
abc 6 16
abc 6 22
123 1 23

It works for the first query because it's the first set of data in the table.
But the second set of data (that goes with the second query) is continuing
the running total from the beginning.
SO instead of doing a running total for 2nd organization, it's doing a
running total for the 1st AND 2nd organization.


This is the function I've been using:
Running Total: Format(DSum("[Total Log].[Cost]","Total Log","[ID]<= " &
[Total Log].[ID]),"Currency")

I have a table named Total Log, this is where all my data is contained.
The columns in that table are: Organization, Telephone, Contact Name,
Member, Period Covered, Purchase Order #, Payment By, Cost, Copies Given,
and ID. I inserted the ID column because I thought it would make it easier to
write the function. (But if I can do it without it, that would be better!!!)
Then I have separate queries for each of the different organizations. The
queries have the same columns plus the column Running Total column.

HELP, Please! I know a little about Access, but nothing to this depth. I
figured out how to write this bit just from researching it.

THANKS!

 

[MGFoster]

I am using the dsum function in a query to keep a running total of a cost
column. But basically I need the running total to restart when the
organization changes.
How do I change the function fomula to work for organizations after the
first set?

This is what I want it to look like:

Organization: Cost: Running total:
xyz 5 5
xyz 5 10
abc 6 6
abc 6 12
123 1 1


This is what it looks like:

Cost: Running total:
xyz 5 5
xyz 5 10
abc 6 16
abc 6 22
123 1 23

It works for the first query because it's the first set of data in the table.
But the second set of data (that goes with the second query) is continuing
the running total from the beginning.
SO instead of doing a running total for 2nd organization, it's doing a
running total for the 1st AND 2nd organization.


This is the function I've been using:
Running Total: Format(DSum("[Total Log].[Cost]","Total Log","[ID]<= " &
[Total Log].[ID]),"Currency")

I have a table named Total Log, this is where all my data is contained.
The columns in that table are: Organization, Telephone, Contact Name,
Member, Period Covered, Purchase Order #, Payment By, Cost, Copies Given,
and ID. I inserted the ID column because I thought it would make it easier to
write the function. (But if I can do it without it, that would be better!!!)
Then I have separate queries for each of the different organizations. The
queries have the same columns plus the column Running Total column.

HELP, Please! I know a little about Access, but nothing to this depth. I
figured out how to write this bit just from researching it.

THANKS!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just add the Organization to the DSum() criteria.

Running Total: DSum("[Total Log].[Cost]","Total Log","[ID]<= " &
[Total Log].[ID] & " Organization=" & [Total Log].Organization)

I took off the Format() function - it isn't necessary if the "Cost"
column is already a Currency data type in the table. If you want to
format the query's display - In the query design view:
1. right-click on the Running Total column
2. select Properties
3. in the Format property enter Currency.

Now when the query runs the Running Total column will show as a Currency
column.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSluNw4echKqOuFEgEQLXjQCgu0drzOYnT8yViCEh6sbRZI4KdY8AnilE
Ze7BzNKFSKeMrm7sHEW8Ax7r
=7Wxx
-----END PGP SIGNATURE-----