Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

Access 2007 – How do I choose the right structure for my database?

Locked

I am trying to use Access 2007 to store data with many relationships and am trying to determine the best way to store this data. I am also creating quite a complex UI (in Access) to use/store this info and send info to and from Excel for Automotive Industry.
I am trying to build a model of a complete car in Access and wish to select from a list of different size gears to put into a gearbox. However, I have a choice of 1st gears etc for different cars. So once I’ve chosen the car type, I would then like to choose a 1st gear based on a list of car-specific 1st gears. I am able to do this with a combobox on a form. However, I’m not sure of the best way to store this info as this type of problem is repeated in greater complexity across the database. For example, not every car has 5 gears and some have other unique parts in the gearbox that also have values associated with them. I currently have a special ‘options’ table that I query to determine the car-specfic list. However, I’m really struggling the determine the best way of storing and using my data.

Table1: GearOptions
GO_ID (PK)
car name (FK)
Part name (eg 1st gear)
Part Value (eg ’12/36 teeth)

Table2: Gearbox
Gb_ID (PK)
car name (FK)
1st gear value (choosen from combobox on form from list generated from Table 1 above)
2nd gear value (etc..)

I would be very grateful if anyone can help with the structure of my database.

Topic

Clarifications

In reply to Access 2007 – How do I choose the right structure for my database?

Clarifications

[Author]

Replies