- Joined
- Feb 16, 2012
- Messages
- 1
- Reaction score
- 0
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
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