Running Sum

A

Andy

Hello Friends,
Finally I achived Running Sum in Query Table with your guidence. But still
one Problem!!!
Here is my Sql Statement_
SELECT HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo,
Sum(HMADetail.HMAPlaced) AS SumOfHMAPlaced,
DSum("[HMAPlaced]","HMADetail","[HMADetail].[LotNo]&[HMADetail].[SubLotNo]<="
& HMADetail.LotNo & HMADetail.SubLotNo) AS cumulativeHMAPlaced
FROM HMADetail
GROUP BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo
ORDER BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo;

This Query gives me following_
Date LotNo SubLotNo SumOfHMAPlaced cumulativeHMAPlaced
05/01/2007 1 1 500 500
18/01/2007 1 2 500 1000
18/01/2007 1 3 500 1500
18/01/2007 1 4 500 2000
18/03/2007 1 5 500 2500
18/03/2007 1 6 500 3000
23/03/2007 1 7 500 3500
23/03/2007 1 8 500 4000
27/03/2007 1 9 500 4500
27/03/2007 1 10 500 6500
10/04/2007 2 1 500 5000
10/04/2007 2 2 500 5500
10/04/2007 2 3 500 6000
The Cumulative Sum for SubLot 10 is not correct. I think it is may be
because all nos in lot and SubLots are single digit and sublot 10 is double
digit? if so then how can I change those no with 0 as prefix? ie. 1=01?
(Note: When I change 10 to 9 then all cumuletive su for all records comes
correct)

Andy
 
M

Michel Walsh

Your fields for lotNo and SubLotNo are integers, right? If not, try to make
them so.

Also, you use a lot of concatenation. Such strings handling, under Windows,
is not very efficient, in general. You are right about having to use
constant length substrings if you want to compare lot and sub lot as you do.
Assuming two characters are enough:


DSum("[HMAPlaced]","HMADetail","Format([HMADetail].[LotNo], '00') &
[HMADetail].[SubLotNo]<="
& Format(HMADetail.LotNo, "00") & HMADetail.SubLotNo) AS
cumulativeHMAPlaced


Definitively, a join should be much faster, since a join CAN use index, your
computed DSUM criteria just cannot, in that case. A join is not much harder
than the DSum, and in fact, it is simpler:


SELECT a.Date, a.LotNo, a.SubLotNo,
Sum(a.HMAPlaced) AS SumOfHMAPlaced,
Sum(b.HMAPlaced) AS cumulativeHMAPlaced

FROM HMADetail AS a INNER JOIN HMADetail As b
ON b.lotNo < a.LotNo OR (b.lotNo = a.lotNo AND b.SubLotNo<= a.SubLotNo)

GROUP BY a.Date, a.LotNo, a.SubLotNo
ORDER BY a.Date, a.LotNo, a.SubLotNo;




There is still something that seems fishy about the date, since you don't
really use it in the ordering criteria, while 'previously', in my mind,
imply date.

Note: that assumes that LotNo and SubLotNo ARE numbers, not string!





Vanderghast, Access MVP
 
A

Andy

Hi Michel,
LotNo And SubLotNo Long INteger Fields. Actualy I am Learning Access through
Discussion Group and Help manu. I Have Created some tables and Queries and
Reports that I am sing at my job that makes my life easier!!!!!

you said I am using lot of concatenation. In reality I don't know what it
is!!!!!!!

I tried your Query by replacing HMADetail for 'a' and HMADetail_1 for 'b'
then I gets error Message. If possible would u please send me Exact Query
statementso that I copy and pest. Normay I create Query from vizards. I tried
your Query through Query vizards and check it on sql window I can't get the
same as yours
Andy

Michel Walsh said:
Your fields for lotNo and SubLotNo are integers, right? If not, try to make
them so.

Also, you use a lot of concatenation. Such strings handling, under Windows,
is not very efficient, in general. You are right about having to use
constant length substrings if you want to compare lot and sub lot as you do.
Assuming two characters are enough:


