Table/Query Lookup

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.

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"F. M. Tom" wrote:

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.

I will look for the book. Can you tell me what you mean by "customer FK"?

"Jerry Whittle" wrote:

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"F. M. Tom" wrote:

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.

On Wed, 4 Jan 2006 12:24:03 -0800, F. M. Tom
wrote:

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.

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]

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!
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"F. M. Tom" wrote:

I will look for the book. Can you tell me what you mean by "customer FK"?

"Jerry Whittle" wrote:

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"F. M. Tom" wrote:

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.

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.

"Jerry Whittle" wrote:

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!
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"F. M. Tom" wrote:

I will look for the book. Can you tell me what you mean by "customer FK"?

"Jerry Whittle" wrote:

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.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"F. M. Tom" wrote:

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.

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.

"John Vinson" wrote:

On Wed, 4 Jan 2006 12:24:03 -0800, F. M. Tom
wrote:

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.

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]

On Mon, 9 Jan 2006 14:10:05 -0800, F. M. Tom
wrote:

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.

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.

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.

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]



John W. Vinson[MVP]

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 - 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

"John Vinson" wrote:

On Mon, 9 Jan 2006 14:10:05 -0800, F. M. Tom
wrote:

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.

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.

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.

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]



John W. Vinson[MVP]

On Tue, 10 Jan 2006 14:11:02 -0800, F. M. Tom
wrote:

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 - 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

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]