Transpose Data that involves subquery

Feb 16, 2012
Reaction score
Hello, I am stuck on a data structure issue, and hoping that someone can help!

I am trying to create a database for a clinic. The MD's need a report with all lab results for a given patient's first and last four visits. They want the lab measures (eg cholesterol, trig, etc) as rows and the lab dates as columns.

The only way I could think of to limit the results to the first and last four lab visits was to create queries with the alias tables to count visits by patientID and then a union of the "first" query and the "last four" query. (qryfirstlast4labs)

I "normalized" the data with a new query (qrynormalize)

I created a crosstab query (qrytranspose) but, as I have now learned (after more time than I care to admit!) that the 'jet engine' for these crosstab queries will not work since the lab number/restriction to first and last used alias tables.

I am totally stuck! To get around this problem, I have desperately tried the module from Access, then I end up with a table that does not recognize the ID as a variable (it is a transposed field so just labelled as variable #1) so I cannot filter results to each patient. I am new to MS Access and desperately trying to find a way to accomplish this goal of a report for only first and last four labs that presents lab results by date as column and lab measures as rows.

Is there a straightforward way to export my pivot table to a new query/table so that it can be used as a report or a way to set this up in vba (keep in mind, I am new!)?

Many thanks in advance for any help that you may be able to offer


  • Transpose
    206.3 KB · Views: 60


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question