Help with Form and adding fields from 2 or more tables

[MitziUK]

I have a form which details vehicle particulars such as make, model, reg no
(primary key) vin no, engine no, date purchase, cc, year of first reg, radio
code, date sold, date sorn. Obviously the data entered into this form goes
into a table.

I have another service table which has the following fields:

Service ID (Auto No)
Reg No
Service Date
Mileage Last Service
Mileage between services
Weeks between Services
Service Type
Service by
Servicing Costs

Going back to my vehicle particulars form, I want to include the following
fields on it:

Last Service Date, (only show last date from service table)
Mileage Between services
Next service due (calculated field)

How do I add these please?

Also Mileage between Services – if the vehicle is a Renault mileage between
service is 9,000, Vauxhall 10,000. At the moment these figures are entered
into the service table, but is there a way that I can get the form to look at
the make of vehicle then according to its result display the appropriate
mileage between services on the form. Or am I going way to above myself.

 

[Andy Williams]

Yes it can be done, although there are a few bits that could trip you up and
depending on what you already know it might be a bit of a steep learning
curve.

In order to do this you'll need to understand the following:-

1. Relationships
2. Queries (including UNION queries)
3. Forms and Sub-Forms

If you aren't sure about any of these then it's probably best to start by
reading up on them.

I'll leave it at that for now but post back if you want any further
assistance.

Andy W

 

[MitziUK]

Andy

Was your reply in response to the first part of my question or the secoond?

I'm OK with forms, sub-forms, relationships, but will have a read up on
union queries.

No doubt I'll be posting again later.

 

[MitziUK]

OK Andy I've read up on Union queries though I'm still a bit "woolley" and
still reading/searching websites.

In my vehicle table I have make and model for vehicle, registration no is
primary key, although in my service table I only have the registration no
field (linked via relationship). DO I need to add to the servic table the
make/modelk of the vehicle?

 

[Andy Williams]

Your structure is basically correct and you only need to use the Reg No in
your Service table and not the Make and Model.

The following our suggestions to improve your database

Have a table of Makes

tblMakes
fldMakeID (pk), fldMake

and a table of Models

tblModel
fldModelID (pk), fldModel

Link them together with a third table Make/Model

tblMakeModel
fldMakeID, fldModelID

The advantage of this is you can create Combo boxes on your forms and link
them to queries such that when you select Ford as a Make you only get Models
that are applicable to Ford also if you update the tables with a new make or
model the changes will automatically update in the Combo boxes without you
having to do anything.

In your Vehicle Details you only need to record the MakeID and ModelID from
tblMake and tblModel rather than the full text because on your forms you can
use Combo boxes that display the Text but store the ID.

You can also then create a table of Service Intervals by Make

tblServiceInterval
fldMakeID, fldServiceIntMiles, fldServiceIntWeeks

You can then use this in a UNION query to display the Next Service Date and
Next Service Mileage based on either the last Service Date or the Purchase
Date depending on whether you have a previous Service or not (you may need
some help with this if you've not done one before so if you get stuck then
post back).

You should have the following relationships between your tables.

tblVehicleDetails fldRegNo to tblService fldRegNo
tblMake fldMakeID to tblVehicle fldMakeID
tblMake fldMakeID to tblMakeModel fldMakeID
tblModel fldModelID to tblVehicle fldModelID
tblModel fldModelID to tblMakeModel fldModelID
tblMake fldMakeID to tblServiceInterval fldMakeID

This should get you going.

 

[MitziUK]

Andy

I am at present improving my database with the suggestions you nade and can
see the benefit of this regarding combo boxes on forms etc. However, can you
please explain why i need another further table linking both the Make ID and
Model ID

Link them together with a third table Make/Model

tblMakeModel
fldMakeID, fldModelID

I assume that the fields Make ID and Model ID used on forms come from the
respective tables and not the one joining both makeID and modelID together.

Thanks

 

[Andy Williams]

Sorry for the delay in replying, been busy elsewhere.

If you link the Make and Model ID's in a table then on your form(s) after
you select a Make you can use the After Update command to re-query the
RowSource of the Model Combo box so that it only displays models relevant to
that make with a select statement like

"select fldModelID, fldModel from tblMakeModel where fldMakeUID = " &
me.MyForm.fldMakeID

It isn't essential but it makes things neater and stops you from ending with
customers who drive round in Volkswagen Mondeos or Ford Golfs!

Andy W

 

[Andy Williams]

Sorry just noticed an error in my last reply the select statemet should read

"select tblModel.fldModelID, tblModel.fldModel from tblMakeModel inner join
tblModel on tblMakeModel.fldModelID = tblModel.fldModelID where fldMakeUID =
" & me.MyForm.fldMakeID

Andy W