Total Access Newbie: Designing database tables from spreadsheet

[jefflj]

I have an excel spreadsheet that has grown to be unwieldy to view relevant data at once and was hoping that moving it to an access dB would help manage the view of the data. I've created a smaller version of the spreadsheet that represents the issue and was hoping for help in designing the dB for this project. If this is too simple, please forgive me.



The problem (in the example) is this: I have a table with column headers containing Month names and a row headers containing numbers between 1 and 31 (corresponding to the numeral of the Day). The purpose of the table is to show which month has the corresponding day in it. The proposed database will contain the data in A3-J10. Data comes to me in the format of the "New Record' info in Row 17. Occasionally I will get new "Months" to add, as in column L.

The users of the database want to run 2 queries: 1) enter a "Month" and see which "Days" are contained within it. 2) enter a "Day" and see which "Months" contain it.



As you can see there is some additional data associated with each of the column and row headers, but I've pretty much got that figured out:



I've created tables to contain the additional data, Table 1 for the Day and Description, and Table 2 for the Month and Season.

My problem comes in not knowing how to set up the table for the main part of the data, the "y"s in the spreadsheet. I know I'm missing some basic piece if knowledge , maybe a lot of basic knowledge. Can someone offer help or pointers?

[June7]

A month named Septober? What calendar are you using?

A db that requires regular table modification to add new fields is not normalized.

Why do you need this in a table? Why and where from would you get records that say a day is within certain months? Doesn't everyone have memorized which months hath 31, 30, and 27/sometimes 28 days and which season a month falls in?

When building relational database, forget everything you know about spreadsheets.

Maybe this tutorial site will help http://www.rogersaccesslibrary.com/

[jefflj]

Yes that data is ridiculous, but it describes my business problem exactly. It was a simple attempt at sanitizing my data.

So my issue appears to be with designing the Entity Relationship Diagram so that I can build the dB from it. I'll see how far I can get with the tutorials. Thanks for the pointers.

[jefflj]

Thanks for the pointer. I've read the tutorials, very good, BTW, thanks and Normalization is the key.
I've come up with an ERD that I think represents the data and is in 3rd Normal form:






Please let me know if I'm on the right track here.

Thanks for your help.