Help with some basic design

[ngordon]

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.

 

[Dorian]

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

-Dorian

 

[ngordon via AccessMonster.com]

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.

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.

 

[Fred]

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.

 

[John W. Vinson]

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!

 

[ngordon via AccessMonster.com]

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]

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?

 

[ngordon via AccessMonster.com]

Each record for the 2nd table contains about 10 fields (columns) including
1. A single name of a specific government program available (HUD, FHA, etc)
2. A single unique 5 digit ID number for that program
3. The state or states where that program is available
4. The county or counties where that program is available in that state
5. Some fields with dates that are fairly irrelevant

These programs are only available in a select number of counties and not
everywhere in the country. I am trying to cross reference the companies in
the first list by their counties to the counties that have programs in the
second list.

 

[Fred]

County names are often repeated between states. And so you have inherent
ambiguity in your one table if it lists multiple states and multiple counties
as it does not say which state each county is in. It probably relies on
human interpretation based on other factors. This really isn't a database
table structure. For example, if it lists IL and MN, and Llake county is
smongst the counties, is that Lake COunty IL, or Lake County MN?

Also, to clarify, a "match" in a county meas a match of both the county name
and it's state's name, not just the county name.

If it feasible, you should turn that table into a normalized DB table
structure. If not, of if you just wanted a narrower fix, you could just
execute a query which looks for the state being within the string of states
listed in the field, and the county name being within the string of county
names in the field. This process would be limited by the inherent ambiguity
described above.

To put it into a real DB table structure, we would need to know whether
those 240 records include repetions of the programs. For now let's assume
not, that your table is a listing of 240 programs plus the ambiguous listing
of the states/counties where they occur. In this case (change/shorten names
as desired) make a program tabl with 240 records, same as your current table
except no state or county info, and add a ProgramIDNumber Autonumber PK
field.

Now make a table which has a record for each instance of the program
applying in a state. Include a FK ProgramIDNumber, The state abbreviation,
and a PK StateInstanceID. Now make a table for each instance of a program
applying in a county. FK StateInstanceID, and CountyName. (add an
optional PK if you want). Link the FK's to theri namesake primary keys.

Make a query that has all of the fields from the above tables plus a colum
which concentates the County&State. Make a query from your company table
which concentates it's county & state. Linke these concantated colums
together. The result will show all applicable programs for every company.

I'm good at unscrambling situaitons and structure, but much weaker than
other responders on the other Access developer stuff. They may know a
better way to handle the latter part of my post.

 

[ngordon via AccessMonster.com]

Fred,

I only had to read this a couple times, but I actually got the concept and
think I have a shot a getting this. Thank you so much