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
|
|||
|
|||
removing redudancies in the table
Hello
There is a table with me which stores: county_name, Year_field, population, number_of_cases and rate for a disease, for a particular county for each year. Here as I store data for more and more years, the year and county columns are getting repeated. Which way I can reduce the redundancies in storing the above said information with a better table design. any help is appreciated, thank you. regards talktobatchu. |
#2
|
|||
|
|||
If you are adding sets of fields ("the year and county columns are getting
repeated"), your design belongs in a spreadsheet. If you wish to make good use of the features and functions of MS Access, you'll need to normalize your data structure. I may not have a clear understanding of your data/situation, so take the following suggestion as a rough idea... trelAnnualCountyStatistics (a "relation"/"resolver"/"junction" table) ID CountyID (this is a foreign key, from the tblCounty -- see below) StatisticsYear Population NumberOfCases DiseaseRate tlkpCounty (a "lookup" table, listing counties) CountyID (a primary key, uniquely identifying each County) County Please note that the Annual Statistics table design is predicated on the information you provided, and is only appropriate if there is ONLY one "disease". If you have a 1:M relationship between County and Disease, you'll need a different structure. -- Good luck Jeff Boyce Access MVP "talktobatchu" wrote in message ... Hello There is a table with me which stores: county_name, Year_field, population, number_of_cases and rate for a disease, for a particular county for each year. Here as I store data for more and more years, the year and county columns are getting repeated. Which way I can reduce the redundancies in storing the above said information with a better table design. any help is appreciated, thank you. regards talktobatchu. |
#3
|
|||
|
|||
Hello Jeff
Many thanks for the solution. The solutions is perfect for the table description which I have given earlier and the data is only for one particular disease, as you guessed. When the data needs to stored for different diseases with all other fields remaining the same, like a 1:M relationship. Should there be a third table storing the disease names with a disease-ID linking it to the Annual Statistics table. any help is appreciated. regards talktobatchu. "Jeff Boyce" wrote: If you are adding sets of fields ("the year and county columns are getting repeated"), your design belongs in a spreadsheet. If you wish to make good use of the features and functions of MS Access, you'll need to normalize your data structure. I may not have a clear understanding of your data/situation, so take the following suggestion as a rough idea... trelAnnualCountyStatistics (a "relation"/"resolver"/"junction" table) ID CountyID (this is a foreign key, from the tblCounty -- see below) StatisticsYear Population NumberOfCases DiseaseRate tlkpCounty (a "lookup" table, listing counties) CountyID (a primary key, uniquely identifying each County) County Please note that the Annual Statistics table design is predicated on the information you provided, and is only appropriate if there is ONLY one "disease". If you have a 1:M relationship between County and Disease, you'll need a different structure. -- Good luck Jeff Boyce Access MVP "talktobatchu" wrote in message ... Hello There is a table with me which stores: county_name, Year_field, population, number_of_cases and rate for a disease, for a particular county for each year. Here as I store data for more and more years, the year and county columns are getting repeated. Which way I can reduce the redundancies in storing the above said information with a better table design. any help is appreciated, thank you. regards talktobatchu. |
#4
|
|||
|
|||
I'm confused. If there is only one disease, you only need the NumberOfCases
and DiseaseRate in the record that holds the County's information for each year. If the underlying relationship is that one county can have many years, and each County/Year can have many diseases, you need a different table structure to represent it in a relational database such as Access. -- More info, please ... Jeff Boyce Access MVP "talktobatchu" wrote in message ... Hello Jeff Many thanks for the solution. The solutions is perfect for the table description which I have given earlier and the data is only for one particular disease, as you guessed. When the data needs to stored for different diseases with all other fields remaining the same, like a 1:M relationship. Should there be a third table storing the disease names with a disease-ID linking it to the Annual Statistics table. any help is appreciated. regards talktobatchu. "Jeff Boyce" wrote: If you are adding sets of fields ("the year and county columns are getting repeated"), your design belongs in a spreadsheet. If you wish to make good use of the features and functions of MS Access, you'll need to normalize your data structure. I may not have a clear understanding of your data/situation, so take the following suggestion as a rough idea... trelAnnualCountyStatistics (a "relation"/"resolver"/"junction" table) ID CountyID (this is a foreign key, from the tblCounty -- see below) StatisticsYear Population NumberOfCases DiseaseRate tlkpCounty (a "lookup" table, listing counties) CountyID (a primary key, uniquely identifying each County) County Please note that the Annual Statistics table design is predicated on the information you provided, and is only appropriate if there is ONLY one "disease". If you have a 1:M relationship between County and Disease, you'll need a different structure. -- Good luck Jeff Boyce Access MVP "talktobatchu" wrote in message ... Hello There is a table with me which stores: county_name, Year_field, population, number_of_cases and rate for a disease, for a particular county for each year. Here as I store data for more and more years, the year and county columns are getting repeated. Which way I can reduce the redundancies in storing the above said information with a better table design. any help is appreciated, thank you. regards talktobatchu. |
#5
|
|||
|
|||
Sorry for not making myself clear in giving out the structure of the db. We
actually store information for various diseases(cases and rates) of our state in the database. For each county, for every year, the number of cases and rates along with the population details of the county are stored in the database tables. Right now I'm having separate tables for each disease. The countyname and yearfield are getting repeated in storing the above information in the table. Current table structure(s) look as following: EntryID DistNum CtyName Yearfield Population Cases Rate -------------------------------------------------------------------------------------- Which will be the best way to structure the tables and store the data without any redundancies. thanks for any help. regards talktobatchu "Jeff Boyce" wrote: I'm confused. If there is only one disease, you only need the NumberOfCases and DiseaseRate in the record that holds the County's information for each year. If the underlying relationship is that one county can have many years, and each County/Year can have many diseases, you need a different table structure to represent it in a relational database such as Access. -- More info, please ... Jeff Boyce Access MVP "talktobatchu" wrote in message ... Hello Jeff Many thanks for the solution. The solutions is perfect for the table description which I have given earlier and the data is only for one particular disease, as you guessed. When the data needs to stored for different diseases with all other fields remaining the same, like a 1:M relationship. Should there be a third table storing the disease names with a disease-ID linking it to the Annual Statistics table. any help is appreciated. regards talktobatchu. "Jeff Boyce" wrote: If you are adding sets of fields ("the year and county columns are getting repeated"), your design belongs in a spreadsheet. If you wish to make good use of the features and functions of MS Access, you'll need to normalize your data structure. I may not have a clear understanding of your data/situation, so take the following suggestion as a rough idea... trelAnnualCountyStatistics (a "relation"/"resolver"/"junction" table) ID CountyID (this is a foreign key, from the tblCounty -- see below) StatisticsYear Population NumberOfCases DiseaseRate tlkpCounty (a "lookup" table, listing counties) CountyID (a primary key, uniquely identifying each County) County Please note that the Annual Statistics table design is predicated on the information you provided, and is only appropriate if there is ONLY one "disease". If you have a 1:M relationship between County and Disease, you'll need a different structure. -- Good luck Jeff Boyce Access MVP "talktobatchu" wrote in message ... Hello There is a table with me which stores: county_name, Year_field, population, number_of_cases and rate for a disease, for a particular county for each year. Here as I store data for more and more years, the year and county columns are getting repeated. Which way I can reduce the redundancies in storing the above said information with a better table design. any help is appreciated, thank you. regards talktobatchu. |
#6
|
|||
|
|||
One of the tenants of a strong relational design is that you DON'T have
"separate tables for each disease" (or each person, or each vehicle, or each ....). While that is the typical design for a spreadsheet, Access isn't a spreadsheet! I suspect your efforts would be better solved by turning off your computer and using paper & pencil to sketch out the entities, attributes and relationships involved in your situation. This may mean that what you've already done will need to be modified. From what you've been describing, you may have something like: Counties Cities Diseases AnnualDiseaseCasesPerCity I'm guessing you have: one County can have 1:M Cities a city has a population (this is an attribute of the city, and may change over time) a city has a number of cases of a disease each year Notice that NONE of these involve "Rate" -- unless I'm mistaken (always possible), a disease rate is a calculated value (#cases/population). Calculated values rarely need to be saved in an Access database, and there are some very good reasons NOT to save calculated values. Again, this is unlike Excel, but Access isn't a spreadsheet. Take a look at the topic of normalization, turn off your computer, and see what you come up with if you step away from your current table structure. -- Good luck Jeff Boyce Access MVP "talktobatchu" wrote in message ... Sorry for not making myself clear in giving out the structure of the db. We actually store information for various diseases(cases and rates) of our state in the database. For each county, for every year, the number of cases and rates along with the population details of the county are stored in the database tables. Right now I'm having separate tables for each disease. The countyname and yearfield are getting repeated in storing the above information in the table. Current table structure(s) look as following: EntryID DistNum CtyName Yearfield Population Cases Rate -------------------------------------------------------------------------- ------------ Which will be the best way to structure the tables and store the data without any redundancies. thanks for any help. regards talktobatchu |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |
Removing errors from a Pivot table | Mighty Magpie | General Discussion | 2 | February 3rd, 2005 03:15 PM |
Automatic filling of fields in table two from table one | Jim Kelly | Database Design | 1 | September 27th, 2004 10:16 PM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
Semicolon delimited text query help | Al Guerra | Running & Setting Up Queries | 3 | August 12th, 2004 11:50 AM |