Table/Query Lookup

F

F. M. Tom

I am trying to populate a field in access called “quoted to†which will
include several customers. I am using a table/query to look up the customers
in a separate table called customers. So far I am only able to put 1 customer
in this field. Does anyone know if I can put multiple customer names in the
same field in this manner.
 
J

Jerry Whittle

Sounds like you need a third table commonly called a bridging or
cross-reference table. You have a many-to-many relationship with one quote
for many customers and a customer can have many quotes. Therefore you need
this third table that contains a foriegn key from the quotes table and also
the customer FK.

Actually what you might need is a copy of Database Design for Mere Mortals
by Hernandez to explain this concept better than I can in this short space.
 
J

John Vinson

I am trying to populate a field in access called “quoted to” which will
include several customers. I am using a table/query to look up the customers
in a separate table called customers. So far I am only able to put 1 customer
in this field. Does anyone know if I can put multiple customer names in the
same field in this manner.
Click to expand...

If you have a Many (quotes) to Many (customers) relationship, you need
THREE tables. You cannot put multiple customers in one field!

A possible design would be

Quotes
QuoteID Primary Key <this might be your unique Quote Number>
<information about the quote>

Customers
CustomerID Primary Key <perhaps an autonumber>
LastName
FirstName
<other bio and contact information>

CustomerQuotes
QuoteID <link to Quotes>
CustomerID <link to Customers>
<any fields needed to store information about THIS quote for THIS
customer, e.g. a yes/no field for accepted, a memo field for comments,
a quote date, etc.>

To do data entry, you'ld use a Form for Quotes with a Subform based on
CustomerQuotes. There'd be a CustomerID combo box on the subform to
select the customer; for a multi-customer quote you'ld just add
multiple rows.

John W. Vinson[MVP]
 
J

Jerry Whittle

I just applied a dope slap up the side of my head. I should explain
abbreviations. FK stands for Foreign Key and PK stands for Primary Key. A
primary key is a field or set of fields that uniquely identifies a record in
a table. Say that Jerry is CustomerID 1 and CustomerID is the primary key.

Now a Customer can have many Quotes. To link these two tables together I'll
have a CustomerID_FK field in the Quotes table and put the number 1 in each
of Jerry's quotes. That way we can link these two tables together. This is
the ideal one-to-many ( 1-M ) relationship.

Your problem is that not only can a Customer have many Quotes, a Quote may
have many Customers. That's the dreaded Many-to-Many ( M-M ) relationship.
The way to handle this is a bridging, linking, or cross-reference table (all
names for same thing). That way you have something like this:

Customer Link Quotes
1 - M - 1

This concept is better presented in the book along with nicer graphics!
 
F

F. M. Tom

Thanks, Jerry - I should appologize to you for not knowing the abbreviations.
My son found he has access to the book on line and I looked at the first two
chapters last night and will get more into it tonight.
 
F

F. M. Tom

I have set up the 3rd table and I have an input form designed. Can you tell
me how I will be able to add more than one customer to the quoted to field
and how it will be stored in the database. I have used access a little but
nothing this complex before. I was using a program on my palm which would let
me enter more than one name in the "quoted to" field. Thanks in advance for
any help you can give me or any publications you can refer me to. As I
remember Access for Dummies does not go this deep.
 
J

John Vinson

I have set up the 3rd table and I have an input form designed. Can you tell
me how I will be able to add more than one customer to the quoted to field
and how it will be stored in the database.
Click to expand...

You do NOT want to "add more than one customer to the quoted to
field".

Relational databases DO NOT WORK THAT WAY. Fields should be "atomic",
having only one value.
I have used access a little but
nothing this complex before. I was using a program on my palm which would let
me enter more than one name in the "quoted to" field. Thanks in advance for
any help you can give me or any publications you can refer me to. As I
remember Access for Dummies does not go this deep.
Click to expand...

The Dummies book seems to be ok for people using existing Access
databases, but (by design) doesn't deal with relational design or
database creation.

Rather than trying to store multiple names in one field, please
investigate my suggestion. It's standard practice, and it works well.


John W. Vinson[MVP]
 
F

F. M. Tom

To do data entry, you'ld use a Form for Quotes with a Subform based on
John - The above paragraph is what I have been trying to do. I agree with
all that you have said. I have set up the "customer quotes" table but am
having trouble setting up the subform or even a form that will let me add
more than one customer to the quoted to form. This is what I am looking for
help on. - Thanks
 
J

John Vinson

To do data entry, you'ld use a Form for Quotes with a Subform based on

John - The above paragraph is what I have been trying to do. I agree with
all that you have said. I have set up the "customer quotes" table but am
having trouble setting up the subform or even a form that will let me add
more than one customer to the quoted to form. This is what I am looking for
help on. - Thanks
Click to expand...

Please post the following information:

The Recordsource properties of your mainform and subform (show the SQL
view of the query if it's a query)
The Primary Key of each table
The RowSource of the combo box (post the SQL)
The Control Source and Bound Column of the combo box

John W. Vinson[MVP]
 
F

F. M. Tom

I do not know how to find all that you ask for but here is what I have:

record source of main form - projects (the table of all proposals)
record source of sub form - customerquotedto (this is the combo table)
primary key of projects - "quote no assigned" auto generated no.
primary key of customers - "cust ID no" auto generated no.
both above primary keys are also the primary keys in "customerquotedto"

The last two items you are looking for I do not know how to get. If you can
explain how I will get them to you

Thanks
 
J

John Vinson

primary key of customers - "cust ID no" auto generated no.
both above primary keys are also the primary keys in "customerquotedto"
Click to expand...

That's your problem.

If the CustIDNo is the Primary Key of customerquotedto, then you can
have one and only one record for that CustIDNo.

John W. Vinson[MVP]
 
Top