I
Ingrid
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
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