DSum("[HMAPlaced]","HMADetail","Format([HMADetail].[LotNo], '00') &
[HMADetail].[SubLotNo]<="
& Format(HMADetail.LotNo, "00") & HMADetail.SubLotNo) AS
cumulativeHMAPlaced


Definitively, a join should be much faster, since a join CAN use index, your
computed DSUM criteria just cannot, in that case. A join is not much harder
than the DSum, and in fact, it is simpler:


SELECT a.Date, a.LotNo, a.SubLotNo,
Sum(a.HMAPlaced) AS SumOfHMAPlaced,
Sum(b.HMAPlaced) AS cumulativeHMAPlaced

FROM HMADetail AS a INNER JOIN HMADetail As b
ON b.lotNo < a.LotNo OR (b.lotNo = a.lotNo AND b.SubLotNo<= a.SubLotNo)

GROUP BY a.Date, a.LotNo, a.SubLotNo
ORDER BY a.Date, a.LotNo, a.SubLotNo;




There is still something that seems fishy about the date, since you don't
really use it in the ordering criteria, while 'previously', in my mind,
imply date.

Note: that assumes that LotNo and SubLotNo ARE numbers, not string!





Vanderghast, Access MVP


Andy said:
Hello Friends,
Finally I achived Running Sum in Query Table with your guidence. But still
one Problem!!!
Here is my Sql Statement_
SELECT HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo,
Sum(HMADetail.HMAPlaced) AS SumOfHMAPlaced,
DSum("[HMAPlaced]","HMADetail","[HMADetail].[LotNo]&[HMADetail].[SubLotNo]<="
& HMADetail.LotNo & HMADetail.SubLotNo) AS cumulativeHMAPlaced
FROM HMADetail
GROUP BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo
ORDER BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo;

This Query gives me following_
Date LotNo SubLotNo SumOfHMAPlaced cumulativeHMAPlaced
05/01/2007 1 1 500 500
18/01/2007 1 2 500 1000
18/01/2007 1 3 500 1500
18/01/2007 1 4 500 2000
18/03/2007 1 5 500 2500
18/03/2007 1 6 500 3000
23/03/2007 1 7 500 3500
23/03/2007 1 8 500 4000
27/03/2007 1 9 500 4500
27/03/2007 1 10 500 6500
10/04/2007 2 1 500 5000
10/04/2007 2 2 500 5500
10/04/2007 2 3 500 6000
The Cumulative Sum for SubLot 10 is not correct. I think it is may be
because all nos in lot and SubLots are single digit and sublot 10 is
double
digit? if so then how can I change those no with 0 as prefix? ie. 1=01?
(Note: When I change 10 to 9 then all cumuletive su for all records comes
correct)

Andy
Click to expand...
Click to expand...
 
M

Michel Walsh

The following works fine, in Access 2003:


Andy said:
Hi Michel,
LotNo And SubLotNo Long INteger Fields. Actualy I am Learning Access
through
Discussion Group and Help manu. I Have Created some tables and Queries and
Reports that I am sing at my job that makes my life easier!!!!!

you said I am using lot of concatenation. In reality I don't know what it
is!!!!!!!

I tried your Query by replacing HMADetail for 'a' and HMADetail_1 for 'b'
then I gets error Message. If possible would u please send me Exact Query
statementso that I copy and pest. Normay I create Query from vizards. I
tried
your Query through Query vizards and check it on sql window I can't get
the
same as yours
Andy

Michel Walsh said:
Your fields for lotNo and SubLotNo are integers, right? If not, try to
make
them so.

Also, you use a lot of concatenation. Such strings handling, under
Windows,
is not very efficient, in general. You are right about having to use
constant length substrings if you want to compare lot and sub lot as you
do.
Assuming two characters are enough:


