Developer Forum

Would you post the query you are attempting?

--
Smartin

Steve,

I can help you despite Smartin's laziness.

Open up your query design view and add the table you listed below -
let's call it PartTable.

Add the fields Part, Date and qty.

Now click on the "sigma" icon on the toolbar. This icon looks like a
fancy uppercase "E." Once you click on it your query below will gain an
additional line called "Total."


For the field "Part" set the Total line on the query to "group by."

For the field "Date" set the Total line on the query to "group by."

For the field "qty" set the Total line on the query to "SUM"

Run that ish!

Woot!

Love,
Diane

This is a multi-part message in MIME format.
Diane,

Nice, but your solution gives a grand total by part. The poster (Steve)
wants a running total by part and date.

My question is does the poster need this in the query or in a report.
In a report it can be done rather simply by grouping on part in the
report and adding a control that does a running sum over group of the
the qty field.

In a query, it will require a subquery. An accurate solution would be
easier to build if we knew what the poster had tried. So I wouldn't say
that asking for additional information was being lazy.

The query for this might look something like

SELECT A.Part
, A.Date
, A.qty
, (SELECT Sum(B.Qty)
FROM SomeTable as B
WHERE B.Part =A.Part AND
B.Date <= A.Date) as RunningSum
FROM SomeTable as A

That solution has a problem if there is a part and date combination that
is duplicated in the original table. Then things will get double counted.

John Spencer
Access MVP 2002-2005, 2007

hanks for your eagle-eye John!

I think you could take the query I wrote, then create an additional
query based on it additing the date and quantity fields, grouping by
date and summing by quantity.

I think we can both agree that tables don't lend to running totals -
queries and reports are much more appropriate.


John Spencer wrote:

iane,
Here is the table structure
Part number, Date, Quantity
At each change in part number, date change I need a quantity total. I have
already summed the quantity per part per date in a preceeding query.
So my data looks like this
RQCOM RQDUD RQQTY Total
07017 1071019 16 16
07017 1071022 235 235
07017 1071024 2 2
07017 1071026 2 2
07017 1071010 400 400
19056 1070823 4 4
19056 1070111 14 14
19056 1070215 2 2
19056 1070419 2 2
19056 1070524 2 2
19056 1070719 4 4
What I am trying to get is the following
RQCOM RQDUD RQQTY Total Running Total
07017 1071019 16 16 16
07017 1071022 235 235 251
07017 1071024 2 2 253
07017 1071026 2 2 255
07017 1071010 400 400 655
19056 1070823 4 4 4
19056 1070111 14 14 18
19056 1070215 2 2 20
19056 1070419 2 2 22
19056 1070524 2 2 24
19056 1070719 4 4 28
Thanks in advance for your help
Steve

" XXXX@XXXXX.COM " wrote:

azy Smartin Sez...

I can do this by adding a sequential number field to your table to keep
the rows in order. Your table does not seem to have a definitive order.
This solution also gets around the potential problem of counting rows
twice when a value of RQDUD exists more than once per RQCOM.

SELECT
A.ID
,A.RQCOM
,A.RQDUD
,A.RQQTY
,A.Total
,(SELECT SUM(B.Total)
FROM Steve AS B
WHERE B.ID <= A.ID AND B.RQCOM = A.RQCOM
) AS RunningTotal
FROM Steve AS A
ORDER BY A.ID

Result:

ID RQCOM RQDUD RQQTY Total RunningTotal
1 7017 1071019 16 16 16
2 7017 1071022 235 235 251
3 7017 1071024 2 2 253
4 7017 1071026 2 2 255
5 7017 1071010 400 400 655
6 19056 1070823 4 4 4
7 19056 1070111 14 14 18
8 19056 1070215 2 2 20
9 19056 1070419 2 2 22
10 19056 1070524 2 2 24
11 19056 1070719 4 4 28


Steve wrote:


--
Smartin

ardon me for butting in.....

looks to me like you just missed use
of alias B in Smartin's example:

SELECT
requirements.ID,
requirements.RQCOM,
requirements.RQDUD,
requirements.RQQTY,
(SELECT SUM(B.RQQTY)
FROM requirements AS B
WHERE
B.ID <= requirements.ID
AND
B.RQCOM = requirements.RQCOM) AS RunningTotal
INTO
[requirements totaled]
FROM requirements
ORDER BY requirements.ID;

"Steve" .

ary,
Thank you I did miss that. So I added it to my query like this

SELECT
Requirements.ID,
requirements.RQCOM,
requirements.RQDUD,
requirements.Total,
(SELECT SUM(requirements.Total)
FROM requirements AS B
WHERE
B.ID <= requirements.ID
AND
B.RQCOM = requirements.RQCOM ) AS RunningTotal
INTO
[requirements totaled]
FROM requirements
ORDER BY requirements.ID;


Input table looks like this
ID RQCOM RQDUD Total
1 00934 1080423 53
2 00934 1080522 270
3 00935 1080423 6
4 00935 1080522 38
5 00976 1070116 1
6 00976 1070420 1
7 00976 1070524 1
8 00976 1070608 1
9 00976 1070629 1
10 00976 1070720 1
11 00976 1070726 1
12 00976 1070727 1
13 00976 1070824 1
14 00976 1071019 2
15 00976 1071026 1
16 01007 1061229 79
17 01007 1070115 1
18 01007 1070129 200
19 01007 1070507 200
20 01007 1070730 1
21 01007 1070813 201
22 01007 1071119 200
23 01007 1080215 200

Output table looks like this
ID RQCOM RQDUD Total RunningTotal
1 00934 1080423 53 53
2 00934 1080522 270 540
3 00935 1080423 6 6
4 00935 1080522 38 76
5 00976 1070116 1 1
6 00976 1070420 1 2
7 00976 1070524 1 3
8 00976 1070608 1 4
9 00976 1070629 1 5
10 00976 1070720 1 6
11 00976 1070726 1 7
12 00976 1070727 1 8
13 00976 1070824 1 9
14 00976 1071019 2 20
15 00976 1071026 1 11
16 01007 1061229 79 79
17 01007 1070115 1 2
18 01007 1070129 200 600
19 01007 1070507 200 800
20 01007 1070730 1 5
21 01007 1070813 201 1206
22 01007 1071119 200 1400
23 01007 1080215 200 1600
Any help will be greatly appreciated.

Thanks,
Steve

"Gary Walter" wrote:

lease look at where B is used in subquery.

"Steve" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

ary,
This is my first time using subquerys, so I really do not understand what I
am looking for.
Sorry to seem ignorant but I am trying to learn
Steve

"Gary Walter" wrote:

SELECT
Requirements.ID,
requirements.RQCOM,
requirements.RQDUD,
requirements.Total,
(SELECT SUM(B.Total) <----*****you need B here***
FROM requirements AS B
WHERE
B.ID <= requirements.ID
AND
B.RQCOM = requirements.RQCOM ) AS RunningTotal
INTO
[requirements totaled]
FROM requirements
ORDER BY requirements.ID;

Gary,
Thanks so much.
I looked at that darn query for hours and could not see the forest for the
trees.
Thanks again,
Steve

HI STEVE,

your Question was great!!! 3 days i'm working on the same issue.
thanks