how to relate between main and sub invoice

[saudkazia]

i am trying to create a invoice/inventory database.



i have created tables (examples of fields and other fields do exist but shortened as they are not relevant in this question. assume id being autonumber and is written id for the sake of it is actually tablename_id)

products table (generic items can be reused)
id, name, model, price, prod_desc

accessories table generic items can be reused
id, accessory, price, acc_desc

invoice
id, invoice_date, customer, total amount, paid (yes/no), payment_type

invoice detail
id, invoice_no, item_no, unit price, qty


when i create the invoice form i need to ensure that i have two subforms that will show the item details for the products and for the accessories as the details in each will vary significantly.

what is the best way to go about doing this. basically i need to map the item detail to the product or accessories.

example

Main Job
1. Dining Table a, b, c, d, e, f, g, h, (different fields) amount, qty, subtotal calculated
2. bed a, b, c, d, e, f, g, h, (different fields) amount, qty, subtotal calculated
Total
accessories
1. table cover i,j,k,amount, qty, subtotal calculated
2. pillows i,j,k,amount, qty, subtotal calculated
3. mattress i,j,k,amount, qty, subtotal calculated
Total

grand total

how would i be able to relate all the subforms to the main invoice form without creating too many tables for transactions

[ranman256]

Make a union query....
all items in main
union
all items in accessories.

[saudkazia]

they will different fields and number of fields

[ranman256]

If this is what you are wanting,
then you,must compensate each query so they BOTH have the same fields.
even tho some will be empty.