Cumulative sum in report

[DougW via AccessMonster.com]

Hi-

I have created a query (with much help from folks on this forum) that lists
deliveries of parts by date. Now I am struggling to create a report that
summarizes the information. I could use more help.

There are 7 different Part types. Each Part is further described by 3
Variables.
There are 4 possible values for Variable 1 (V1), 11 values for V2, and 4
values for V3.
So there are 176 possible variants of each Part.

My query records contain this information:
Record index#
V1 value (1-4)
V2 value (1-11)
V3 value (1-4)
Part Type# (1-7)
Delivery date

The report that I have created (so far) organizes the information with the
following nested groups:
Group by delivery month (in ascending order)
- Group by V1 (ascending)
-- Group by V2 (ascending)
--- Group by V3 (ascending)
---- Detail (for that combo of date,V1,V2,V3) lists that month's count for
Part1, P2, P3, P4, P5, P6, P7

So far that all works fine. The grouping levels in the report show only the
Parts by Variable that exist as records (i.e. I have avoided created a
"spreadsheet" with 176 columns).

Now I would like to add a cumulative sum of each combination of Part# and the
3 variables (V1, V2, V3) over time. I can't simply use a running sum of
each part, since I need to distinguish them by the unique combination of
Variables.

For example, I might have a delivery of Part#1 with V1=2, V2=8, V3=1, in
qty=7, in June 2008.
The next time there is a delivery of P1 with those same V1, V2, V3 values may
be Nov 2008, in qty=3.
In that detail row, next to the qty 3 value, I would like to see the
cumulative sum of (10).

Can anybody suggest a strategy to allow me to track the cumulative sum of
distinct parts over time?
I appreciate any ideas you may have.

thanks,
Doug

 

[DougW via AccessMonster.com]

Can I somehow create the cumulative sum in a totals query and then show it in
the report??

 

[Evi]

You can achieve your Running Sum if you have a distinct date/Time field (ie
you only have 1 delivery per day or you record the time as well as the date
of the delivery) or some accumulative field that increases from record to
record perhaps your Autonumber if records are always entered
chronologically.
You could use in your report's query.
DSum the Value field with the criteria that YourVariable = Your current
Variable and Part Number = Your current part number and your date or
Autonumber is less than your current one.

You would need to circle that with the NZ function so that your first record
would just have the current value.
You would then add that result to your current value
If you need help with this, give us the field names involved and what data
type they are (number, text, date) then


Evi

 

[DougW via AccessMonster.com]

Evi, thanks for the response.
I tried what you described and was able to get it to work.
I understand your comment about needing a distinct date/time field or a
unique chronological autonumber.

