Next record data needed for calc

  • Thread starter Thread starter Malcolm
  • Start date Start date
M

Malcolm

Hi,

I have a transaction table where (Among other data) cash
amounts and non-resettable cash counter readings are
stored for vending machines, as follows:

Machine Date Cash Counter
School1 2/3 $47.00 1345.70
Factory7 5/3 $57.00 8745.30
School1 10/3 $87.50 1433.20
School1 23/3 $132.75 1565.95
Factory7 30/3 $87.30 8832.60 And so on.

For each [Machine] cash entry, I can check the cash
amounts against the cash counters by taking the present
counter and subtracting the most previous counter.

What I just can't seem to work out is how to have this
calculated difference in a query for a form. In other
words I need data from the most previous date [machine]
record in the current record for a calculation.

With some other data I was able to achieve this because
there was a sequential numeric field, and I created 2
queries with a relationship between [SequentialField] and
[SequentialField]+1, which aligns the next record nicely.
This sequential data is not available here though.

Any thoughts are appreciated.


Malcolm
 
Try a query like:
SELECT tblTrans.*, (SELECT Max(Counter) FROM tblTrans T WHERE T.Machine =
tblTrans.Machine AND T.Counter < tblTrans.Counter) as PrevCounter
FROM tblTrans;
 
Duane,

Your idea seems to work, which is great! I don't quite
understand the sql syntax "(SELECT Max(Counter) FROM
tblTrans T WHERE..." and you then specify the conditions.

Is the use of the 'T' as a tag to a second recordset? Or
can you advise where this would be further explained in
help? When I enter the actual field names it is to big to
read in the query designer, so I can only look at sql view.

Thanks for your help, this is great.


Malcolm
-----Original Message-----
Try a query like:
SELECT tblTrans.*, (SELECT Max(Counter) FROM tblTrans T WHERE T.Machine =
tblTrans.Machine AND T.Counter < tblTrans.Counter) as PrevCounter
FROM tblTrans;

--
Duane Hookom
MS Access MVP


Hi,

I have a transaction table where (Among other data) cash
amounts and non-resettable cash counter readings are
stored for vending machines, as follows:

Machine Date Cash Counter
School1 2/3 $47.00 1345.70
Factory7 5/3 $57.00 8745.30
School1 10/3 $87.50 1433.20
School1 23/3 $132.75 1565.95
Factory7 30/3 $87.30 8832.60 And so on.

For each [Machine] cash entry, I can check the cash
amounts against the cash counters by taking the present
counter and subtracting the most previous counter.

What I just can't seem to work out is how to have this
calculated difference in a query for a form. In other
words I need data from the most previous date [machine]
record in the current record for a calculation.

With some other data I was able to achieve this because
there was a sequential numeric field, and I created 2
queries with a relationship between [SequentialField] and
[SequentialField]+1, which aligns the next record nicely.
This sequential data is not available here though.

Any thoughts are appreciated.


Malcolm
Click to expand...


.
Click to expand...
 
This is a subquery. The T is used as a second name for the same recordset.

--
Duane Hookom
MS Access MVP


Malcolm said:
Duane,

Your idea seems to work, which is great! I don't quite
understand the sql syntax "(SELECT Max(Counter) FROM
tblTrans T WHERE..." and you then specify the conditions.

Is the use of the 'T' as a tag to a second recordset? Or
can you advise where this would be further explained in
help? When I enter the actual field names it is to big to
read in the query designer, so I can only look at sql view.

Thanks for your help, this is great.


Malcolm
-----Original Message-----
Try a query like:
SELECT tblTrans.*, (SELECT Max(Counter) FROM tblTrans T WHERE T.Machine =
tblTrans.Machine AND T.Counter < tblTrans.Counter) as PrevCounter
FROM tblTrans;

--
Duane Hookom
MS Access MVP


Hi,

I have a transaction table where (Among other data) cash
amounts and non-resettable cash counter readings are
stored for vending machines, as follows:

Machine Date Cash Counter
School1 2/3 $47.00 1345.70
Factory7 5/3 $57.00 8745.30
School1 10/3 $87.50 1433.20
School1 23/3 $132.75 1565.95
Factory7 30/3 $87.30 8832.60 And so on.

For each [Machine] cash entry, I can check the cash
amounts against the cash counters by taking the present
counter and subtracting the most previous counter.

What I just can't seem to work out is how to have this
calculated difference in a query for a form. In other
words I need data from the most previous date [machine]
record in the current record for a calculation.

With some other data I was able to achieve this because
there was a sequential numeric field, and I created 2
queries with a relationship between [SequentialField] and
[SequentialField]+1, which aligns the next record nicely.
This sequential data is not available here though.

Any thoughts are appreciated.


Malcolm
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top