DSum("[HMAPlaced]","HMADetail","Format([HMADetail].[LotNo], '00') &
[HMADetail].[SubLotNo]<="
& Format(HMADetail.LotNo, "00") & HMADetail.SubLotNo) AS
cumulativeHMAPlaced


Definitively, a join should be much faster, since a join CAN use index,
your
computed DSUM criteria just cannot, in that case. A join is not much
harder
than the DSum, and in fact, it is simpler:


SELECT a.Date, a.LotNo, a.SubLotNo,
Sum(a.HMAPlaced) AS SumOfHMAPlaced,
Sum(b.HMAPlaced) AS cumulativeHMAPlaced

FROM HMADetail AS a INNER JOIN HMADetail As b
ON b.lotNo < a.LotNo OR (b.lotNo = a.lotNo AND b.SubLotNo<=
a.SubLotNo)

GROUP BY a.Date, a.LotNo, a.SubLotNo
ORDER BY a.Date, a.LotNo, a.SubLotNo;




There is still something that seems fishy about the date, since you don't
really use it in the ordering criteria, while 'previously', in my mind,
imply date.

Note: that assumes that LotNo and SubLotNo ARE numbers, not string!





Vanderghast, Access MVP


Andy said:
Hello Friends,
Finally I achived Running Sum in Query Table with your guidence. But
still
one Problem!!!
Here is my Sql Statement_
SELECT HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo,
Sum(HMADetail.HMAPlaced) AS SumOfHMAPlaced,
DSum("[HMAPlaced]","HMADetail","[HMADetail].[LotNo]&[HMADetail].[SubLotNo]<="
& HMADetail.LotNo & HMADetail.SubLotNo) AS cumulativeHMAPlaced
FROM HMADetail
GROUP BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo
ORDER BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo;

This Query gives me following_
Date LotNo SubLotNo SumOfHMAPlaced cumulativeHMAPlaced
05/01/2007 1 1 500 500
18/01/2007 1 2 500 1000
18/01/2007 1 3 500 1500
18/01/2007 1 4 500 2000
18/03/2007 1 5 500 2500
18/03/2007 1 6 500 3000
23/03/2007 1 7 500 3500
23/03/2007 1 8 500 4000
27/03/2007 1 9 500 4500
27/03/2007 1 10 500 6500
10/04/2007 2 1 500 5000
10/04/2007 2 2 500 5500
10/04/2007 2 3 500 6000
The Cumulative Sum for SubLot 10 is not correct. I think it is may be
because all nos in lot and SubLots are single digit and sublot 10 is
double
digit? if so then how can I change those no with 0 as prefix? ie.
1=01?
(Note: When I change 10 to 9 then all cumuletive su for all records
comes
correct)

Andy
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

The following works fine in Access 2005:


SELECT a.Date, a.LotNo, a.SubLotNo,
Sum(a.HMAPlaced) AS SumOfHMAPlaced,
Sum(b.HMAPlaced) AS cumulativeHMAPlaced

FROM HMADetail AS a INNER JOIN HMADetail As b
ON (b.lotNo < a.LotNo OR (b.lotNo = a.lotNo AND b.SubLotNo<=
a.SubLotNo))

GROUP BY a.Date, a.LotNo, a.SubLotNo
ORDER BY a.Date, a.LotNo, a.SubLotNo;



(I just needed to add a pair or () to delimit the ON clause properly).



Concatenation is 'gluing' two strings together:


? "Hello" & "World"
HelloWorld


Note that is does not add, automatically, a space.

It is a word coming from Latin:
con : together
catena : chain




Vanderghast, Access MVP


Andy said:
Hi Michel,
LotNo And SubLotNo Long INteger Fields. Actualy I am Learning Access
through
Discussion Group and Help manu. I Have Created some tables and Queries and
Reports that I am sing at my job that makes my life easier!!!!!

you said I am using lot of concatenation. In reality I don't know what it
is!!!!!!!

