Linking a table to two feilds????????

[CMB1998]

I have a database I am starting to build. In it there are VENDOR NAM
and a VENDOR NUMBER fields. I want the main table to look up th
values from another table and post it to the main table. The proble
is that I want it to post from one field to two fields.

Basically - I have a sub table with two fields VENDOR and VENDO
NUMBER. I did a lookup wizard on the main table so that there is
drop down menu that shows all the values from VENDOR on the sub table.
I want the corresponding vendor number to post in the next fiel
automatically. So if someone selects the vendor "Ace Hardware" th
corresponding vendor number will post automatically in the next field.

THANKS!

Chris

 

[Rolls]

You need to understand data normalization first.

You have one entity called "Vendor" which has two characteristics 1)
VendorNo and 2) VendorName. This data belongs in two fields in tblVendor.

Create a query that references tblVendor. In the QBE wibdow criteria field
below the VendorNo column type "[Vendor Number]. The user sees a message
box, types in a valid VendorNo such as "123". Then the query returns "Ace
Hardware."

 

[Rolls]

There is no such thing as a "subtable". Maybe you mean that there is a
Parent-Child relationship between two Entities (tables). Every table is
related to at least one other table with a 1:Many relationship via a join
(query definition). This is what makes it a "relational" DB. You might
have these tables:

tblProducts
tblVendors
tblCustomers
tblOrders

Each record in the above tables is a unique instance of that entity. One
record per product, vendor, customer, order, etc.

Each contains one Entity which can have multiple characteristics (fields).
One customer might have multiple orders. Each order could contain multiple
products.

Therefore you would also need these tables:

tblCustomer-Order
tblOrder-Product

The latter two are "lookup" tables which contain key values entered in the
first set of tables. tblCustomer-Order holds header information for Orders.
tblOrder-Product holds detail information about each order.

Each record in the above two tables is a unique parent-child relationship
between two or more entities.

To get data into and out of tables, use a frmMain with an embedded subform
for each parent-child relationship.

Now you've got the basics in a nutshell. The DB can handle lots of
complexity by expansion if it's designed properly.