Reverse Cross-Tab query

[Jeremy]

Hi,

Does anybody know how to take column headings and turn
them into data, kind of like a reverse cross-tab query?

For example: I have data with dates across the top (field
headings) and names down the left side (field
heading 'names'). This data is timesheet data, so it is
hours worked by person and by day. Can I turn this
arrangement into a table with three
headings: 'Names', 'Hours', 'date' ?

Thanks!

Jeremy

 

[John Viescas]

The general technique is to use a UNION query:

SELECT Names, #31 Jan 2004# As [Date], Jan312004 As Hours
FROM MyTable
UNION
SELECT Names, #29 Feb 2004# As [Date], Feb292004 As Hours
FROM MyTable
UNION ...

Of course, I'm just guessing at the actual "date" column names, but I hope
you get the idea.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)