I tried your Query by replacing HMADetail for 'a' and HMADetail_1 for 'b'
then I gets error Message. If possible would u please send me Exact Query
statementso that I copy and pest. Normay I create Query from vizards. I
tried
your Query through Query vizards and check it on sql window I can't get
the
same as yours
Andy

Michel Walsh said:
Your fields for lotNo and SubLotNo are integers, right? If not, try to
make
them so.

Also, you use a lot of concatenation. Such strings handling, under
Windows,
is not very efficient, in general. You are right about having to use
constant length substrings if you want to compare lot and sub lot as you
do.
Assuming two characters are enough:


DSum("[HMAPlaced]","HMADetail","Format([HMADetail].[LotNo], '00') &
[HMADetail].[SubLotNo]<="
& Format(HMADetail.LotNo, "00") & HMADetail.SubLotNo) AS
cumulativeHMAPlaced


Definitively, a join should be much faster, since a join CAN use index,
your
computed DSUM criteria just cannot, in that case. A join is not much
harder
than the DSum, and in fact, it is simpler:


SELECT a.Date, a.LotNo, a.SubLotNo,
Sum(a.HMAPlaced) AS SumOfHMAPlaced,
Sum(b.HMAPlaced) AS cumulativeHMAPlaced

FROM HMADetail AS a INNER JOIN HMADetail As b
ON b.lotNo < a.LotNo OR (b.lotNo = a.lotNo AND b.SubLotNo<=
a.SubLotNo)

GROUP BY a.Date, a.LotNo, a.SubLotNo
ORDER BY a.Date, a.LotNo, a.SubLotNo;




There is still something that seems fishy about the date, since you don't
really use it in the ordering criteria, while 'previously', in my mind,
imply date.

Note: that assumes that LotNo and SubLotNo ARE numbers, not string!





Vanderghast, Access MVP


Andy said:
Hello Friends,
Finally I achived Running Sum in Query Table with your guidence. But
still
one Problem!!!
Here is my Sql Statement_
SELECT HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo,
Sum(HMADetail.HMAPlaced) AS SumOfHMAPlaced,
DSum("[HMAPlaced]","HMADetail","[HMADetail].[LotNo]&[HMADetail].[SubLotNo]<="
& HMADetail.LotNo & HMADetail.SubLotNo) AS cumulativeHMAPlaced
FROM HMADetail
GROUP BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo
ORDER BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo;

This Query gives me following_
Date LotNo SubLotNo SumOfHMAPlaced cumulativeHMAPlaced
05/01/2007 1 1 500 500
18/01/2007 1 2 500 1000
18/01/2007 1 3 500 1500
18/01/2007 1 4 500 2000
18/03/2007 1 5 500 2500
18/03/2007 1 6 500 3000
23/03/2007 1 7 500 3500
23/03/2007 1 8 500 4000
27/03/2007 1 9 500 4500
27/03/2007 1 10 500 6500
10/04/2007 2 1 500 5000
10/04/2007 2 2 500 5500
10/04/2007 2 3 500 6000
The Cumulative Sum for SubLot 10 is not correct. I think it is may be
because all nos in lot and SubLots are single digit and sublot 10 is
double
digit? if so then how can I change those no with 0 as prefix? ie.
1=01?
(Note: When I change 10 to 9 then all cumuletive su for all records
comes
correct)

Andy
Click to expand...
Click to expand...
Click to expand...
 
A

Andy

Michel

This Query works good. Thank you very much.
But I can't open this query in Design view what is the cause for this?
Gives me this message_
Microsoft Access can't represent join expression b.LotNo<a.LotNo
OR(b.LotNo=a.LotNo And b.SubLotNo<=a.SubLotNo) In Design View"
Andy

Michel Walsh said:
The following works fine in Access 2005:


SELECT a.Date, a.LotNo, a.SubLotNo,
Sum(a.HMAPlaced) AS SumOfHMAPlaced,
Sum(b.HMAPlaced) AS cumulativeHMAPlaced

