Is there a possible expression

[arm1]

I was hoping that someone could at least tell me if this is possible.

I have a table that (among other fields) has an integer field (call it X) and a date field call it Y).

I was hoping that I could create a query that captured these two fields (no problem), but also included a third, build expression field.

This latter field is supposed to be a sum of the previous X-values up until the current record date Y-value.

Ex:

If I have the following info

X Y

1 jan-3-07
3 jan-5-07
23 jan-5-07
12 jan-19-07


Then the query would be built so that the results would look like one of the following

X Y Z OR Y Z

1 jan-3-07 1 jan-3-07 1
3 jan-5-07 4 jan-5-07 27
23 jan-5-07 27
12 jan-19-07 39 jan-19-07 39



I can't seem to be able to build the right expression to achieve this. I can easily code this through a form using VB, but I assume it is equally possible through just a query.

I would be grateful for any and all help people can provide,

-arm

 

[pbaldy]

Not too tricky. The term you want to search on is running sum. Typically done with either a DSum() or a sub query. In either case, the logic is "give me the sum of x with a date <= the current record's date".

 

[Anthony George]

Concatenation in a query

 

[pbaldy]

Tony, one of us has misunderstood the request. I think it's you, of course.

 

[Anthony George]

You could well be right

Hi Arm1
Hi Paul

Having had another look at the question I think you may well be right.

Oh well! it might help someone one day who has never built a query with a calculating field.

Have a good one

Regards

Tony