Bad Table Design - Months as fields 1

[MikeAuz1979]

Hi,

I have a table with some data that I'm supposed to query against another table but the 1st table has the following horrible field design:
Site Number/Aug06/Sep06 etc.
100050 20 25

rather than the proper field design:
Site Number/Month/Value
100050 Aug06 20
100050 Sep06 25

Does anyone have any ideas how I can present this table in 2nd format using a query?

I'd rather not change the format of actual table as frankly the whole access db that the 1st table is from is a mess.

Thanks for any help

 

[Blorf]

Sounds like a union query would work.



Select Yourtable.SiteNumber, "Aug06" as Period, YourTable.Aug06 as Value from YourTable;

Union

Select Yourtable.SiteNumber, "Sep06" as Period, YourTable.Aug06 as Value from YourTable;

....

ChaZ


There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[MikeAuz1979]

Wow! great idea, Thanks Blorf!

I ended up with:

Select [Site No],'Aug 06' as Month, AugPrev as MyValues
from [Network MDQ]

Union Select [Site No],'Sep 06' as Month,SepPrev as MyValues
from [Network MDQ]

Mike

 

[Blorf]

Glad to help.

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[markphsd]

Actually, there are many good reasons to separate the dates into separate fields.

One reason is for speed. If you want to look up all of one month in the entire table, you can refer to one field that is already parsed for the date. Same goes with the day or year.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.