Relational databases

[Guest]

This is probably a basic questions but I am learning ACCESS.

I have created a master database with primary ID. The primary ID is not
autonumbering, in case that matters. The master database contains vehicle
info, ie VIN, Color, etc.

I want to create another database to enter info pertaining to service done
on the vehicles, of course using the primary key. How do I create the new
service info database so that it knows to connect to the master database?

Thanks,
Les

 

[Allen Browne]

Hi Les. In Access we refer to tables rather than databases.

You have a Vehicle table, with a VehicleID primary key of some kind.

Your Service table will have fields like this:
ServiceID primary key
VehicleID relates to Vehicle.VehicleID
ServiceDate date/time
etc.

Service.VehicleID should be the same data type and size as
Vehicle.VehicleID, e.g. both Number (long integer), or both Text (24
characters.)

Then choose Relationships on the Tools menu to open the Relationships
window. Drag Vehicle.VehicleID onto Service.VehicleID, and check the box for
Relational Integrity.

 

[Guest]

So I will have two primary keys present in the SERVICE table? I also guess
that I create the Service table from scratch without having any connection to
the Vehicle table until I do what you said about going to TOOL on the menu
and choosing Relations?

Thanks for help,
Les

 

[Smartin]

So I will have two primary keys present in the SERVICE table? I also guess
that I create the Service table from scratch without having any connection to
the Vehicle table until I do what you said about going to TOOL on the menu
and choosing Relations?

Thanks for help,
Les

The Service table has one *primary key*, which uniquely identifies a
particular service event. It also has what we call a *foreign key* (the
VehicleID) which matches a VehicleID in the Vehicles table. The latter
tells you which vehicle the service relates to. In this way the Service
table can record any number of services per vehicle.

For the second part of your question, yes. Create the Service table
first, save it, close it, then follow the instructions for creating the
relationship.

Hope this helps!