H
help123
Dear online Access wizards, I have been reading posts for days and remain utterly baffled. 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 transpose module from Access and random other code but no luck. 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
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 transpose module from Access and random other code but no luck. 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