----- Tom Ellison wrote: -----
Dear Rick:
I've inserted comments below:
I have a database that has the following;
information
(with a vendor number),
(with state number)
(each state name in form is a hyperlink)
that state.
(each county name in form is a hyperlink)
that state with all of the phone and fax info for each
county.
WOOPS! You mean there are 50 tables?
Make one table called StateCounty with 2 columns, State and County.
How do i enter information into the vendor table, for
each state and county that the vendor does business in????
Have a combo box into which the user first places the name of the
state using the states table.
Once the state has been entered, change the RowSource (using the click
event of the state combo box) of the county combo box to show only
those counties for the selected state. Or, use a RowSource like this:
SELECT County
FROM StateCounty
WHERE State = [Forms]![YourFormNameGoesHere]![StateComboBox]
Keeping in mind of course that any one vendor may do
business in 2 states, but only 3 counties in each of
those states, or do business in all 50 states. we have
575 vensors.
OK, the above information was for only one state/county per vendor.
If you want to allow multiple states and counties per vendor, you have
a many-to-many relationship. This means you have yet another table:
VendorStateCounty. It has those three columns in it: Vendor, State,
and County. This will almost certainly be a separate continuous
subform on the vendor form.
The end result is that I want to be able to retreive this
information with a query.
As in, what? You want to see all the state/county combinations for
any selected vendor? For each and every vendor?
Well, the above method is the tried and proven approach. It's a good
foundation for whatever is likely to come along. You can trust it,
because it accurately and flexibly models what you are doing.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Hi Tom,
What I really want to do is the following;
Step one:
I want to click on the STATE NAME, and
GoTo the form for that state listing all of the counties in that state.(I only need the county names here)
Step Two:
Click on a COUNTY NAME, and
1.) GoTo a form that shows me all of the phone and fax information for that county, and
2.) Present a list of all of the vendors doing business in that county.(there may be only two, or there could be ten)
Step Three:
Click on a VENDOR NAME, and
GoTo that vendor's record.
I currently have the program doing step one(using a hyperlink with the state form as a target),
and #1 of step two(using a query for each different county).
And, if I do it this way, I will end up with a query for every county in th US.(all 3000 of them)
My query in step two now shows the information for that particular county(phone, fax, etc...). but that's all.
So,
1.) how do I get my query in step two to pull the information from the vendors records for any and all vendors doing business in that county ?
(I only need to list the vendor names, and costs),
and,
2.) how do I enter state and county information into each vendor's record ?
Again, keeping in mind that one vendor may do business in three states, but only 2 counties in one state, 14 counties in another state, etc...
and one vendor may do business in every county in all 50 states.
I also want to be able to update my vendor files periodically and have any new information, or any new vendor, come up in the Query results from #2 of step two.
This is the first database that I have ever built using Access, so I know that I have a lot to learn, but it seems to me that there should be a simple way to accomplish the end result.
I'm pretty close now, but I have probably made this a lot harder than it has to be.
At this point I am at a loss.
Any assistance that you may be able to render will be greatly appreciated.
Thanks,
Rick