The SQL of my query is here (it's still a work in progress):

SELECT [Core parts2].DeliverLocnID, [Core parts2].DrumSizeID, [Core parts2].
BeadSizeID, [Core parts2].DrumWidthID, [Core parts2].[Ship date (est)], [Core
parts2].[2], DSum("2","Core parts2","[DrumSizeID]= " & [Core parts2].
DrumSizeID) AS RunSum2, [Core parts2].[5], [Core parts2].[1], [Core parts2].
[3], [Core parts2].[4], [Core parts2].[6], [Core parts2].[7], [Core parts2].
[8], [Core parts2].[9], [Core parts2].[10], [Core parts2].[11], Locations.
LocnName, [Core parts2].[PO Number], BasicDrumSize.DrumSizeDesc, BeadSize.
BeadSizeDesc, DrumWidth.DrumWidthDesc, [7]/2 AS DiaEQCalc, [8]/32 AS
CovStCalc, [9]/16 AS SegBigCalc, [10]/16 AS SegSmallCalc, [11]/32 AS
CovPlCalc
FROM DrumWidth INNER JOIN ((([Core parts2] INNER JOIN Locations ON [Core
parts2].DeliverLocnID = Locations.LocnIndex) INNER JOIN BasicDrumSize ON
[Core parts2].DrumSizeID = BasicDrumSize.DrumSizeIndex) INNER JOIN BeadSize
ON [Core parts2].BeadSizeID = BeadSize.BeadSizeIndex) ON DrumWidth.
DrumWidthIndex = [Core parts2].DrumWidthID
ORDER BY [Core parts2].DeliverLocnID, [Core parts2].DrumSizeID, [Core parts2].
BeadSizeID, [Core parts2].DrumWidthID, [Core parts2].[Ship date (est)];


My query uses a crosstab query called 'Core parts2' (bad naming practice, I
now know) as a table.
I tried the DSum function in a calculated field called RunSum2. It's summing
the count of Part #2.
The field for Part #2 is called "2", which is how it was generated in my
crosstab. I realize that's probably also bad naming convention, but I
haven't discovered how to rename those column names that are generated in my
crosstab query.
DrumSizeID is one of my variables (what I referred to as V1 in my post).

The expression that I came up with for that calculated field is as follows:

RunSum2: DSum("2","Core parts2","[DrumSizeID]= " & [Core parts2].DrumSizeID)

I had a really hard time working out a syntax for the DSum criteria of "field
= current field".
I stumbled across a few examples on other posts here, but the syntax was not
easy to deduce.
I have seen lots of ampersands and single quotes and double quotes but am not
sure of the significance.
Can you point me to a reference that describes how that sort of syntax should
be constructed?
(I couldn't even come up with a good set of search terms to find info on this
syntax!)

Thank you very much for taking the time to help me on this.
I appreciate your assistance!

Best regards,
Doug

You can achieve your Running Sum if you have a distinct date/Time field (ie
you only have 1 delivery per day or you record the time as well as the date
of the delivery) or some accumulative field that increases from record to
record perhaps your Autonumber if records are always entered
chronologically.
You could use in your report's query.
DSum the Value field with the criteria that YourVariable = Your current
Variable and Part Number = Your current part number and your date or
Autonumber is less than your current one.

You would need to circle that with the NZ function so that your first record
would just have the current value.
You would then add that result to your current value
If you need help with this, give us the field names involved and what data
type they are (number, text, date) then

Evi

Can I somehow create the cumulative sum in a totals query and then show it in
the report??

[quoted text clipped - 54 lines]

 

[Evi]

Are you sure you were able to get it to work? I've never seen anyone using a
Crosstab query as the Source for a Select Query before (though that doesn't
mean it's wrong to do it as far as I know)

DSum/DLookup/DCount/DMax/DMin syntax is a bit of a pig but look how many
functions you get for the price - the syntax is the same for all.
I gave you the merest summary in case it wasn't what you needed.
About the clearest summary of the syntax is on Allen Browne's page
http://www.allenbrowne.com/casu-07.html

If you need help for your specific DSum then give me the names of these
fields and say what data type they are in their table (text, Date, or
Number)
Tell me
1.The field you want to Sum (so it has to be a field containing numbers)
2. The field that contains the chronoligical value so we know which order
the records are in (that field needs to be in the Query where you are
looking up your data and also in your current query.
3. the name of the Query or Table that contains the fields you want to
sum.



Evi

Evi, thanks for the response.
I tried what you described and was able to get it to work.
I understand your comment about needing a distinct date/time field or a
unique chronological autonumber.

The SQL of my query is here (it's still a work in progress):

SELECT [Core parts2].DeliverLocnID, [Core parts2].DrumSizeID, [Core parts2].
BeadSizeID, [Core parts2].DrumWidthID, [Core parts2].[Ship date (est)], [Core
parts2].[2], DSum("2","Core parts2","[DrumSizeID]= " & [Core parts2].
DrumSizeID) AS RunSum2, [Core parts2].[5], [Core parts2].[1], [Core parts2].
[3], [Core parts2].[4], [Core parts2].[6], [Core parts2].[7], [Core parts2].
[8], [Core parts2].[9], [Core parts2].[10], [Core parts2].[11], Locations.
LocnName, [Core parts2].[PO Number], BasicDrumSize.DrumSizeDesc, BeadSize.
BeadSizeDesc, DrumWidth.DrumWidthDesc, [7]/2 AS DiaEQCalc, [8]/32 AS
CovStCalc, [9]/16 AS SegBigCalc, [10]/16 AS SegSmallCalc, [11]/32 AS
CovPlCalc
FROM DrumWidth INNER JOIN ((([Core parts2] INNER JOIN Locations ON [Core
parts2].DeliverLocnID = Locations.LocnIndex) INNER JOIN BasicDrumSize ON
[Core parts2].DrumSizeID = BasicDrumSize.DrumSizeIndex) INNER JOIN BeadSize
ON [Core parts2].BeadSizeID = BeadSize.BeadSizeIndex) ON DrumWidth.
DrumWidthIndex = [Core parts2].DrumWidthID
ORDER BY [Core parts2].DeliverLocnID, [Core parts2].DrumSizeID, [Core parts2].
BeadSizeID, [Core parts2].DrumWidthID, [Core parts2].[Ship date (est)];


My query uses a crosstab query called 'Core parts2' (bad naming practice, I
now know) as a table.
I tried the DSum function in a calculated field called RunSum2. It's summing
the count of Part #2.
The field for Part #2 is called "2", which is how it was generated in my
crosstab. I realize that's probably also bad naming convention, but I
haven't discovered how to rename those column names that are generated in my
crosstab query.
DrumSizeID is one of my variables (what I referred to as V1 in my post).

The expression that I came up with for that calculated field is as follows:

RunSum2: DSum("2","Core parts2","[DrumSizeID]= " & [Core parts2].DrumSizeID)

I had a really hard time working out a syntax for the DSum criteria of "field
= current field".
I stumbled across a few examples on other posts here, but the syntax was not
easy to deduce.
I have seen lots of ampersands and single quotes and double quotes but am not
sure of the significance.
Can you point me to a reference that describes how that sort of syntax should
be constructed?
(I couldn't even come up with a good set of search terms to find info on this
syntax!)

Thank you very much for taking the time to help me on this.
I appreciate your assistance!

Best regards,
Doug

You can achieve your Running Sum if you have a distinct date/Time field (ie
you only have 1 delivery per day or you record the time as well as the date
of the delivery) or some accumulative field that increases from record to
record perhaps your Autonumber if records are always entered
chronologically.
You could use in your report's query.
DSum the Value field with the criteria that YourVariable = Your current
Variable and Part Number = Your current part number and your date or
Autonumber is less than your current one.

You would need to circle that with the NZ function so that your first record
would just have the current value.
You would then add that result to your current value
If you need help with this, give us the field names involved and what data
type they are (number, text, date) then

Evi

Can I somehow create the cumulative sum in a totals query and then show it in
the report??

[quoted text clipped - 54 lines]

 

[DougW via AccessMonster.com]

Evi,
I was definitely able to get the crosstab query to work as the source of the
select query.
I suppose it's not a good practice (?) but it does seem to work fine in this
case.

Thanks for the reference to Allen Browne's page explaining the syntax.
That's exactly what I was
looking for. I see what you mean about DSum etc being "a bit of a pig",
especially with some
of the long field names I used - another lesson learned for me

I also notice that using the DSum in that context does really bog down the
program - the query runs
slowly and I can see the individual rows update. And that is with only one
DSum currently in place
(I would eventually have 11 of them to produce the information I needed).

After some more thought yesterday I came up with an alternate strategy to
sort and sum the
information I am looking for. I thought to create a new query (call it C)
which will produce a particular
set of my criteria variables (my V1 V2 V3 etc), and then placing this query
in my select query (B)
along with the crosstab query (A), and creating joins between the appropriate
fields, to result in a
"filtered" recordset that contains the results (parts by date delivered) for
one particular case (set
of criteria variables). I tried this for one case and it worked fine. I
think I could do the
summing I needed to in this query (B) - then take the resulting record set
and append it to a "results"
table (D). I would then repeat the process as needed to iterate through the
various criteria combinations in query (C), each time appending the records
to my table (D).

My results table D would then be the record source for my final report.
I would need to make some kind of macro to automate the iteration of my
criteria variables,
run the query B and append the records to D. If this process would work I
think I could avoid
the aggregate DSum summing.

Does this alternate strategy sound feasible? If you have any thoughts or
suggestions on that
I would appreciate it. In any case thank you very much for your help, it is
greatly appreciated.

-- Doug

Are you sure you were able to get it to work? I've never seen anyone using a
Crosstab query as the Source for a Select Query before (though that doesn't
mean it's wrong to do it as far as I know)

DSum/DLookup/DCount/DMax/DMin syntax is a bit of a pig but look how many
functions you get for the price - the syntax is the same for all.
I gave you the merest summary in case it wasn't what you needed.
About the clearest summary of the syntax is on Allen Browne's page
http://www.allenbrowne.com/casu-07.html

If you need help for your specific DSum then give me the names of these
fields and say what data type they are in their table (text, Date, or
Number)
Tell me
1.The field you want to Sum (so it has to be a field containing numbers)
2. The field that contains the chronoligical value so we know which order
the records are in (that field needs to be in the Query where you are
looking up your data and also in your current query.
3. the name of the Query or Table that contains the fields you want to
sum.

Evi

Evi, thanks for the response.
I tried what you described and was able to get it to work.

[quoted text clipped - 79 lines]

 

[Evi]

I can't advise you on the query you are planning to create - I've never
tried that sort of thing before.

You are absolutely correct that DSums etc slow things down. To date,
whenever I've been faced with more than 5 'D's in a report it usually means
that I've missed an alternative; stuff like as Totals Query as a subreport
or (ugh!) a total redesign of my database. Duane Hookom has done some fairly
amazing things with Union queries and Temporary Tables (well over my head!)
in this newsgroup and microsoft.public.access.queries. It may be worth a
search on his name to see if he's answered a similar problem to your own.

Evi

Evi,
I was definitely able to get the crosstab query to work as the source of the
select query.
I suppose it's not a good practice (?) but it does seem to work fine in this
case.

Thanks for the reference to Allen Browne's page explaining the syntax.
That's exactly what I was
looking for. I see what you mean about DSum etc being "a bit of a pig",
especially with some
of the long field names I used - another lesson learned for me

I also notice that using the DSum in that context does really bog down the
program - the query runs
slowly and I can see the individual rows update. And that is with only one
DSum currently in place
(I would eventually have 11 of them to produce the information I needed).

After some more thought yesterday I came up with an alternate strategy to
sort and sum the
information I am looking for. I thought to create a new query (call it C)
which will produce a particular
set of my criteria variables (my V1 V2 V3 etc), and then placing this query
in my select query (B)
along with the crosstab query (A), and creating joins between the appropriate
fields, to result in a
"filtered" recordset that contains the results (parts by date delivered) for
one particular case (set
of criteria variables). I tried this for one case and it worked fine. I
think I could do the
summing I needed to in this query (B) - then take the resulting record set
and append it to a "results"
table (D). I would then repeat the process as needed to iterate through the
various criteria combinations in query (C), each time appending the records
to my table (D).

My results table D would then be the record source for my final report.
I would need to make some kind of macro to automate the iteration of my
criteria variables,
run the query B and append the records to D. If this process would work I
think I could avoid
the aggregate DSum summing.

Does this alternate strategy sound feasible? If you have any thoughts or
suggestions on that
I would appreciate it. In any case thank you very much for your help, it is
greatly appreciated.

-- Doug

Are you sure you were able to get it to work? I've never seen anyone using a
Crosstab query as the Source for a Select Query before (though that doesn't
mean it's wrong to do it as far as I know)

DSum/DLookup/DCount/DMax/DMin syntax is a bit of a pig but look how many
functions you get for the price - the syntax is the same for all.
I gave you the merest summary in case it wasn't what you needed.
About the clearest summary of the syntax is on Allen Browne's page
http://www.allenbrowne.com/casu-07.html

If you need help for your specific DSum then give me the names of these
fields and say what data type they are in their table (text, Date, or
Number)
Tell me
1.The field you want to Sum (so it has to be a field containing numbers)
2. The field that contains the chronoligical value so we know which order
the records are in (that field needs to be in the Query where you are
looking up your data and also in your current query.
3. the name of the Query or Table that contains the fields you want to
sum.

Evi

Evi, thanks for the response.
I tried what you described and was able to get it to work.

[quoted text clipped - 79 lines]