Query Help

[Mandi]

I am attempting to build a database at work and have 2 weeks to complete it.
It contains a table with a list of customer account numbers, customer names,
city locations, which salesman is assigned, and which warehouse manages the
customers in that location. My fields are:

ID Warehouse# CustomerID Description City Shipto
ShiptoDescrip Salesrep1 Salesrep2

1 1 100 3M
Dallas 100 3D Plastic DRD PM
2 9 100 3M Tulsa
101 3D Plastic DM SA
3 3 100 3M
Shreveport 103 3D Plastic WC NN

The primary use of this database is contact management. The form I need to
make has to be able to have drop down boxes when I put my first letter in the
field. Let's say my choices in the drop down box when I type 3 would be 3M,
3-D Graphics, 3 Sisters. When I hit 3M and go to the City field on the form,
I need a drop down box to list the locations applicable for 3M. So, Dallas,
Tulsa and Shreveport would be the only cities listed in the box.

I have the table built and have worked hard to make the drop down boxes. I
managed to get all the Descriptions (Customer Names) to list in a drop down
box and all the Cities, however, it shows every single city in the entire
table and doesn't really pertain to the company I need it to be linked to. I
need help setting up the queries and any macros for this. I can build the
forms and even the reports, can someone please help me? Thank you in advance!
: )

 

[John Vinson]

I am attempting to build a database at work and have 2 weeks to complete it.
It contains a table with a list of customer account numbers, customer names,
city locations, which salesman is assigned, and which warehouse manages the
customers in that location. My fields are:

ID Warehouse# CustomerID Description City Shipto
ShiptoDescrip Salesrep1 Salesrep2

1 1 100 3M
Dallas 100 3D Plastic DRD PM
2 9 100 3M Tulsa
101 3D Plastic DM SA
3 3 100 3M
Shreveport 103 3D Plastic WC NN

There's a BIG problem right to start with. If you have a Many (ID) to
Many (salesrep) relationship, you should emphatically NOT have fields
Salesrep1 and Salesrep2. I'm also concerned about Warehouse# and
Customer - does each customer have one and only one warehouse?

You almost surely need more tables to model these relationships
correctly!

The primary use of this database is contact management. The form I need to
make has to be able to have drop down boxes when I put my first letter in the
field. Let's say my choices in the drop down box when I type 3 would be 3M,
3-D Graphics, 3 Sisters. When I hit 3M and go to the City field on the form,
I need a drop down box to list the locations applicable for 3M. So, Dallas,
Tulsa and Shreveport would be the only cities listed in the box.

You can make a second Combo Box dependent on the first combo box. Base
it on a Query referencing the first combo: you need a table (which you
haven't mentioned) to indicate which cities are relevant to 3M. You
can base the Cities combo box on a Query using

=Forms![YourFormName]![cboCompany]

as a criterion (where cboCompany is the name of the company combo
box); this query should return the valid cities for that company. You
will need only one line of VBA code: in the Company combo box's
AfterUpdate event you need to requery the city combo:

Private Sub cboCompany_AfterUpdate()
Me!cboCities.Requery
End Sub

John W. Vinson[MVP]

 

[Mandi]

All the information I have for the table I gave an example of was imported
from Excel. There are over 2600 company names and each warehouse location
(there are 9) can ship to the same company but in their specific region.
Tulsa couldn't ship to Shreveport customers unless the Shreveport based store
generated the order, not Tulsa.

I have started breaking the main table down, which was how the information
was imported into Access. If I have a company named Motion Flooring and
there were 11 locations for them can I run a query just based on Company
names and another on Cities, can I specify Motion Flooring as the Criteria
and also Motion Flooring's City locations? Or do I have to have a separate
table for each Company? Will I have to have 2600 queries showing the
locations for each company? Please do not forget I'm a newb.

=Forms![YourFormName]![cboCompany]

Where does this line go? In the query itself or in the table's combo box
section.

I understand the other goes into the Basic section. Thanks again for your
help!

Mandi

You can make a second Combo Box dependent on the first combo box. Base
it on a Query referencing the first combo: you need a table (which you
haven't mentioned) to indicate which cities are relevant to 3M. You
can base the Cities combo box on a Query using

=Forms![YourFormName]![cboCompany]

as a criterion (where cboCompany is the name of the company combo
box); this query should return the valid cities for that company. You
will need only one line of VBA code: in the Company combo box's
AfterUpdate event you need to requery the city combo:

Private Sub cboCompany_AfterUpdate()
Me!cboCities.Requery
End Sub

John W. Vinson[MVP]