Linking Data between tables

[Guest]

Can you helkp me link some data please.

I have 2 tables in my database which both have 'current mileage' as a field.
The Primary Key in both tables is the Registration Number.

One table lists a weekly milage return, the other service details.

I want the current mileage field from the Service Details table to look at
the last record (based on data entered incurrent mileage field) from the
mileage table and display it in the Service details table.

How please.

 

[John Vinson]

Can you helkp me link some data please.

I have 2 tables in my database which both have 'current mileage' as a field.
The Primary Key in both tables is the Registration Number.

One table lists a weekly milage return, the other service details.

I want the current mileage field from the Service Details table to look at
the last record (based on data entered incurrent mileage field) from the
mileage table and display it in the Service details table.

How please.

The current milage field SHOULD NOT EXIST. Any field which can be
calculated or looked up using a Query should be displayed in that way,
not stored in your Table.

Are you using table datasheets for data entry or display? Well...
don't. That's not what they're for! Instead, use a Form. You can have
a Form based on the vehicle table, with a subform based on the service
table. The service table should certainly contain the Registration
Number, but it should be the *FOREIGN* key, not the primary key; the
primary key is by definition unique within the table, so having it be
the primary key would be saying that a given vehicle can be serviced
once and once only, never again in its lifetime.

I'd say you need (at least) three tables:

Vehicles
RegistrationNumber <Primary Key>
<information about the vehicle, such as its owner, make, model>

Milage
RegistrationNumber
MilageDate Date/Time
Milage

(The first two fields could be a joint two-field Primary Key)

Service
RegistrationNumber
ServiceDate
ServiceType
<other info about the service>

The first THREE fields should be the primary key (ctrl-click the three
fields in table design and click the Key icon), or you might want an
autonumber ServiceID field as the primary key if you'll be storing
other details about each service episode.

To display the current milage, use a query on the Milage table; it
will need to use a Subquery to find the milage for the most recent
date. For instance you could create a Query based on milage and put

=(SELECT Max([MilageDate]) FROM Milage AS X WHERE X.RegistrationNumber
= Milage.RegistrationNumber)

on the Criteria line under MilageDate.

John W. Vinson[MVP]

 

[Guest]

John

Thanks for your response. I have taken out the current mileage field from
these tables. I do actually have 3 tables (Vehicles, Mileage, Service).

I can follow your instruction but do not understand what you mean by
=(SELECT Max([MileageDate]) FROM Mileage AS X WHERE X.RegistrationNumber=
= Mileage.RegistrationNumber)

In particular X WHERE X (what are you assuming 'X' standas for?

Thanks

 

[John Vinson]

John

Thanks for your response. I have taken out the current mileage field from
these tables. I do actually have 3 tables (Vehicles, Mileage, Service).

I can follow your instruction but do not understand what you mean by
=(SELECT Max([MileageDate]) FROM Mileage AS X WHERE X.RegistrationNumber=
= Mileage.RegistrationNumber)

In particular X WHERE X (what are you assuming 'X' standas for?

X is an alias for the Mileage table, since I'm using the mileage table
in two ways - in the main query and in the subquery. The syntax

FROM Mileage AS X

defines X as another name for (this instance of) the Mileage table,
and

WHERE X.RegistrationNumber = Mileage.RegistrationNumber

says that you want to use the main query's ("Milage") registration
number to filter the subquery ("X").

John W. Vinson[MVP]