Do the data type have to match from one table to another

[maura]

My tblCar has a primary key CarNumber that is text
My tblRentalAgreement has a primary key AgreementNumber that is an autonumber

I will be using both these tables to create a form pulling information from
both tables... a coworker thinks I need a table -- tblCarAgreement which will
have an independant primary key (autonumber) with only two fields: (the
primary keys from the above tables) CarNumber and the AgreementNumber. He
thinks this is needed to form the relationships between the two tables. Is
this correct?

If yes, in this third table will the data types for these fields have to be
identical (to the table where they reside as primary keys)?

Thanks, maura

 

[Darryl Kerkeslager]

If yes, in this third table will the data types for these fields have to
be
identical (to the table where they reside as primary keys)?

Yes.

tblCarAgreement would not need to have its own primary key, if it is a
joining or bridge table between the car and the rental agreement for the
car. However, based on my limited knowledege of rental cars, I assume that
the rental agreement IS the join table, between the customer and the car.
Again, to make the relationship work, you could dispense with the autonumber
primary key field in
tblRentalAgreement, since this kind of structure (many to many) could just
use the pk of both tables, in combination, as the pk. You of course want a
renal agreement number to put on the form and track, but that still does not
need to be the pk of the table. But it could be for clarity of the design.

If tblCar has a text primary key (VIN), and tblRentalAgreement has an
autonumber primary key, then your tables look like this:

tblCar
[VIN] text (x) pk

tblCustomer
[customer_id] long-auto pk

tblRentalAgreement
[AgreementNumber] long-auto pk
[VIN] fk from tblCar
[customer_id] fk from tblCustomer
--------------OR------------------
tblCar
[VIN] text (x) pk

tblCustomer
[customer_id] long-auto pk

tblRentalAgreement
[VIN] fk tblCar \ combined PK
[customer_id] fk tblCustomer /
[AgreementNumber] long-auto

--------------OR------------------
tblCar
[car_id] long-auto pk
[VIN] text (x)

tblCustomer
[customer_id] long-auto pk

tblRentalAgreement
[car_id] fk tblCar \ combined PK
[customer_id] fk tblCustomer /
[AgreementNumber] long-auto

OR about 1,236 variations.