Many to Many? *DELETED*

[Joel C R]

Post deleted by Joel C R

[RalphL]

There is a better way and you are not close to it.

Is a requisition limited to one vendor? Is there a reason why only 8 items can be on 1 requisition?

I assume there are a number of vendors and there are are a number of people who can generate a requisition.

You may want to have a table of vendors with their data: Vendor ID, name, address, phone, fax, contact etc.

You may want to have a table of requestors with their data.

You will need a table of products which gives vendor ID, Product ID, name, description, price, etc.

You need a table of Requisitions and a table of line items. The line items will contain Product ID, Name, description, price, quantity, extended price for each of the items. Use a lookup for price and description.

The Requisition will have a portal to Line Items with 8 rows, this will limit the requisition to 8 items.

[Joel C R]

To answer your question re: vendor restrictions, due to a quirk in the administration here, we are limited to one vendor per requisition. Therfore, I make the vendor one of the parent fields in the main table. I've set it to a custom value list, with the possibility of adding entries.

So my question is thus; if I want to be able to enter all data from the main form (new names, new accounts, new vendors, new products, et cetera). How can I arrange my relationships in order for that to occur? I can't create new records through a relationship where the match field doesn't exist yet (or can I? I've managed to do this with names & associated information, but with the line items/products tables, it seems quite a bit more difficult).

Joel