Conditional Link between Tables required

[smurph8875]

Hello - I am new to the forum and would like to start with a problem.

I have a Database with several tables with regard to vehicles and test dates. At the moment the tables are updated with the new Test Expiry Date each year, but this doesn't record historical data.

I would like to have another table that would record the test dates against the registration of the vehicle and the main table would then pull across the latest date from the table. For example, the structure is below:

MAIN:

Registration Number (Key)
Make
Model
VIN Number
Date of Registration
Date of Last MOT
Date of Crane Inspection
Vehicle Type (Lookup)

MOT DATE:

Registration Number (Key)
MOT Date (Date)

At the moment I have the relationship between the registration number fields on each table. I am pretty sure that the MOT Date can be populated with a SQL Command, but not sure how to get this to populate?

I am using Access 2010 to do this and would appreciate any help you can give me in this,

Many thanks in advance

 

[vbaInet]

This is what your tables should look like:

MAIN:

Registration Number (Key)
Make
Model
VIN Number
Date of Registration
Vehicle Type (Lookup) ??


MOTs:

Registration Number (Key)
MOT Date (Date)


CRANE INSPECTIONS:

Registration Number (Key)
Date of Crane Inspection

You can get the date of latest MOT date using a query. Look into the Max function.