I am working on a work order table, and I am not sure how to get a bill to field in the table. The bill to info will either come from a record in the customer table, vendor table, or some third party in no table. What is the best way to handle this? Do I need a Bill to table?

Each table you create should have an ID number, usually an "autonumber". Additionally this ID number should not be used as a product number, it should essentially be invisible. The ID number for each record should be unique, no duplicates.

The "billinfo" table would have a unique number, lets say 1234. In your order table you would have a field, lets call it BillSource, that would hold the number 1234. Through this number you can link the data in the two fields. Of course you will need to make sure that your program transfers the number 1234 for the "billinfo" table to the "Order Table".

Look-up "Create or Modify Relationships" in ACCES Help. Also Under "tools" you will also see "relationships" which should help show how this works.

To make it easy on me, what ill do is have a bound control for a field called bill to, and the options will be customer, vendor, other. I think that is the simplest way and avoids entering the same data twice.

BTW, thanks for the quick response ortaias.

Its not that simple. I am actually moving away from using bound controls and electing to set the value of the control by using visual basic. But that doesn't answer the question.

The easiest way to get a handle on this is under "objects" go to the "queries" tab and add your tables. You can then create a link between your two tables. You would then use the query as the source document for your form (not your actual tables). These can be bound fields. Also you can view your query, before designing the form to verify that all your data is as you want it to be.

Im not quite sure what it is you want me to link. The tables in question are "Vendors" and "Customers". Currently, there is nothing in common between these two tables I can link.

That's the rub. Each vendor and each customer should have unique IDnumber. I would assume that you would also have an "OrderTable" that order table would also have a unique ID for each order, but it would also have two extra fields. On field would contain the IDnumber of a customer and another field would contain the IDnumber of the vendor.

When you develop the query you will have three tables on the screen. Link the unique customer IDnumber with the customer field in the "OrderTable". Do the same thing with the Vendor Table, link the unique number to the Vendor field in the OrderTable. This query would then be used as your record source for your form.

See if you have a copy of the "Northwind" database available to you. Microsoft may still be distributing it as a sample database with ACCESS. There should be an example of how to build a query. Also check out Access help, type in "relational database " also type in "query" and select Queries: What they are and how they work" (This assumes you have the same screens that I do, I have ACCESS 2000)