FROM HMADetail AS a INNER JOIN HMADetail As b
ON (b.lotNo < a.LotNo OR (b.lotNo = a.lotNo AND b.SubLotNo<=
a.SubLotNo))

GROUP BY a.Date, a.LotNo, a.SubLotNo
ORDER BY a.Date, a.LotNo, a.SubLotNo;



(I just needed to add a pair or () to delimit the ON clause properly).



Concatenation is 'gluing' two strings together:


? "Hello" & "World"
HelloWorld


Note that is does not add, automatically, a space.

It is a word coming from Latin:
con : together
catena : chain




Vanderghast, Access MVP


Andy said:
Hi Michel,
LotNo And SubLotNo Long INteger Fields. Actualy I am Learning Access
through
Discussion Group and Help manu. I Have Created some tables and Queries and
Reports that I am sing at my job that makes my life easier!!!!!

you said I am using lot of concatenation. In reality I don't know what it
is!!!!!!!

I tried your Query by replacing HMADetail for 'a' and HMADetail_1 for 'b'
then I gets error Message. If possible would u please send me Exact Query
statementso that I copy and pest. Normay I create Query from vizards. I
tried
your Query through Query vizards and check it on sql window I can't get
the
same as yours
Andy

Michel Walsh said:
Your fields for lotNo and SubLotNo are integers, right? If not, try to
make
them so.

Also, you use a lot of concatenation. Such strings handling, under
Windows,
is not very efficient, in general. You are right about having to use
constant length substrings if you want to compare lot and sub lot as you
do.
Assuming two characters are enough:


DSum("[HMAPlaced]","HMADetail","Format([HMADetail].[LotNo], '00') &
[HMADetail].[SubLotNo]<="
& Format(HMADetail.LotNo, "00") & HMADetail.SubLotNo) AS
cumulativeHMAPlaced


Definitively, a join should be much faster, since a join CAN use index,
your
computed DSUM criteria just cannot, in that case. A join is not much
harder
than the DSum, and in fact, it is simpler:


SELECT a.Date, a.LotNo, a.SubLotNo,
Sum(a.HMAPlaced) AS SumOfHMAPlaced,
Sum(b.HMAPlaced) AS cumulativeHMAPlaced

FROM HMADetail AS a INNER JOIN HMADetail As b
ON b.lotNo < a.LotNo OR (b.lotNo = a.lotNo AND b.SubLotNo<=
a.SubLotNo)

GROUP BY a.Date, a.LotNo, a.SubLotNo
ORDER BY a.Date, a.LotNo, a.SubLotNo;




There is still something that seems fishy about the date, since you don't
really use it in the ordering criteria, while 'previously', in my mind,
imply date.

Note: that assumes that LotNo and SubLotNo ARE numbers, not string!





Vanderghast, Access MVP


Hello Friends,
Finally I achived Running Sum in Query Table with your guidence. But
still
one Problem!!!
Here is my Sql Statement_
SELECT HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo,
Sum(HMADetail.HMAPlaced) AS SumOfHMAPlaced,
DSum("[HMAPlaced]","HMADetail","[HMADetail].[LotNo]&[HMADetail].[SubLotNo]<="
& HMADetail.LotNo & HMADetail.SubLotNo) AS cumulativeHMAPlaced
FROM HMADetail
GROUP BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo
ORDER BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo;

This Query gives me following_
Date LotNo SubLotNo SumOfHMAPlaced cumulativeHMAPlaced
05/01/2007 1 1 500 500
18/01/2007 1 2 500 1000
18/01/2007 1 3 500 1500
18/01/2007 1 4 500 2000
18/03/2007 1 5 500 2500
18/03/2007 1 6 500 3000
23/03/2007 1 7 500 3500
23/03/2007 1 8 500 4000
27/03/2007 1 9 500 4500
27/03/2007 1 10 500 6500
10/04/2007 2 1 500 5000
10/04/2007 2 2 500 5500
10/04/2007 2 3 500 6000
The Cumulative Sum for SubLot 10 is not correct. I think it is may be
because all nos in lot and SubLots are single digit and sublot 10 is
double
digit? if so then how can I change those no with 0 as prefix? ie.
1=01?
(Note: When I change 10 to 9 then all cumuletive su for all records
comes
correct)

