Help - Table Setup - Possible Normalization??

[AC5FF]

I'm creating a new DB to track monthly usage data. I had such a hard time figuring out how to get all the data into the database I think I may have overlooked just how I was going to use the data.

I have two tables set up with data represented below. The first is the data table giving me usage information (Inputs 1 - 36 are actually field names i.e. Input1, Input2 ...) for one month. The data is everything between the periods. The data table has data for years worth of usage information.

Acct System Date    INPUTS 1 --------------------------THRU------------------------------36		
029-001	01  Jan  .z.z.h.z.z.z.z.z.z.z|z.z.z.z.z.z.-.-.-.-|-.-.-.-.-.-.-.-.-.-|-.-.-.-.-.-.    
029-001	02  Jan  .z.z.z.z.z.z.z.z.z.z|z.z.z.z.z.z.z.z.z.z|z.z.z.z.z.z.z.h.z.z|z.z.z.z.z.z.   
029-001	03  Jan  .z.z.z.z.z.z.z.z.z.z|z.z.z.z.z.z.z.z.h.z|z.z.z.z.-.-.-.-.-.-|-.-.-.-.-.-.

Acct	System	Input	Line	Address
029-001	01	1	A101	1503 GMD
029-001	01	2	A102	1503 GMD
029-001	01	3	A103	1503 GMD
029-001	01	4	A104	1503 GMD
029-001	02	1	B103	1505 GMD
029-001	02	2	B104	1505 GMD
029-001	02	3	B105	1505 GMD
029-001	02	4	B106	1505 GMD
029-001	02	5	B201	1505 GMD
029-001	03	9	C206	1507 GMD
029-001	03	10	C207	1507 GMD
029-001	03	11	C301	1507 GMD
029-001	03	12	C302	1507 GMD
029-001	03	13	C303	1507 GMD

I now need to run a query to link the address and input information from table 2 with table 1's data. But because the data inputs are all field names and the address input information are in rows my link does not work as I envisioned.

First - is the DB starting out right? I.E. normalized?
Second - thoughts as to how I can link the two tables to give me an output similar to this?

Account	System	Line	Jan	Feb	Mar	Apr	May	June	July	Aug	Sept	
029-001	01	A101	 	 	 	 	z	z	z	z	z	
029-001	01	A102	z	z	z	z	z	z	z	z	z	
029-001	01	A103	 	 	 	 	 	 	z	z	 			 
029-001	01	A104	 	 	 	 	 	v	v	v	z			 
029-001	02	B103	z	z	z	z	z	z	 	z	z		 
029-001	02	B104	 	 	 	 	 	 	z	 	 	 		 
029-001	02	B105	 	 	 	 	z	z	z	z	z	
029-001	02	B106	 	 	 	 	 	z	z	z	z	
029-001	02	B201	 	 	 	 	 	z	z	z	 	 		 
029-001	03	C206	 	 	 	 	z	z	z	z	z	
029-001	03	C207	 	 	 	 	 	z	z	z	 	 		 
029-001	03	C301	 	 	 	 	z	z	z	z	z	
029-001	03	C302	 	E	E	E	 	 	z	z	z	
029-001	03	C303	 	 	 	 	 	z	z	z	z

WHEW! Lotta Typing for this late in the afternoon!

 

[spikepl]

If you retain your table as is, then any query sql statement will have to deal with 36 columns, at least, of data. That will be hell to maintain, and makes no sense.

Get the data into a separate table, with a column marking the data, one containg the data, and one linking it to the record - the contents of the first 3 columns

 

[AC5FF]

Spike - Not sure I am following you here.

My data table ... I have to know what account number and system that data belongs with and I also need to know the date of the data. I guess I am just not visualizing the table structure any other way...

Just for info here - I've imported data now for the past 14 months - I had planned on getting at a minimum of 3yrs of history in the table. But with only 14 months the data table now has over 900 rows. That's how fast this table will grow. So, it may be 36+ columns of data but there is going to be a LOT of data in it.

Can you post an example of the table structured as you see it?