Normalisation (sp?)

[g28dman]

I am seeking advice on a database with currently over 40,000 records, which was pulled off the internet from NOAA for weather related records.

The spreadsheet that they were using looks like this:

GridNumber | Year | IntervalNumber | Index Value

Each grid has history back to 1948 and each year has 6 intervals with an index value to each interval.

What I think would be better is have:

GridNumber | Year | Interval 1 | Interval 2 | Interval 3|.....| Interval 6|

And have the index value for each interval by year.

I am wanting to add some unbound fields on a form and create a "rate of payment" for each interval's index value and some other thing.

Does anyone one know of a way of moving this data over to over to a new format in an easy manner? And would it be the thing to do?

I have attached the db for looking at as anyone would be able to pull this info off the net. I removed several 10,000s records to allow for space.

I would sincerely appreciate any insight anyone could give on the subject.

 

[Bee]

I am seeking advice on a database with currently over 40,000 records, which was pulled off the internet from NOAA for weather related records.

If you have interval 1, interval 2...etc, you will need to do the same with index. That's will make your table contain less records and you won't have to repeat GridNo and year for each interval/index you input to your table.

I don't know how much payment field and the other fields are related to your primary key. But if you think they would create duplicate records or insertion/deletion anomalies, then they should be put on a seperate table and link that table to this one.

Hope this helps.

 

[g28dman]

If you have interval 1, interval 2...etc, you will need to do the same with index. That's will make your table contain less records and you won't have to repeat GridNo and year for each interval/index you input to your table.

Thanks for the response, I guess I may have worded wrong. I was planning on having this - 6 column representing 6 intervals, then each year/grid will have 1 line. That way I can just key in the year, grid & interval index values as they become available.

Do you happen to know an easy way to convert the data to this format instead of coping and pasting?

Thanks

 

[Bee]

Thanks for the response, I guess I may have worded wrong. I was planning on having this - 6 column representing 6 intervals, then each year/grid will have 1 line. That way I can just key in the year, grid & interval index values as they become available.

Do you happen to know an easy way to convert the data to this format instead of coping and pasting?

Thanks

I understand what you mean now. I don't know of any effective way to do that; however, this may help: Try to create queries that display one category of data (e.g. all indexes that correspond to interval 1) and copy the whole lot and paste it under interval 1. Then do the same for the next interval...etc until its all done.

This way may save you copying and pasting each record.

 

[g28dman]

Ok, ok I just confused myself. Your right if I do it by interval I am going to have have a different line for each grid number. I just need to step back for a minute and take another look.

Sorry for the confusion I caused on my part.

 

[The_Doc_Man]

GridNumber | Year | Interval 1 | Interval 2 | Interval 3|.....| Interval 6|

NEVER do this.

OK, hardly ever do this.

Your correct table would be
Grid #, year, interval #, data for that interval

To otherwise introduces non-normalized entries in the form of a "repeating group" (the interval1, interval2, etc. IN THE SAME RECORD).

What you are describing is a spreadsheet view. Spreadsheets work fine until you hit their record limit. Queries broken out by interval number would be impossible with repeating groups because you would need 6 different queries to get everything.

If you are really worried about minimizing space, the question becomes, "How big is the grid # field?" Because the year and interval number could each be two bytes, pretty darned small. The data for the interval would be whatever it is. So if the grid # is small, you aren't wasting anything.

If the grid # is very large, then put grid number and an autonumber in a table and use the autonumber as a foreign key in the data table. JOIN the tables when you need them to be together.

 

[g28dman]

Doc,

Thanks for the reply...I guess it boils down to "I was trying to make it more complicated, than I really wanted". I appreciate the advice and will continue with the current format.

I am diving into some unknowns as far as access as this point w/charts. I have a very nice query built showing each interval index value by year. But was wanting to take it a little deeper.

1st with percentages - How many years between 1948- 2005 did the index value fall below 90%. I created a nice chart query using criteria of index value = <90. But cant seem to get a number like 36% of those years or 48%.

2nd - This information is used for a rainfall insurance for ranchers. If the index value falls below 90% we pay them a loss. I can calculate out but can not chart what the per acre indemnity would be. I'll get it though at sometime.

Then putting those charts on a single report for a single grid. I know it can be done, just may take a little time of me looking around here for what I need.

I am working on also on a different db. And without the help of this forums vast knowledge couldnt have made it this far. I appreciate all you who answer us "little peoples" questions.