How do I add rows related to value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am using Access 2003. My first table "tbl_orders" has 4 fields with name
"hw_model_A" , "hw_model_B", "qty_A", "qty_B" to register purchase orders.
My second table "tbl_asset" is designed to store hw configuration and
included fields "hw_model_A" and "hw_model_B" .
Ho do I append rows to table tbl_asset related to value from "qty_A",
"qty_B" tbl_orders?
for exam:
purchase order included
3pcs of hw model A
5pcs of hw model B

How do I add into tbl_asset
3 rows where the value "hw_model_A" is duplicated
5 rows where the value "hw_model_B" is duplicated

many thanks
 
the setup you describe contains data in fieldnames (hw_model_A, hw_model_B),
which violates normalization rules. suggest a separate table for order
details, as

tblOrders
OrderID (primary key)
<other fields that describe the order *as a whole*, such as CustomerID,
DateOrdered, etc.>

tblOrderDetails
DetailID (primary key)
OrderID (foreign key from tblOrders)
Model
Quantity

the relationship is
tblOrders.OrderID 1:n tblOrderDetails.OrderID
where one Order record may have many detail records, but each detail record
is related to only one Order record - a classic one-to-many relationship.
the records in tblOrderDetails would look like

tblOrderDetails
DetailID OrderID Model Quantity
1 1 hw_A 3
2 1 hw_B 5

i'm not sure how tbl_asset fits in, but i question the need for another
table that is basically a duplicate of tblOrderDetails. recommend you read
up on relational design principles, so you'll be sure to structure your
tables/relationships correctly. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth
 
Back
Top