Help with some basic design

Hi, thanks in advance for the help.

I am trying to design a relatively simple database (or so I think so) in
Access 2003. I have to data contained in 2 Excel spreadsheets. I have
imported the first one which contains data including company name, address,
city, state, county and some other data unique to each record.

The second XLS contains data that with records that each have their own
unique record number (plus some other 1 to 1 data) but each record could be
associated with any number of counties in a given state.

I need to associate the records in this table with those particular counties
for that record (That is data in a pdf which I will have to manually acquire
in order to associate to that record.) Then I will need to create the query
or report that will allow me to search a company (data from 1st table, which
will have a single county associated to that company record) and see if that
company is in a county that is associated to a record in the 2nd table.

Any advice or help will be greatly appreciated.

Its best to ask one question per message rather than expect someone to design
your entire application. Then we just feel overwhelmed.

-Dorian

"ngordon" wrote:

Hi, thanks in advance for the help.

I am trying to design a relatively simple database (or so I think so) in
Access 2003. I have to data contained in 2 Excel spreadsheets. I have
imported the first one which contains data including company name, address,
city, state, county and some other data unique to each record.

The second XLS contains data that with records that each have their own
unique record number (plus some other 1 to 1 data) but each record could be
associated with any number of counties in a given state.

I need to associate the records in this table with those particular counties
for that record (That is data in a pdf which I will have to manually acquire
in order to associate to that record.) Then I will need to create the query
or report that will allow me to search a company (data from 1st table, which
will have a single county associated to that company record) and see if that
company is in a county that is associated to a record in the 2nd table.

Any advice or help will be greatly appreciated.

Thanks, I don't think I phrased my whole question correctly and am not
expecting people to do something for free that I should be paying for. I was
actually wondering why I wasn't getting a response. What a newbie ;-(

Take 2 - So my need is for the best way to associate a state in the "ST"
field that will then allow the selection of on or more counties located in
that state in the "COUNTIES" field. I have the data in XLS files.

I am hoping this is a more appropriate request and again, thanks in advance
for any help.



Dorian wrote:
Its best to ask one question per message rather than expect someone to design
your entire application. Then we just feel overwhelmed.

-Dorian

Hi, thanks in advance for the help.

[quoted text clipped - 15 lines]

Any advice or help will be greatly appreciated.

--
Message posted via http://www.accessmonster.com

I noticed that still nobody answered. If I may direct in an attempt to be
helpful.....

Overall, a question like this requires telling us about the nature and
structure of your data. You did this well on the first table, everything
after that was a jumble, hopping all over the place telling us about
ancillary items. Be sure to include telling us what specifically is in that
second table..

Also a clearer statement of what you are trying to do.

Hope that helps a little.

Thanks for the response, I will try to be more clear. My first set of data is
company contact information including their state and county for over 42000
companies in an excel spreadsheet. The second set of data is 240 records
which describe a certain status for a given geographic area. That
spreadsheet contains records which each having its own unique ID number and
each record covering one or more states AND one or more counties in that
state.

I am trying to be able to use the county location from the company records
(first data set) to see if they are in one of the counties included in the
records of the second data set.

Again, hope this is stated better and is a reasonable request. THANKS

Fred wrote:
I noticed that still nobody answered. If I may direct in an attempt to be
helpful.....

Overall, a question like this requires telling us about the nature and
structure of your data. You did this well on the first table, everything
after that was a jumble, hopping all over the place telling us about
ancillary items. Be sure to include telling us what specifically is in that
second table..

Also a clearer statement of what you are trying to do.

Hope that helps a little.

--
Message posted via http://www.accessmonster.com

Still not clear on the structure / contents of the second table. Can you
tell use the key fields in it and and an example record?

On Mon, 08 Dec 2008 15:44:59 GMT, "ngordon via AccessMonster.com" u48023@uwe
wrote:

Thanks, I don't think I phrased my whole question correctly and am not
expecting people to do something for free that I should be paying for. I was
actually wondering why I wasn't getting a response. What a newbie ;-(

Take 2 - So my need is for the best way to associate a state in the "ST"
field that will then allow the selection of on or more counties located in
that state in the "COUNTIES" field. I have the data in XLS files.

It *sounds* like you want to be able to first select a state, and then select
from the counties within that state; e.g. you might have Benton County,
Arkansas and Benton County, Oregon and want to be able to select the right
one.

What I'd recommend is having a table of states and counties. You can actually
get one on the web:
http://www.itl.nist.gov/fipspubs/co-codes/states.htm
Dig around on this site and you can download all the states and counties or
parishes with their unique FIPS code. This can be loaded into an Access table.
You can then base Combo Boxes on queries on this table.

You can base your County Combo box on a query referencing the State combo box
on the form as a criterion.

Hope this gets you started... if I'm misunderstanding the question please post
back!
--

John W. Vinson [MVP]