A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with some basic design



 
 
Thread Tools Display Modes
  #1  
Old December 6th, 2008, 03:32 PM posted to microsoft.public.access.tablesdbdesign
ngordon
external usenet poster
 
Posts: 1
Default 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.

  #2  
Old December 8th, 2008, 04:23 PM posted to microsoft.public.access.tablesdbdesign
Dorian
external usenet poster
 
Posts: 542
Default Help with some basic design

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.


  #3  
Old December 8th, 2008, 04:44 PM posted to microsoft.public.access.tablesdbdesign
ngordon via AccessMonster.com
external usenet poster
 
Posts: 4
Default Help with some basic design

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

  #4  
Old December 8th, 2008, 05:34 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Help with some basic design

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.
  #5  
Old December 8th, 2008, 06:22 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Help with some basic design

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]
  #6  
Old December 8th, 2008, 06:50 PM posted to microsoft.public.access.tablesdbdesign
ngordon via AccessMonster.com
external usenet poster
 
Posts: 4
Default Help with some basic design

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

  #7  
Old December 8th, 2008, 07:36 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Help with some basic design

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?
  #8  
Old December 8th, 2008, 08:13 PM posted to microsoft.public.access.tablesdbdesign
ngordon via AccessMonster.com
external usenet poster
 
Posts: 4
Default Help with some basic design

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 wrote:
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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200812/1

  #9  
Old December 8th, 2008, 10:36 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Help with some basic design

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.

  #10  
Old December 8th, 2008, 11:28 PM posted to microsoft.public.access.tablesdbdesign
ngordon via AccessMonster.com
external usenet poster
 
Posts: 4
Default Help with some basic design

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

Fred wrote:
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.


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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.