Field from Previous Record

[Budgie]

I need to create a little access project to calculate the fuel consumption
for a fleet of abou 100 buses. Typical information to be captured are as
below:

ID Bus No Date Odometer Gallons
----------------------------------------------
1 50/001 6/21/94 77917.8 10.2
2 50/001 6/25/94 78254.7 9.6
3 50/001 6/30/94 78582.3 10
4 50/002 7/5/94 78918.4 10.4
5 50/002 7/8/94 79500.8 8.3
6 50/003 7/10/94 79223.4 9.4

I now need to calculate the consumption (Miles per gallon) and print out a
report.
I have lookup at the example at this link where the Dlookup() function is
used.

http://support.microsoft.com/default.aspx?id=210504.

This works fine as long as you don't delete a record from your table,
because then the [ID] /(Autonumber) is no longer in sequence and the Previous
Record is for a different Bus, resulting in the wrong results.

Below the record with [ID]=5 is deleted.

ID Bus No Date Odometer Gallons
----------------------------------------------
1 50/001 6/21/94 77917.8 10.2
2 50/001 6/25/94 78254.7 9.6
3 50/001 6/30/94 78582.3 10
4 50/002 7/5/94 78918.4 10.4
6 50/003 7/10/94 79223.4 9.4

Using the Dlookup() will now give the wrong results.

I am not at all an Access expert and have taken this on to assist a
colleague who is using an excel spreadsheet to handle this, but now I am
stuck.

Can anyone please assist me in this?

Many thanx.

 

[Wayne-I-M]

I can not see any relational data in your example. It may be that you would
be much better using excel for this project. It will be "very" simple to set
up in excel

 

[Allen Browne]

See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[Budgie]

Thanx Allen. However I still have a problem as I cannot use the
[ID]/Autonumber as primary key as the [ID] loses its sequence soon as a
record gets deleted.
--
BvR

See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I need to create a little access project to calculate the fuel consumption
for a fleet of abou 100 buses. Typical information to be captured are as
below:

ID Bus No Date Odometer Gallons
----------------------------------------------
1 50/001 6/21/94 77917.8 10.2
2 50/001 6/25/94 78254.7 9.6
3 50/001 6/30/94 78582.3 10
4 50/002 7/5/94 78918.4 10.4
5 50/002 7/8/94 79500.8 8.3
6 50/003 7/10/94 79223.4 9.4

I now need to calculate the consumption (Miles per gallon) and print out a
report.
I have lookup at the example at this link where the Dlookup() function is
used.

http://support.microsoft.com/default.aspx?id=210504.

This works fine as long as you don't delete a record from your table,
because then the [ID] /(Autonumber) is no longer in sequence and the
Previous
Record is for a different Bus, resulting in the wrong results.

Below the record with [ID]=5 is deleted.

ID Bus No Date Odometer Gallons
----------------------------------------------
1 50/001 6/21/94 77917.8 10.2
2 50/001 6/25/94 78254.7 9.6
3 50/001 6/30/94 78582.3 10
4 50/002 7/5/94 78918.4 10.4
6 50/003 7/10/94 79223.4 9.4

Using the Dlookup() will now give the wrong results.

I am not at all an Access expert and have taken this on to assist a
colleague who is using an excel spreadsheet to handle this, but now I am
stuck.

Can anyone please assist me in this?

Many thanx.

 

[Allen Browne]

The subquery does not assume a complete sequence.

It only assumes that you have a field that determines the order of the
records. If you have no such field, then the concept of a 'previous' record
is meaningless.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanx Allen. However I still have a problem as I cannot use the
[ID]/Autonumber as primary key as the [ID] loses its sequence soon as a
record gets deleted.
--
BvR

See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

I need to create a little access project to calculate the fuel
consumption
for a fleet of abou 100 buses. Typical information to be captured are
as
below:

ID Bus No Date Odometer Gallons
----------------------------------------------
1 50/001 6/21/94 77917.8 10.2
2 50/001 6/25/94 78254.7 9.6
3 50/001 6/30/94 78582.3 10
4 50/002 7/5/94 78918.4 10.4
5 50/002 7/8/94 79500.8 8.3
6 50/003 7/10/94 79223.4 9.4

I now need to calculate the consumption (Miles per gallon) and print
out a
report.
I have lookup at the example at this link where the Dlookup() function
is
used.

http://support.microsoft.com/default.aspx?id=210504.

This works fine as long as you don't delete a record from your table,
because then the [ID] /(Autonumber) is no longer in sequence and the
Previous
Record is for a different Bus, resulting in the wrong results.

Below the record with [ID]=5 is deleted.

ID Bus No Date Odometer Gallons
----------------------------------------------
1 50/001 6/21/94 77917.8 10.2
2 50/001 6/25/94 78254.7 9.6
3 50/001 6/30/94 78582.3 10
4 50/002 7/5/94 78918.4 10.4
6 50/003 7/10/94 79223.4 9.4

Using the Dlookup() will now give the wrong results.

I am not at all an Access expert and have taken this on to assist a
colleague who is using an excel spreadsheet to handle this, but now
I am stuck.