Question about 4-Field Pk Subform..... Is there a better way?

[Guest]

My Main form is called Quotes and has a Quote ID as the pk.

I the following 4 field table that lists information regarding the quote:
Quote ID
Vehicle ID
Seating Position ID
Component ID

I can have:
Multiple Vehicle IDs per Quote ID
Multiple Seating Position IDs per Vehicle ID
Multiple Component IDs per Seating Position ID

Therefore, I have All 4 fields make up the pk for this table.

On my Quote form, I would like to have the following Tabular Subforms:
1. Vehicle ID Subform - showing just the Vehicle IDs linked to the current
Quote ID
2. Seating Position Subform - showing just the Seating Positions linked to
the selected Quote/Vehicle combo
3. Components Subform - showing just the Components linked to the selected
Quote/Vehicle combo/Seating Positions.

Of course I want to also be able to add data into each subform and be sure
that it's tied to the correct "master field" (the one above it in the
hiearchy).

Any ideas? Is there a better way to setup the data than a 4 field primary
Key?
With the table structure the way it is, I probably would expect to have more
than 30,000 records at max.

Thanks,
Mike

 

[Jack MacDonald]

You haven't mentioned any of the tables in your database structure. I
can't tell from your message whether you have normalized the
structure, or put it into a single flat table. Please provide that
information for additional comments.

BTW -- I always avoid spaces in field names. It makes things easier in
the long run to avoid spaces.

My Main form is called Quotes and has a Quote ID as the pk.

I the following 4 field table that lists information regarding the quote:
Quote ID
Vehicle ID
Seating Position ID
Component ID

I can have:
Multiple Vehicle IDs per Quote ID
Multiple Seating Position IDs per Vehicle ID
Multiple Component IDs per Seating Position ID

Therefore, I have All 4 fields make up the pk for this table.

On my Quote form, I would like to have the following Tabular Subforms:
1. Vehicle ID Subform - showing just the Vehicle IDs linked to the current
Quote ID
2. Seating Position Subform - showing just the Seating Positions linked to
the selected Quote/Vehicle combo
3. Components Subform - showing just the Components linked to the selected
Quote/Vehicle combo/Seating Positions.

Of course I want to also be able to add data into each subform and be sure
that it's tied to the correct "master field" (the one above it in the
hiearchy).

Any ideas? Is there a better way to setup the data than a 4 field primary
Key?
With the table structure the way it is, I probably would expect to have more
than 30,000 records at max.

Thanks,
Mike

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Guest]

The 2 tables that I'm referring to are:
Master Quote TableQuote Content Table - Child

This is the Quote Content Table sturcture.
The PK is an index of all 4 fields.
The Master Quote Table itself has a pk field of Quote ID and is linked to
the Content Table above.

Thanks for the tip on field names and spaces... But at this point, can I
really change them without mucking up all my existing links, forms & queries?

If you need any more info to help, please let me know.

 

[Douglas J. Steele]

There are 3rd party products that will let you rename your fields:

Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html
Ucora's: http://www3.bc.sympatico.ca/starthere/findandreplace/

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the tip on field names and spaces... But at this point, can I
really change them without mucking up all my existing links, forms &

queries?

 

[Jack MacDonald]

I presume that you have additional tables that you haven't mentioned
e.g.
- a vehicle table with a PK of VehicleID
- a seating position table with PK of SeatingPositionID
- a component table with PK of ComponentID

Each of these items is presumably related to your child table via the
corresponding field in the child table.

That's one way to do it, although not the method that I would choose.
The problem that I foresee is when you are entering a new component,
you must specify the quote, vehicle, and seating position that it
pertains to. I prefer to have the tables in several cascading
one-to-many relations.

tblQuote
-QuoteID autonumber, PK
- other info that pertains to the quote - date, customer, etc

tblVehicle
- VehicleID autonumber, PK
- QuoteIDfk foreign key to the quote table
- other info that pertains to the vehicle - make, model, owner, etc

This table allows many (zero or more) vehicles to be associated with a
single quote in a classic one-to-many relationship.


tblSeatingPosition
- SeatingID autonumber, PK
- VehicleIDfk foreign key to the vehicle table
- other info that pertains to the seating postion e.g. it's position
in the vehicle (driver, front passenger, etc)

This table allows each vehicle to have zero or more seating postions.



tblComponent
- ComponentID autonumber, PK
- SeatingPostionIDfk foreign key to the seating postion table
- other info that pertains to the component - description, cost, etc

For doing data entry with this structure, you will pick the quote, and
your form will show you all the vehicles that pertain to that quote.
Then you pick the vehicle, and the form will show you all the seating
positions that pertain to the vehicle. Finally, you will pick the
Seating Position, and the form will show you all the components that
pertain to the seating postion.

For generating reports, info from all four tables can be combined and
displayed as if it were a single table.


Before adopting this structure, you should ask at least these two
questions:
- do I care if the same vehicle is involved in more than one quote? If
NOT, then this structure is adequate -- each vehicle is considered as
a separate entity. However, if you *do* want to know whether a single
vehicle is involved in more than one quote, then you will require a
many-to-many relation between quotes and vehicles, and will require an
additional junction table.

- do you care whether the same component (e.g. Brand X headlight) is
used for more than one quote. If so, then you require a many-to-many
relationship between SeatingPositions and components (instead of the
one-to-many relationship shown here), and will require an additional
junction table.

HTH

The 2 tables that I'm referring to are:
Master Quote TableQuote Content Table - Child

This is the Quote Content Table sturcture.
The PK is an index of all 4 fields.

The Master Quote Table itself has a pk field of Quote ID and is linked to
the Content Table above.

Thanks for the tip on field names and spaces... But at this point, can I
really change them without mucking up all my existing links, forms & queries?

If you need any more info to help, please let me know.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Guest]

Jack,
I think you elluded to my problem in your comments cut below:
Yes I do need to have these types of relationships.
That's why I have 4 fields in the primary key all based off of other pk's in
other tables.


"> - do I care if the same vehicle is involved in more than one quote? If

NOT, then this structure is adequate -- each vehicle is considered as
a separate entity. However, if you *do* want to know whether a single
vehicle is involved in more than one quote, then you will require a
many-to-many relation between quotes and vehicles, and will require an
additional junction table.

- do you care whether the same component (e.g. Brand X headlight) is
used for more than one quote. If so, then you require a many-to-many
relationship between SeatingPositions and components (instead of the
one-to-many relationship shown here), and will require an additional
junction table."

I presume that you have additional tables that you haven't mentioned
e.g.
- a vehicle table with a PK of VehicleID
- a seating position table with PK of SeatingPositionID
- a component table with PK of ComponentID

Each of these items is presumably related to your child table via the
corresponding field in the child table.

=====>>> These are all correct statements....