Andy
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Because the graphical designer can only represents an equi-join, a join with
a = comparison, and only allow AND, if more than one comparison is involved;
while here, we use a compound comparison with OR, and it is not only
implying = but < and <= too.



Vanderghast, Access MVP


Andy said:
Michel

This Query works good. Thank you very much.
But I can't open this query in Design view what is the cause for this?
Gives me this message_
Microsoft Access can't represent join expression b.LotNo<a.LotNo
OR(b.LotNo=a.LotNo And b.SubLotNo<=a.SubLotNo) In Design View"
Andy

Michel Walsh said:
The following works fine in Access 2005:


SELECT a.Date, a.LotNo, a.SubLotNo,
Sum(a.HMAPlaced) AS SumOfHMAPlaced,
Sum(b.HMAPlaced) AS cumulativeHMAPlaced

FROM HMADetail AS a INNER JOIN HMADetail As b
ON (b.lotNo < a.LotNo OR (b.lotNo = a.lotNo AND b.SubLotNo<=
a.SubLotNo))

GROUP BY a.Date, a.LotNo, a.SubLotNo
ORDER BY a.Date, a.LotNo, a.SubLotNo;



(I just needed to add a pair or () to delimit the ON clause properly).



Concatenation is 'gluing' two strings together:


? "Hello" & "World"
HelloWorld


Note that is does not add, automatically, a space.

It is a word coming from Latin:
con : together
catena : chain




Vanderghast, Access MVP


Andy said:
Hi Michel,
LotNo And SubLotNo Long INteger Fields. Actualy I am Learning Access
through
Discussion Group and Help manu. I Have Created some tables and Queries
and
Reports that I am sing at my job that makes my life easier!!!!!

you said I am using lot of concatenation. In reality I don't know what
it
is!!!!!!!

I tried your Query by replacing HMADetail for 'a' and HMADetail_1 for
'b'
then I gets error Message. If possible would u please send me Exact
Query
statementso that I copy and pest. Normay I create Query from vizards. I
tried
your Query through Query vizards and check it on sql window I can't
get
the
same as yours
Andy

:

Your fields for lotNo and SubLotNo are integers, right? If not, try
to
make
them so.

Also, you use a lot of concatenation. Such strings handling, under
Windows,
is not very efficient, in general. You are right about having to use
constant length substrings if you want to compare lot and sub lot as
you
do.
Assuming two characters are enough:


DSum("[HMAPlaced]","HMADetail","Format([HMADetail].[LotNo], '00') &
[HMADetail].[SubLotNo]<="
& Format(HMADetail.LotNo, "00") & HMADetail.SubLotNo) AS
cumulativeHMAPlaced


Definitively, a join should be much faster, since a join CAN use
index,
your
computed DSUM criteria just cannot, in that case. A join is not much
harder
than the DSum, and in fact, it is simpler:


SELECT a.Date, a.LotNo, a.SubLotNo,
Sum(a.HMAPlaced) AS SumOfHMAPlaced,
Sum(b.HMAPlaced) AS cumulativeHMAPlaced

FROM HMADetail AS a INNER JOIN HMADetail As b
ON b.lotNo < a.LotNo OR (b.lotNo = a.lotNo AND b.SubLotNo<=
a.SubLotNo)

GROUP BY a.Date, a.LotNo, a.SubLotNo
ORDER BY a.Date, a.LotNo, a.SubLotNo;




There is still something that seems fishy about the date, since you
don't
really use it in the ordering criteria, while 'previously', in my
mind,
imply date.

