last step in access 2000

[Guest]

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, an
1.) GoTo a form that shows me all of the phone and fax information for that county, an
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, an
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

Ric

I have a database that has the following

a vendor table & vendor form listing all vendor
informatio
(with a vendor number)

a states table & states form(list of all 50 states
(with state number
(each state name in form is a hyperlink

a form for each state listing all of the counties for
that state
(each county name in form is a hyperlink

a table for each state that lists all of the counties in
that state with all of the phone and fax info for each
county

How do i enter information into the vendor table, for
each state and county that the vendor does business in???
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
The end result is that I want to be able to retreive this
information with a query

Listing the county information(phone, Fax, Etc...

 

[[MVP] S.Clark]

Did you ever take Tom Ellison's advice and "Make one table called
StateCounty with 2 columns, State and County."?

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.