Company | Products Name | Unit Price |
UniqueID | InvoiceID | Products | Company | Quantity | Price |
Company | Address | Ref | Phone | InvoceID | Date | Email Invoice? | Note | Total |
Solved! Go to Solution.
In your Items table, you have Products Name as the key. It cannot be the key if the same Product can appear for different Companies. You need to have a separate key column (AND I don't recommend to use a "name" column as a key). I would suggest simply including a UNIQUEID() column named maybe Item ID or Product ID.
The middle table looks to be the Invoice table? Here you have Company as the key column. InvoiceID should probably be the key column.
Once you have these columns set to the appropriate Key columns, when you select Product for your "Invoice List of Items" table, you should assign the Item ID or Product ID into the Products column. Then to assign Price in the "Invoice List of Items" table, you only need to use "dot" notation to "de-reference" your Products Ref column....like this:
[Products].[Unit Price]
Try the above to see how far you get and come back here to ask questions.
It might also be helpful to get a better understanding of table relationships. I would recommend looking over this article: References between Tables
Pay close attention to this section "Express ownership between tables".
In your Items table, you have Products Name as the key. It cannot be the key if the same Product can appear for different Companies. You need to have a separate key column (AND I don't recommend to use a "name" column as a key). I would suggest simply including a UNIQUEID() column named maybe Item ID or Product ID.
The middle table looks to be the Invoice table? Here you have Company as the key column. InvoiceID should probably be the key column.
Once you have these columns set to the appropriate Key columns, when you select Product for your "Invoice List of Items" table, you should assign the Item ID or Product ID into the Products column. Then to assign Price in the "Invoice List of Items" table, you only need to use "dot" notation to "de-reference" your Products Ref column....like this:
[Products].[Unit Price]
Try the above to see how far you get and come back here to ask questions.
It might also be helpful to get a better understanding of table relationships. I would recommend looking over this article: References between Tables
Pay close attention to this section "Express ownership between tables".
I really appreciate your help.
I tried what you suggested.
User | Count |
---|---|
26 | |
25 | |
25 | |
22 | |
22 |