Re: Question about 4-Field Pk Subform..... Is there a better way?
From: Jack MacDonald (jackMACmacdonald_at_telus.net)
Date: 12/23/04
- Next message: Jack MacDonald: "Re: Report does not alphabetize from Table"
- Previous message: Bear Pair: "Re: Report does not alphabetize from Table"
- In reply to: Mike: "Re: Question about 4-Field Pk Subform..... Is there a better way?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 23 Dec 2004 19:54:57 GMT
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
On Thu, 23 Dec 2004 09:35:02 -0800, "Mike"
<Mike@discussions.microsoft.com> wrote:
>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.
>> >Quote ID
>> >Vehicle ID
>> >Seating Position ID
>> >Component ID
>
>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.
>
>
>"Jack MacDonald" wrote:
>
>> 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.
>>
>>
>>
>> On Thu, 23 Dec 2004 07:59:04 -0800, "Mike"
>> <Mike@discussions.microsoft.com> wrote:
>>
>> >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
>>
>>
>> **********************
>> jackmacMACdonald@telusTELUS.net
>> remove uppercase letters for true email
>> http://www.geocities.com/jacksonmacd/ for info on MS Access security
>>
**********************
jackmacMACdonald@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
- Next message: Jack MacDonald: "Re: Report does not alphabetize from Table"
- Previous message: Bear Pair: "Re: Report does not alphabetize from Table"
- In reply to: Mike: "Re: Question about 4-Field Pk Subform..... Is there a better way?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
