Table/Query Lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

F. M. Tom - 04 Jan 2006 20:24 GMT

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.

Reply to this Message

Jerry Whittle - 04 Jan 2006 20:57 GMT

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.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

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

Reply to this Message

F. M. Tom - 04 Jan 2006 21:15 GMT

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

> 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
[quoted text clipped - 10 lines]
> > in this field. Does anyone know if I can put multiple customer names in the
> > same field in this manner.

Reply to this Message

Jerry Whittle - 05 Jan 2006 02:13 GMT

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!

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I will look for the book. Can you tell me what you mean by "customer FK"?
>
[quoted text clipped - 12 lines]
> > > in this field. Does anyone know if I can put multiple customer names in the
> > > same field in this manner.

Reply to this Message

F. M. Tom - 05 Jan 2006 13:56 GMT

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.

> 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
[quoted text clipped - 32 lines]
> > > > in this field. Does anyone know if I can put multiple customer names in the
> > > > same field in this manner.

Reply to this Message

John Vinson - 04 Jan 2006 23:19 GMT

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

Reply to this Message

F. M. Tom - 09 Jan 2006 22:10 GMT

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.

> >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
[quoted text clipped - 30 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 10 Jan 2006 19:03 GMT

>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>
[quoted text clipped - 19 lines]
>>
>> John W. Vinson[MVP]

John W. Vinson[MVP]

Reply to this Message

F. M. Tom - 10 Jan 2006 22:11 GMT

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

> >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
[quoted text clipped - 44 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 11 Jan 2006 02:30 GMT

>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
[quoted text clipped - 6 lines]
>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]

Reply to this Message

F. M. Tom - 13 Jan 2006 19:36 GMT

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

> >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
[quoted text clipped - 16 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 09 Feb 2006 06:05 GMT

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

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]

Reply to this Message