Update field in one table from field in another

[KevinPreston]

Hello everyone, this is my first post so apologies if i dont get it right
first time, i am a self taught Access user, i am stuck on something i am
trying to do, briefly i have 2 tables, one for vehicle details and one for
inspection details of vehicles in the first table. I have 2 fields that are
the same in both tables, lastsafe and distance, the vehicles table can only
have 1 vehicle to each record, the inspection table can have multiple records
for each vehicle, what i want to do is, when i input the last safe and
distance values in the inspection table i need it to transfer that data to
the vehicles table so that i am not having to type the same information twice
for each record.
Hope that makes sense and any help would be much appreciated.

To make it clearer, here are the fields

vehicles

lastsafe (date field)
Distance (number field

inspections

date (date field)
distance (number field)
Sorry i was wrong earlier, the field names are not the same but the data type
are.

[jahoobob via AccessMonster.com]

I'm assuming that you have a fleet of vehicles for which you have info such
as make, year model, VIN, License #, etc. and you do periodic inspections on
these vehicles that you enter a safety inspection date and an odometer
reading.
I would have my vehicle info in the one file with a unique key field (set it
to autonumber.) Do not include the lastsafe and distance fields. I would
then create the inspection table with a key field set to a Long Integer
number, the same as the key field in the vehicles table, as well as the date
and distance fields.
Create a form based on the vehicle table and place a subform based on the
inspection table in that form and link them by their two key fields.
Anytime a particular vehicle is brought up in the form, any data put in the
subform will always be linked to that one vehicle. You will have a list of
all safety inspection dates and distances and if you jsut need a list of the
last inspection dates for each vehicle you can do this via a query.
Hope this helps. If you need additional information, ask.

KevinPreston wrote:

>Hello everyone, this is my first post so apologies if i dont get it right
first time, i am a self taught Access user, i am stuck on something i am
trying to do, briefly i have 2 tables, one for vehicle details and one for
inspection details of vehicles in the first table. I have 2 fields that are
the same in both tables, lastsafe and distance, the vehicles table can only
have 1 vehicle to each record, the inspection table can have multiple records
for each vehicle, what i want to do is, when i input the last safe and
distance values in the inspection table i need it to transfer that data to
the vehicles table so that i am not having to type the same information twice
for each record.
Hope that makes sense and any help would be much appreciated.

To make it clearer, here are the fields

vehicles

lastsafe (date field)
Distance (number field

inspections

date (date field)
distance (number field)

Sorry i was wrong earlier, the field names are not the same but the data type
are.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200706/1

[KevinPreston via AccessMonster.com]

Thanks for your prompt assistance to my query, however it doesn't really help.

The vehicles table has 38 fields in it, the inspections table has 77 fields
in it.
The form associated with the vehicles table already includes a subform list
all inspections on a particular vehicle.
I need to be able to look at a particular vehicle and it show me when the
last inspection was completed and the odometer reading, without me having to
revert to the subform list, also eventually when i figure out how to do it,
it will also work out when the next inspection is due, which is either 6,8,10
or 12 weeks forward.
when i input the data for the inspection table for a particular vehicle it is
always the latest inspection so would be easier to updat the lastsafe field
in the vehicles table, this would then save me having to open the vehicles
form and enter the same info.
Hope this explains more fully why i have the two fields on the vehicles table,
incidently the vehicles table was developed first and the inspection was
added later. at the moment, to keep everything accurate i update the vehicle
lastsafe date and then open the inspections form and fill in the details for
the inspection.

jahoobob wrote:

>I'm assuming that you have a fleet of vehicles for which you have info such
as make, year model, VIN, License #, etc. and you do periodic inspections on
these vehicles that you enter a safety inspection date and an odometer
reading.
I would have my vehicle info in the one file with a unique key field (set it
to autonumber.) Do not include the lastsafe and distance fields. I would
then create the inspection table with a key field set to a Long Integer
number, the same as the key field in the vehicles table, as well as the date
and distance fields.
Create a form based on the vehicle table and place a subform based on the
inspection table in that form and link them by their two key fields.
Anytime a particular vehicle is brought up in the form, any data put in the
subform will always be linked to that one vehicle. You will have a list of
all safety inspection dates and distances and if you jsut need a list of the
last inspection dates for each vehicle you can do this via a query.
Hope this helps. If you need additional information, ask.

>>Hello everyone, this is my first post so apologies if i dont get it right
first time, i am a self taught Access user, i am stuck on something i am

[quoted text clipped - 22 lines]

>>Sorry i was wrong earlier, the field names are not the same but the data type
are.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200706/1

[jahoobob via AccessMonster.com]

Do you have a Primary key in the vehicles table that relates to a foreign key
in the inspections table as I described in my first response? I'm assuming
you do since your description below indicates that.
If you do then you can use this query to find the last inspection of a
vehicle and you won't need to put the additional info in the vehicles table.

SELECT vehicles.vehicle, Max(inspections.date) AS MaxOfdate
FROM inspections LEFT JOIN vehicles ON inspections.vehicleID = vehicles.
vehicleID
GROUP BY vehicles.vehicle;

You can add another subform based on this query that will display the last
inpsection date. Just replace vehicle with vehicleID and link it the same
way you linked the inspections subform.
KevinPreston wrote:

>Thanks for your prompt assistance to my query, however it doesn't really help.

The vehicles table has 38 fields in it, the inspections table has 77 fields
in it.
The form associated with the vehicles table already includes a subform list
all inspections on a particular vehicle.
I need to be able to look at a particular vehicle and it show me when the
last inspection was completed and the odometer reading, without me having to
revert to the subform list, also eventually when i figure out how to do it,
it will also work out when the next inspection is due, which is either 6,8,10
or 12 weeks forward.
when i input the data for the inspection table for a particular vehicle it is
always the latest inspection so would be easier to updat the lastsafe field
in the vehicles table, this would then save me having to open the vehicles
form and enter the same info.
Hope this explains more fully why i have the two fields on the vehicles table,
incidently the vehicles table was developed first and the inspection was
added later. at the moment, to keep everything accurate i update the vehicle
lastsafe date and then open the inspections form and fill in the details for
the inspection.

>>I'm assuming that you have a fleet of vehicles for which you have info such
as make, year model, VIN, License #, etc. and you do periodic inspections on

[quoted text clipped - 18 lines]

>>>Sorry i was wrong earlier, the field names are not the same but the data type
are.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200706/1

[KevinPreston via AccessMonster.com]

Thank you very much for the assistance, it has taken me all afternoon but i
have managed to get what you said working. It isnt quite what i want but it
works. More assistance please
the same inspections table has the distance in it aswell, how can extract the
distance related to the date? i need to show the last safety date and
distance together.
jahoobob wrote:

>Do you have a Primary key in the vehicles table that relates to a foreign key
in the inspections table as I described in my first response? I'm assuming
you do since your description below indicates that.
If you do then you can use this query to find the last inspection of a
vehicle and you won't need to put the additional info in the vehicles table.

SELECT vehicles.vehicle, Max(inspections.date) AS MaxOfdate
FROM inspections LEFT JOIN vehicles ON inspections.vehicleID = vehicles.
vehicleID
GROUP BY vehicles.vehicle;

You can add another subform based on this query that will display the last
inpsection date. Just replace vehicle with vehicleID and link it the same
way you linked the inspections subform.

>>Thanks for your prompt assistance to my query, however it doesn't really help.

[quoted text clipped - 22 lines]

>>>>Sorry i was wrong earlier, the field names are not the same but the data type
are.

--
Message posted via http://www.accessmonster.com

[Ed Marzan]

Create 2 text boxes on your main form and do a DLookup or DMax on the
subform table to get the values you want.

BTW 77 fields sounds like a design problem, but I could be wrong.

Regards

On Jun 6, 12:31 pm, "KevinPreston via AccessMonster.com" <u34811@uwe>
wrote:

Thank you very much for the assistance, it has taken me all afternoon but i
have managed to get what you said working. It isnt quite what i want but it
works. More assistance please
the same inspections table has the distance in it aswell, how can extract the
distance related to the date? i need to show the last safety date and
distance together.

jahoobob wrote:

Do you have a Primary key in the vehicles table that relates to a foreign key
in the inspections table as I described in my first response? I'm assuming
you do since your description below indicates that.
If you do then you can use this query to find the last inspection of a
vehicle and you won't need to put the additional info in the vehicles table.

SELECT vehicles.vehicle, Max(inspections.date) AS MaxOfdate
FROM inspections LEFT JOIN vehicles ON inspections.vehicleID = vehicles.
vehicleID
GROUP BY vehicles.vehicle;

You can add another subform based on this query that will display the last
inpsection date. Just replace vehicle with vehicleID and link it the same
way you linked the inspections subform.

>Thanks for your prompt assistance to my query, however it doesn't really help.

[quoted text clipped - 22 lines]

>>>Sorry i was wrong earlier, the field names are not the same but the data type
are.

--
Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -

[KevinPreston via AccessMonster.com]

Thanks for your suggestion, i will give it a try, your way sounds much easier,
i just need to fgure out how to do that, i am fairly inexprienced with the
programming part.

The inspection form needs 77 fields because it records all the items that are
inspected during the inspection. EG recording tyre pressures on a 4 axle
wagon requires 12 fields just for that.

I also require assistance on another problem, on the same vehicles form i
store the last service date & distance, the vehicles have inspections every 6
weeks but only have services at different intervals, ie once a year, every 12
weeks, every 20,000kms etc. How can i record this without having to go to
every vehicle and change it manually? The service is mentioned on the
inspection sheet, would it be possible to add a check box to note the service
and create a query to check if the vehicle has had aservice and then DLookup
the date & distance?

Regards

Ed Marzan wrote:

>Create 2 text boxes on your main form and do a DLookup or DMax on the
subform table to get the values you want.

BTW 77 fields sounds like a design problem, but I could be wrong.

Regards

On Jun 6, 12:31 pm, "KevinPreston via AccessMonster.com" <u34811@uwe>
wrote:

>Thank you very much for the assistance, it has taken me all afternoon but i
have managed to get what you said working. It isnt quite what i want but it

[quoted text clipped - 28 lines]

>>
- Show quoted text -

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200706/1