Querie based on two sets of criteria

[Supraracer]

I have a table name (Job Quotes). It has a field [Customer Name] which you
can choose from the (Customer Info) Table. I have a table called (Customer
Contact Detail) it has a field [Contact Name] and it also has a field
[Customer Name] which you can choose from the (Customer Info) table. In the
(Job Quote) table there is a field [Contact Name] which I would like to
choose from the (Customer Contact Detail) table based on the [Customer Name]
field.


Tables-- Job Quote - Customer Info - Customer Contact Info
Fields Customer - Customer - Customer
Contact - - Contact

I want a combo box in the Job Quote.Contact field that will let me choose
contact info from the Customer Contact.Contact Field based on customer name.

Ie in Job Quote I choose Smith Contracting then under contact the list
should only include contacts for Smith contracting but I get all in my querie.

Thanks

John

 

[Tom van Stiphout]

On Thu, 19 Feb 2009 16:22:06 -0800, Supraracer

(I'm getting a bit dizzy from your object names so I may have changed
them a bit)
Your combobox should have its RowSource property set to something
like:
select ContactID, ContactName
from Contacts
where CompanyID = Forms!myForm!myCompanyIDField
(of course you change myObjectNames with yours)

I have some concerns about your database design. You use fields like
CustomerName as a foreign key in related tables. It is much more
common to use ID fields instead. If a customer ever changes their
business name, you only have to make changes in one table (Customers).
You may say "cascade update takes care of that". I say "sigh".

Object names without spaces or other funny characters are also
preferred, because they make the syntax simpler and more readable. No
more need to wrap everything in square brackets.

-Tom.
Microsoft Access MVP

 

[Supraracer]

Tom,

Thank you for your reply. My Objects do not have the brackets I did that for
posting purposes. My field uses the ID but named Customer name because I am a
noob. I will try to figure out with your post but thank you for thaking the
time to reply.

John

On Thu, 19 Feb 2009 16:22:06 -0800, Supraracer

(I'm getting a bit dizzy from your object names so I may have changed
them a bit)
Your combobox should have its RowSource property set to something
like:
select ContactID, ContactName
from Contacts
where CompanyID = Forms!myForm!myCompanyIDField
(of course you change myObjectNames with yours)

I have some concerns about your database design. You use fields like
CustomerName as a foreign key in related tables. It is much more
common to use ID fields instead. If a customer ever changes their
business name, you only have to make changes in one table (Customers).
You may say "cascade update takes care of that". I say "sigh".

Object names without spaces or other funny characters are also
preferred, because they make the syntax simpler and more readable. No
more need to wrap everything in square brackets.

-Tom.
Microsoft Access MVP

I have a table name (Job Quotes). It has a field [Customer Name] which you
can choose from the (Customer Info) Table. I have a table called (Customer
Contact Detail) it has a field [Contact Name] and it also has a field
[Customer Name] which you can choose from the (Customer Info) table. In the
(Job Quote) table there is a field [Contact Name] which I would like to
choose from the (Customer Contact Detail) table based on the [Customer Name]
field.


Tables-- Job Quote - Customer Info - Customer Contact Info
Fields Customer - Customer - Customer
Contact - - Contact

I want a combo box in the Job Quote.Contact field that will let me choose
contact info from the Customer Contact.Contact Field based on customer name.

Ie in Job Quote I choose Smith Contracting then under contact the list
should only include contacts for Smith contracting but I get all in my querie.

Thanks

John