removing redudancies in the table

[Guest]

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.

 

[Jeff Boyce]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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.