how can i make an accumulation query

[Guest]

For example I have this table :

No. Type Date Amount
2 a 03/03 120
5 b 10/03 130
3 c 12/03 133

And I want the result of my query to be like:

No. Type Date SumOfAmount
2 a 03/03 120
3 c 12/03 253 ----> ( 120 + 133 )
5 b 10/03 383 ----> ( 253 + 130 )

That’s mean I want it ordered by No. and balance should be the amount of the
current record + sum of the previews record(s)

Hope its clear

So how can I make it?!

Any thoughts or suggestions would be much appreciated.

Thanks in advance

 

[Michel Walsh]

Hi,


SELECT a.[No], LAST(a.type), LAST(a.date), LAST(a.Amount), SUM(b.Amount)
FROM myTable As a INNER JOIN myTable As b ON a.[No] >= b.[No]
GROUP BY a.No


Be sure to have an index on field [No], not necessary a reserved word,
yes/no, but clearly border line.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you Michel
It work cause the [No] field is the P.K
But I have two questions please
1. Why you didnt use Last function for a.[No] field?
2. What if I didnt have index on [No] field or its duplicated?

Its just to know questions

Thanks again

Hi,


SELECT a.[No], LAST(a.type), LAST(a.date), LAST(a.Amount), SUM(b.Amount)
FROM myTable As a INNER JOIN myTable As b ON a.[No] >= b.[No]
GROUP BY a.No


Be sure to have an index on field [No], not necessary a reserved word,
yes/no, but clearly border line.


Hoping it may help,
Vanderghast, Access MVP

For example I have this table :

No. Type Date Amount
2 a 03/03 120
5 b 10/03 130
3 c 12/03 133

And I want the result of my query to be like:

No. Type Date SumOfAmount
2 a 03/03 120
3 c 12/03 253 ----> ( 120 + 133 )
5 b 10/03 383 ----> ( 253 + 130 )

That's mean I want it ordered by No. and balance should be the amount of
the
current record + sum of the previews record(s)

Hope its clear

So how can I make it?!

Any thoughts or suggestions would be much appreciated.

Thanks in advance

 

[Michel Walsh]

Hi,


a) the rule is that if an aggregate is used, then all the fields in the
SELECT clause must either be aggregated (here, with MAX and SUM) either be
in the GROUP BY list. Since No is in the GROUP BY list, it does not need to
be aggregated.

b) if No has duplicated values, the ON clause must be re-written so that
unique ordering should be described, something like:

ON ( ( a.No > b.No ) OR ( a.No = b.No AND a.tieBraker >=
b.tieBraker ) )

and the a.tieBraker field would probably be to be added in the GROUP BY
list.


Hoping it may help,
Vanderghast, Access MVP

Thank you Michel
It work cause the [No] field is the P.K
But I have two questions please
1. Why you didnt use Last function for a.[No] field?
2. What if I didnt have index on [No] field or its duplicated?

Its just to know questions

Thanks again

Hi,


SELECT a.[No], LAST(a.type), LAST(a.date), LAST(a.Amount), SUM(b.Amount)
FROM myTable As a INNER JOIN myTable As b ON a.[No] >= b.[No]
GROUP BY a.No


Be sure to have an index on field [No], not necessary a reserved word,
yes/no, but clearly border line.


Hoping it may help,
Vanderghast, Access MVP

For example I have this table :

No. Type Date Amount
2 a 03/03 120
5 b 10/03 130
3 c 12/03 133

And I want the result of my query to be like:

No. Type Date SumOfAmount
2 a 03/03 120
3 c 12/03 253 ----> ( 120 + 133 )
5 b 10/03 383 ----> ( 253 + 130 )

That's mean I want it ordered by No. and balance should be the amount
of
the
current record + sum of the previews record(s)

Hope its clear

So how can I make it?!

Any thoughts or suggestions would be much appreciated.

Thanks in advance