autonumber question

[kathryn]

In creating a table for maintenance of a fleet of cars, I would like each record to auto number as the primary key, except I'd like it to be PER CAR instead of overall. ex: Car 2 could have records 1-100, and Car 3 could have records 1-250, etc. This would make the Car # and Record number a shared primary key (or whatever it's called), right? How do I get it to do this on data entry? The fields are Record Number, Car Number, Maintenance date, etc. Help, if you can. I really appreciate it!

 

[Rick Allison]

Kathryn,

The issue in my mind is normalization.

Seems you want to keep track of cars and the maintenance record for them. I'd create two table. Table one - "tblCar" and give it an auto assigned number. Table two - tblMaintenance would also have a auto assigned number with a foreign key to car. That way a car can have unlimited number of maintenance records.

So, tblCar
CarID AutoNumber
CarNumber Number
etc for other things to keep track of

tblMaintenance
MaintenanceID AutoNumber
CarID Number (foreign Key to tblCar)
MaintenanceDate Date/Time

Create a Form based on tblCar and a form based on tblMaintenance. I'd call them frmCar and fsubMaintenance. Make fsubMaintenance a subform of frmCar and link the two together based on CarID. That way, when you pull up Car 1 you'll get all of its records, etc...

I hope this helps.

If you want to learn more I recommend you surf the web on data normalization. That's what will really help.

Rick

 

[kathryn]

Thanks much! I was half way there, but now I see the light... however... I should be able to enter a new record into that sub-form and have it auto-number the record, right? but it's not autonumbering the maintenance record...??? Have any ideas why?

 

[Immanuel Sibero]

Hi Kathryn,
Yes, the subform should automatically generate autonumber for the
maintenance table records. Are you sure you set up the Main and Sub form
correctly? with the correct link (CarID)?

Make sure MaintenanceID is set to autonumber and the New Values option
should be set to Increment. Also, set a one-to- many relationship between
tblCar and tblMaintenance using CarID.

HTH,
Immanuel Sibero

Thanks much! I was half way there, but now I see the light... however... I

should be able to enter a new record into that sub-form and have it
auto-number the record, right? but it's not autonumbering the maintenance
record...??? Have any ideas why?