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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|