I would apprecaite anyone's input on this.
I am developing a database for my girlfriend's business. She will be selling private lessons and workshops.
In her invoice table, I have a 'class code', 'Description', 'Quantity', 'Unit Price' and 'Total' fields. The 'Description' and 'Unit Price' fields come from another table called "workshops." I set up a relationship between the Invoice Table and the Workshop Table, so that when she enters the class code, it automatically shows the description and unit price. I did this so that she can easy look up and print out who is attending a particular workshop. The Quantity and Total fields are part of the invoice table. The 'Total' is a calculation where the 'Quantity' field is multipled by the 'Unit Price' field. All is working fine. A side note, there is only one item per invoice, so there is no portal on the invoice layout which shows multiple items from a line item table.
Here is where I'm stuck. In her customer table, I would like to be able to view all invoices made by that customer and show me these fields as they are displayed in the invoice table. But since those fields come from both the workshop table and the invoice table, I have no idea how to set up that type of complex relationship so it shows as it would if it came from a single table.
Thanks in advance for any help.