Note: that assumes that LotNo and SubLotNo ARE numbers, not string!





Vanderghast, Access MVP


Hello Friends,
Finally I achived Running Sum in Query Table with your guidence. But
still
one Problem!!!
Here is my Sql Statement_
SELECT HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo,
Sum(HMADetail.HMAPlaced) AS SumOfHMAPlaced,
DSum("[HMAPlaced]","HMADetail","[HMADetail].[LotNo]&[HMADetail].[SubLotNo]<="
& HMADetail.LotNo & HMADetail.SubLotNo) AS cumulativeHMAPlaced
FROM HMADetail
GROUP BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo
ORDER BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo;

This Query gives me following_
Date LotNo SubLotNo SumOfHMAPlaced cumulativeHMAPlaced
05/01/2007 1 1 500 500
18/01/2007 1 2 500 1000
18/01/2007 1 3 500 1500
18/01/2007 1 4 500 2000
18/03/2007 1 5 500 2500
18/03/2007 1 6 500 3000
23/03/2007 1 7 500 3500
23/03/2007 1 8 500 4000
27/03/2007 1 9 500 4500
27/03/2007 1 10 500 6500
10/04/2007 2 1 500 5000
10/04/2007 2 2 500 5500
10/04/2007 2 3 500 6000
The Cumulative Sum for SubLot 10 is not correct. I think it is may
be
because all nos in lot and SubLots are single digit and sublot 10 is
double
digit? if so then how can I change those no with 0 as prefix? ie.
1=01?
(Note: When I change 10 to 9 then all cumuletive su for all records
comes
correct)

Andy
Click to expand...
Click to expand...
Click to expand...
 
A

Andy

Michel ,

with OR criteria ie <= in sql statement it doesn't open design view query?

But again I used the DSum Query and changed The SubLotNo field from Integer
to Text and changed data in table with 0 prefix, Query is working perfect now.

Michel I apreciate you so much that I learn lot of things from you. You are
wonderful. you responded questions very promptly. Thanks again
Andy

Michel Walsh said:
Because the graphical designer can only represents an equi-join, a join with
a = comparison, and only allow AND, if more than one comparison is involved;
while here, we use a compound comparison with OR, and it is not only
implying = but < and <= too.



Vanderghast, Access MVP


Andy said:
Michel

This Query works good. Thank you very much.
But I can't open this query in Design view what is the cause for this?
Gives me this message_
Microsoft Access can't represent join expression b.LotNo<a.LotNo
OR(b.LotNo=a.LotNo And b.SubLotNo<=a.SubLotNo) In Design View"
Andy

Michel Walsh said:
The following works fine in Access 2005:


SELECT a.Date, a.LotNo, a.SubLotNo,
Sum(a.HMAPlaced) AS SumOfHMAPlaced,
Sum(b.HMAPlaced) AS cumulativeHMAPlaced

FROM HMADetail AS a INNER JOIN HMADetail As b
ON (b.lotNo < a.LotNo OR (b.lotNo = a.lotNo AND b.SubLotNo<=
a.SubLotNo))

GROUP BY a.Date, a.LotNo, a.SubLotNo
ORDER BY a.Date, a.LotNo, a.SubLotNo;



(I just needed to add a pair or () to delimit the ON clause properly).



Concatenation is 'gluing' two strings together:


? "Hello" & "World"
HelloWorld


Note that is does not add, automatically, a space.

It is a word coming from Latin:
con : together
catena : chain




Vanderghast, Access MVP


Hi Michel,
LotNo And SubLotNo Long INteger Fields. Actualy I am Learning Access
through
Discussion Group and Help manu. I Have Created some tables and Queries
and
Reports that I am sing at my job that makes my life easier!!!!!

you said I am using lot of concatenation. In reality I don't know what
it
is!!!!!!!

