Transpose horizontal table to a vertical table via query

Hi all,

The dates in TblHorizontal are required to be enter in this way due to a funky form layout, however, it need to be converted into a TblTableVertical table.

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

Now, while I could write a loop in VBA, but would much prefer to use a query.

I've attempted to use a crosstab but keep failing due to field number (I have around 36 date fields).

Can anybody offer a solution please?

Cheers

Thank you for your response NeedForExcel. Unfortunately, the answer provided, while useful for Excel, is not useable in MS Access, the forum for which being where I posted this thread. But I thank you for the effort nonetheless.

For those interested in the answer to this question, I came upon a query design. It looks like this:

Please Login or Register  to view this content.

This query works exactly as intended. However, it might be somewhat too slow for the large number of fields, for a large database.

I can use this in the meantime but if anybody can provide a better query, I'd certainly like to hear it. (An Access query, to clarify the application)

Thanks Alan,

I do run this and it works fine. my users will just have to get a coffee while waiting.
Cheers