I am having a little issue and I am sure it's simple.
Ok, the database is for creating invoices and contact management.
Table 1 - Contact information, name, address, map, membership information, last contact date, blah, blah,
Table 2 - Buyers Information
Table 3 - Sellers Information
Table 4 - Invoices
Table 5 - Invoice Data
Table 5 - Products
Table 6 - Purchases
Everybody is a "table 1" Contact
Everybody could be a Buyer
Everybody could be a Seller
Every Invoice needs a Table 2 "buyer" and Table 3 "seller"
invoice has a portal from Invoice data where a table 5 "product" is a pull down and fills the description and price and does the calculation for final price, totals, tax, shipping, adjustments, perfect writing to the table 5 "invoice data" (for the ability to search any invoice line item and not have to enter the products on a certain line.. ) THANKS RCR..
The problem I am having is... when you create a new record "invoice layout" it has the buyer and the sellers info. at the top.. when i pick the dropdown and choose a buyer it fills in both the buyers and sellers information in both sets of fields. SO.. I created a buyers table and a sellers table. The dropdown works fine now and I can do a lookup on either buyer or seller.. Works GREAT!!
I have the lookup pull to the invoice perfectly so an old invoice will never get modified if they update email, phone, product pricing, whatever it is unless a new lookup is performed.
But how do I add the "contact" to the "buyer" and "seller" tables without having to go to the contacts table, select the "customer ID" and paste to perform the lookup into new records on both tables each time?
Hope this is enough info.
I have attached the different layouts and the relationships
I really want to say THANK YOU!!!! to everyone who helps out here..