Re: Question about 4-Field Pk Subform..... Is there a better way?
From: Mike (Mike_at_discussions.microsoft.com)
Date: 01/04/05
- Next message: iam_at_notarealisp.com: "Re: Fundraising"
- Previous message: Jen: "Insert pre-made excel sheet into new access database, HOW??"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 4 Jan 2005 11:19:06 -0800
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."
"Jack MacDonald" wrote:
> 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....
>
> 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: iam_at_notarealisp.com: "Re: Fundraising"
- Previous message: Jen: "Insert pre-made excel sheet into new access database, HOW??"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