I tried your Query by replacing HMADetail for 'a' and HMADetail_1 for
'b'
then I gets error Message. If possible would u please send me Exact
Query
statementso that I copy and pest. Normay I create Query from vizards. I
tried
your Query through Query vizards and check it on sql window I can't
get
the
same as yours
Andy

:

Your fields for lotNo and SubLotNo are integers, right? If not, try
to
make
them so.

Also, you use a lot of concatenation. Such strings handling, under
Windows,
is not very efficient, in general. You are right about having to use
constant length substrings if you want to compare lot and sub lot as
you
do.
Assuming two characters are enough:


DSum("[HMAPlaced]","HMADetail","Format([HMADetail].[LotNo], '00') &
[HMADetail].[SubLotNo]<="
& Format(HMADetail.LotNo, "00") & HMADetail.SubLotNo) AS
cumulativeHMAPlaced


Definitively, a join should be much faster, since a join CAN use
index,
your
computed DSUM criteria just cannot, in that case. A join is not much
harder
than the DSum, and in fact, it is simpler:


SELECT a.Date, a.LotNo, a.SubLotNo,
Sum(a.HMAPlaced) AS SumOfHMAPlaced,
Sum(b.HMAPlaced) AS cumulativeHMAPlaced

FROM HMADetail AS a INNER JOIN HMADetail As b
ON b.lotNo < a.LotNo OR (b.lotNo = a.lotNo AND b.SubLotNo<=
a.SubLotNo)

GROUP BY a.Date, a.LotNo, a.SubLotNo
ORDER BY a.Date, a.LotNo, a.SubLotNo;




There is still something that seems fishy about the date, since you
don't
really use it in the ordering criteria, while 'previously', in my
mind,
imply date.

Note: that assumes that LotNo and SubLotNo ARE numbers, not string!





Vanderghast, Access MVP


Hello Friends,
Finally I achived Running Sum in Query Table with your guidence. But
still
one Problem!!!
Here is my Sql Statement_
SELECT HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo,
Sum(HMADetail.HMAPlaced) AS SumOfHMAPlaced,
DSum("[HMAPlaced]","HMADetail","[HMADetail].[LotNo]&[HMADetail].[SubLotNo]<="
& HMADetail.LotNo & HMADetail.SubLotNo) AS cumulativeHMAPlaced
FROM HMADetail
GROUP BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo
ORDER BY HMADetail.Date, HMADetail.LotNo, HMADetail.SubLotNo;

This Query gives me following_
Date LotNo SubLotNo SumOfHMAPlaced cumulativeHMAPlaced
05/01/2007 1 1 500 500
18/01/2007 1 2 500 1000
18/01/2007 1 3 500 1500
18/01/2007 1 4 500 2000
18/03/2007 1 5 500 2500
18/03/2007 1 6 500 3000
23/03/2007 1 7 500 3500
23/03/2007 1 8 500 4000
27/03/2007 1 9 500 4500
27/03/2007 1 10 500 6500
10/04/2007 2 1 500 5000
10/04/2007 2 2 500 5500
10/04/2007 2 3 500 6000
The Cumulative Sum for SubLot 10 is not correct. I think it is may
be
because all nos in lot and SubLots are single digit and sublot 10 is
double
digit? if so then how can I change those no with 0 as prefix? ie.
1=01?
(Note: When I change 10 to 9 then all cumuletive su for all records
comes
correct)

Andy
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

With either an OR, either something else than an = in the JOIN, the query
does not open in DESIGN view, but you can open it in SQL view.

Vanderghast, Access MVP


Andy said:
Michel ,

with OR criteria ie <= in sql statement it doesn't open design view query?
Click to expand...
(...)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DSumExpression 2
Running sum 1
Lookup and count all cells that fall between 2 dates 3
Running sum 3
Advanced problem using auto-number and datediff to find readmissio 2
slow calculation of rank and percentile in Excel 2007 3
Obtain Month & Year from a Text formatted Date field using Left Fu 3
Taking the sum between two dates 2

Top