Years as field headings?

[ddelk]

I am trying to determine the best way to record multiple facts about a coal plant over a span of 30 years. The plants are designated by Primary Key and each year is a field heading. Is it better to have one table for each characteristic with each table having 30 "year" columns(2002,2003,2004,etc.) Or is it better to have one table per year with the plant characteristics as field heading (Sulfure emissions, Nox emissions, CO2 emissions, etc.) I have about the same number of characteristics as I do years so it seems that I can choose to do it either way. I just wonder if there is a more "correct" way to handle this situation.

 

[RoyVidar]

I'd never recommend using year as heading. If the information gathered for all the years are the same (same types of information, I think I'd rather recommend a structure where the year is a field in the table, perhaps also part of the primary key, but that would be a later discussion.

Else you are going to get a lot of headaches comparing data for the different years. Repeating the years as columns, as I see it, would be a "violation" of the first normal form.

Probably somethin like this:
[tt]
PlantID
RegYear
Sulf
Nox
CO2
...[/tt]

- from the little information, I think I'd recommend having PlantID and RegYear as Primary Key (a combined/composite PK)

To read up a bit more on table structure/normalisation, another member (JeremyNYC) has kindly made the document "Fundamentals of..." available thru his website. Here's a link

http://www.abcdataworks.com/fundamentals.htm

to where it can be downloaded.

- more questions, post back

Roy-Vidar

 

[ddelk]

Ok. I understand this and have considered it. I work for people who do not understand table design nor do they want to. They simply want to see plant, emissions type, and values for each year strung out diagonally. Since Access does not support "views", at least to my knowledge, what is the best way to present a view in table form that lists the data diagonally with years' as headings? Should I create a query with Year listed once for each year I want to show and then set the criteria to 2002, 2003, 2004, etc.?

 

[RoyVidar]

Now, that's a completely different question;-) The previous regarded the how to store. Don't know if my competence is sufficient to "transpose" these data.

To display for instance the year as heading and only one value, I've used crosstab queries in the past. Think I've seen some workarounds here on how to add more values to crosstabs, but I use only one value (so for instance NOX?)

For more "row values" I use the approach you mention.

In one case, I even created a temp table to use for the report, but that's a bit dangeroud because it causes the db to grow in size

- think the issue for you would be to decide what perspective to use: Easy reporting in this structure, or have the possibility of doing different calculations dynamicly with ease - average thru last 15 years (which might be PIA if the values are in different colums, adding new columns each year, rewriting all stuff based on them)...

BTW, should you decide upon a structure having the years as Fields, be sure that you don't name the fields starting with a number - that might later create even more headaches;-)

- maybe someone else has some views, suggestions...

Roy-Vidar

 

[JonFer]

Access does support views because a select query is basically a view. You can create a crosstab query to view the years as headings instead of storing the data that way. Experiment a bit in the query design window and you should be able to get what you need. You can use something like ("Yr" & [YearField]) to create non-numeric headings.

One note - if you want to use a parameter in a crosstab query, you need to define it in the query parameters window before running the query.

 

[ddelk]

Thanks very much. Food for thought.