Convert rows to columns

[merry_fay]

Hi,
I have a ta ble in a database like this:
Column headers: EID MID M1 M2 M3 M4 M5 M6 etc
xx abc 45 69 35 57 34 52
yy bet 0 35 34 53 12 0

etc
In columns EID & MID the data is not unique
I need to convert the M.. columns to rows
eg:
EID MID Month Value
xx abc 1 45
xx abc 2 69
xx abc 3 35

excluding the 0's.

How can I do this?

Thanks
MerryFay

 

[Douglas J. Steele]

SELECT EID, MID, 1 As WhatMonth, M1 AS WhatValue
FROM MyTable
WHERE M1 <> 0
UNION
SELECT EID, MID, 2, M2
FROM MyTable
WHERE M2 <> 0
UNION
SELECT EID, MID, 3, M2
FROM MyTable
WHERE M3 <> 0
UNION
....
UNION
SELECT EID, MID, 12, M12
FROM MyTable
WHERE M12 <> 0

Note that neither Month nor Value are good choices for field names: they're
both reserved words, and using reserved words for your own purposes can lead
to problems. For a great discussion about what names to avoid, see what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

 

[merry_fay]

Hiya,

The names are slightly different -I was just using examples thanks.

I understand the code, but having not touched an access database or used SQL
for a year, I'm having problems finding where to put this data. I'm currently
using a 2003 version & previously have mainly used 97 & xp (this might be
2003 -I can't remember!!).
I have one table with the information as described & another I need to put
it into in the format described. What sort of query or what option do I need
to select so I can enter my sql?

Thanks
A very blonde(!) MerryFay

 

[cager]

Merry_fay,

If you open a new query in design view and drop in your table. You can then
change to SQL view and type/paste in the code.