Change Layout of Data

[AccessQuestions]

I have been given a large set of records that is layed out with 10 ID fields, then 31 (Day 1, Day 2, etc.) fields representing each day of a given month. These fields contain data. I created two fields labeling the month and year as well.

My goal is to create a date field where I can put together the m/d/y data and take the corresponding day's data and delete the other 30 daily fields. I think I can temporarily create 31 extra fields that give me the date. So then I would have (m/d/y - Day 1, m/d/y Day 2, etc.). I have no idea how to then separate them into unique records.

I am looking to turn the 30k records i have into 900k by reducing the fields. I really can't pull it off manually. I am doing this in order to create graphs (vs. time of course).

I appreciate any ideas you may have on this. If you have any questions about my data or I was unclear, let me know and I will respond.

 

[pbaldy]

One way would be a UNION query, along the lines of:

SELECT OtherFields, Day1, DateSerial(YearField, MonthField, 1) AS TransDate
FROM...
UNION ALL
SELECT OtherFields, Day2, DateSerial(YearField, MonthField, 2) AS TransDate
FROM...
UNION ALL
...