By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,098 Members | 1,927 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,098 IT Pros & Developers. It's quick & easy.

Extended one to many relationship

P: n/a
To help explain my question, I have illustrated the table
relationships and listed the fields in each table. Usually the Order
Details table has the most records in a database due to one to many
relationships. However, I need to be able to extend on the Order
Details table with many records on the Order Components table.

Table Relationships

Components -- Product Break Down -- Products
\
\
Order Details -- Order Components
/
/
Orders

The reason for having an Order Components table is that I'm building a
trophy shop database and most trophies are comprised on many
components.

I need to be able to record the Order Components for each Product on
each Order. Just like you can change the Unit Price on the Order
Details table (DLookup from Products); I want to be able to change the
ComponentID and Color on the Order Components table (DLookup from
Components).

For example, The Prod1 (Product) may have a default component & color
configuration of a Comp2 GS (figurine) and a Comp3 - G (base). But
the customer would actually like an S base, so the default
componentary for the Prod1 on this order would need to be changed to a
Comp2 GS and Comp3 - S. This information needs to be recorded for
purchase order forms.

Components (tbl)
ComponentID ComponentName
(prim) (ind No dupe)
1 Comp1
2 Comp2
3 Comp3
4 Comp4

Product Break Down (tbl)
ProductNameID ProductBreakDownName ComponentID Color
(prim) (ind dupe OK) (ind dupe OK) (default color
.. for component)
1 Prod1 Comp2 GS
2 Prod1 Comp3 G
3 Prod2 Comp1 G
4 Prod2 Comp2 GS
5 Prod2 Comp4 G

Products (tbl)
ProductID ProductBreakDownName UnitPrice
(prim) (ind No dupe)
1 Prod1 $15.00
2 Prod2 $20.00

Order Details (tbl)
OrderDetailsID ProductID UnitPrice
(prim) (ind dupe OK) (DLookUp Products tbl)
1 Prod1 $15.00
2 Prod1 $15.00
3 Prod2 $20.00

Order Components (tbl)
OrderCompID OrderDetailsID ProductID ComponentID Color
(prim) (ind dupe OK) (from Order (DLookUp (DLookUp
.. Details Product Break Product Break
.. tbl) Down tbl) Down tbl)
1 1 Prod1 Comp2 GS
2 1 Prod1 Comp3 G
3 2 Prod1 Comp2 GS
4 2 Prod1 Comp3 G
5 3 Prod2 Comp1 G
6 3 Prod2 Comp2 GS
7 3 Prod2 Comp4 G

The reason I have asked about DLookup is because the Unit Price is
displayed after a product is posted on the Order Details table. I
would like a form that not only dlookup's the price, but can also
generate multiple lines in an Order Components table and Product
Details Subform. It doesn't have to be a subform, I don't know what
it needs to be? Maybe it's not a DLookup I need! I've read several
messages on the Google groups and some have suggested recordsets????
Maybe I need to use a union select query or something else????

I have an Order by Customer form with Order by Customer Subform, for
the Orders table. The Order details can be changed by clicking on an
Orders button, which opens up the Orders form with Order Details
Subform, for the Order Details table. The Product details can then be
changed by clicking on a Products button, which then opens up the
Products form with Product Break Down Subform, for the
ProductBreakDown
table. The Product Break Down Subform has the Component Name from
Components table and Color from the Product Break Down table.

Could I maybe indicate a number on the Products table for the number
of
Component lines are required for each Product?
Products (tbl)
ProductID ProductBreakDownName UnitPrice # of Components
1 Prod1 $15.00 2
2 Prod2 $20.00 3
Would access be able to generate 2 lines on the Product Break Down
subform which is stored on the Order Components table when Prod1 is
typed in the Product field on Order Details Subform.

I know exactly what I want but don't know where to start this next
step, and would really appreciate any advice. I hope I have given
enough information, if not, please let me know.
Thanks
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Ingrid wrote:
To help explain my question, I have illustrated the table
...


I tried setting up the table structure without looking at what you had
and came up with a very similar set of tables and fields. One of the
few differences was that I put ComponentQuantity in both the
ProductBreakdown table (I called it tblDefaultComponents) and
OrderComponents. Use your ProductBreakDown table to get the default
components loaded on your subform when a product is selected. Allow
the user to add or delete components and component quantities as needed
for the selected ProductID on the component subform. Put all the
components and component quantities making up the products contained in
detail lines into OrderComponents (one line per component). Access
will be able to total all the component quantities for the PO's from
OrderComponents. If you think you might need to group components by
OrderID at some point in the future then you could put the OrderID in
OrderComponents also so that you don't have to do an extra join to get
it. I'm probably not the one to help with how to implement this whole
structure with the form and subforms since my methods lean toward the
unorthodox. It's pretty much doing what you did for Orders and
OrderDetails. I'm sure others will be able to provide more
conventional ways of putting this into practice if you need help there.

I hope this helps,
James A. Fortune

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.