Get last record of a field for multiple records

[catphishum]

Hi All - Thanks in advance for your help....Am new to access
programming, but understand the underlying principles, for the most
part ;o)
I'm building a database for a friend that allows him to input
maintenance performed to his fleet of rental cars. They have a rental
car software that uses access on the back end to store information
(Fleet ID, Make, Model, Miles, etc). What I have created so far is a
table & associated form that he can use for data entry of maintenance
to the fleet. The form allows him to select a Fleet ID (from linked
table as described above), miles at time of service, date, and a
variety of yes/no's for the services he routinely performs.

He would like for a report to be generated that shows the following
for the entire fleet:
Fleet ID, Miles, Last Service Miles.....

I cannot seem to build a query that shows this information. I can
build one that shows the last service mileage for a particular Fleet
ID (using a parameter), but not for say cars 1-4, their current
mileage, and the mileage of last service.

any thoughts on what I should do? I don't exactly know what to relate
in the two tables to get the information to propagate....thanks
again,

Brian B.

 

[Guest]

You should be able to create a totals query of the un-named maintenance table
that groups by FleetID and returns the Max of [Miles at time of Service].
Then join this query to your table that stores the FleetID and Miles.

BTW: I would have normalized your maintenance table to remove the yes/no
fields and created a related table that contains a record for each different
service performed during the maintenance event.

 

[catphishum]

Thanks Duane. I will try this out. I'm not sure what you mean by
normalized, but you are probably correct. I'm new at this and still
learning the correct way to go about things...

You should be able to create a totals query of the un-named maintenance table
that groups by FleetID and returns the Max of [Miles at time of Service].
Then join this query to your table that stores the FleetID and Miles.

BTW: I would have normalized your maintenance table to remove the yes/no
fields and created a related table that contains a record for each different
service performed during the maintenance event.
--
Duane Hookom
Microsoft Access MVP

Hi All - Thanks in advance for your help....Am new to access
programming, but understand the underlying principles, for the most
part ;o)
I'm building a database for a friend that allows him to input
maintenance performed to his fleet of rental cars. They have a rental
car software that uses access on the back end to store information
(Fleet ID, Make, Model, Miles, etc). What I have created so far is a
table & associated form that he can use for data entry of maintenance
to the fleet. The form allows him to select a Fleet ID (from linked
table as described above), miles at time of service, date, and a
variety of yes/no's for the services he routinely performs.

He would like for a report to be generated that shows the following
for the entire fleet:
Fleet ID, Miles, Last Service Miles.....

I cannot seem to build a query that shows this information. I can
build one that shows the last service mileage for a particular Fleet
ID (using a parameter), but not for say cars 1-4, their current
mileage, and the mileage of last service.

any thoughts on what I should do? I don't exactly know what to relate
in the two tables to get the information to propagate....thanks
again,

Brian B.

 

[Guest]

Normalization information can be found on the web if you do a search. Bottom
line, if you want to add a new service, you shouldn't have to create a new
field and modify forms, queries, reports,...

--
Duane Hookom
Microsoft Access MVP

Thanks Duane. I will try this out. I'm not sure what you mean by
normalized, but you are probably correct. I'm new at this and still
learning the correct way to go about things...

You should be able to create a totals query of the un-named maintenance table
that groups by FleetID and returns the Max of [Miles at time of Service].
Then join this query to your table that stores the FleetID and Miles.

BTW: I would have normalized your maintenance table to remove the yes/no
fields and created a related table that contains a record for each different
service performed during the maintenance event.
--
Duane Hookom
Microsoft Access MVP

Hi All - Thanks in advance for your help....Am new to access
programming, but understand the underlying principles, for the most
part ;o)
I'm building a database for a friend that allows him to input
maintenance performed to his fleet of rental cars. They have a rental
car software that uses access on the back end to store information
(Fleet ID, Make, Model, Miles, etc). What I have created so far is a
table & associated form that he can use for data entry of maintenance
to the fleet. The form allows him to select a Fleet ID (from linked
table as described above), miles at time of service, date, and a
variety of yes/no's for the services he routinely performs.

He would like for a report to be generated that shows the following
for the entire fleet:
Fleet ID, Miles, Last Service Miles.....

I cannot seem to build a query that shows this information. I can
build one that shows the last service mileage for a particular Fleet
ID (using a parameter), but not for say cars 1-4, their current
mileage, and the mileage of last service.

any thoughts on what I should do? I don't exactly know what to relate
in the two tables to get the information to propagate....thanks
again,

Brian B.

 

[catphishum]

Thanks again Duane. I will check into this.

I did as you suggested with the totals query & then joined the query
to the other table. It works great! I appreciate your help in this,
it has saved me much headache ;o)

Brian B.

Normalization information can be found on the web if you do a search. Bottom
line, if you want to add a new service, you shouldn't have to create a new
field and modify forms, queries, reports,...

--
Duane Hookom
Microsoft Access MVP

Thanks Duane. I will try this out. I'm not sure what you mean by
normalized, but you are probably correct. I'm new at this and still
learning the correct way to go about things...

You should be able to create a totals query of the un-named maintenance table
that groups by FleetID and returns the Max of [Miles at time of Service].
Then join this query to your table that stores the FleetID and Miles.
BTW: I would have normalized your maintenance table to remove the yes/no
fields and created a related table that contains a record for each different
service performed during the maintenance event.
--
Duane Hookom
Microsoft Access MVP
:
Hi All - Thanks in advance for your help....Am new to access
programming, but understand the underlying principles, for the most
part ;o)
I'm building a database for a friend that allows him to input
maintenance performed to his fleet of rental cars. They have a rental
car software that uses access on the back end to store information
(Fleet ID, Make, Model, Miles, etc). What I have created so far is a
table & associated form that he can use for data entry of maintenance
to the fleet. The form allows him to select a Fleet ID (from linked
table as described above), miles at time of service, date, and a
variety of yes/no's for the services he routinely performs.
He would like for a report to be generated that shows the following
for the entire fleet:
Fleet ID, Miles, Last Service Miles.....
I cannot seem to build a query that shows this information. I can
build one that shows the last service mileage for a particular Fleet
ID (using a parameter), but not for say cars 1-4, their current
mileage, and the mileage of last service.
any thoughts on what I should do? I don't exactly know what to relate
in the two tables to get the information to propagate....thanks
again,
Brian B.