Crosstab Query with Variables Created using Alias

[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

[Rabbit]

Without knowing what the data looks like, there's no way to say.

[help123]

The tblLabResults has data as
PatientID LabDate TotChol Trig.... (20 lab measures)
1 1/12/99 14
1 2/15/00
2
2
2
2

The report must include the first lab visit and the last four lab visits, with a "lab visit number" below the date:

The columns are supposed to be:

Patient ID Measure Lab#1 date 4th most recent lab date 3rd most recent lab date 2nd most recent lab date most recent lab date

The rows are supposed to be the actual measures for each patient:

Totchol
Trig
HDL
(etc)

Does that make sense? I am not sure how else to describe it and it does not look like I can post the actual database here. I moved around the data to "transpose" it (I think that is what it is called based on google searches) and I can use this converted table to create a pivot table correctly in the view of the query but I cannot find a way to show this setup in a report

[Rabbit]

There will be multiple complex steps to get this done because of the poor data structure. Here is the overview.

First, you need a way to identify the records you want. You do this by "ranking" the records by the patient id. You can use either a pure SQL approach or a combined SQL/VBA approach. I find the combined approach to be quicker for this specific environment.

Second, we have to fix the data structure by normalizing it so that you don't have the lab measures across the top. You can do this using UNION.

Now that the data is fixed, you will be able to return the records you need, which will allow you to pivot on the "ranking" created earlier.

[help123]

Thanks. Sorry, but I am not clear how to proceed. I did the ranking by using sql alias tables. I normalized the data structure with union commands. It's the pivot on this data that is the problem since the sql command for pivot (crosstab) do not allow data that had used the alias. Is there vba code to address this problem? (I am very new to Access so not sure how to write code for this.)

Is there a way to send you my database to show the problem more easily?

[Rabbit]

You just save it off as a query and then use that query as the source in a new query. Then those columns are no longer aliases. But if you wanted to, you can use the derivation rather than the alias.

[help123]

I did use the new query as the source, but the crosstab query still tells me it is not okay and I get a message that (although the query for the crosstab data is new,the source of THAT query) includes an alias. What does it mean to use the "derivation"? I do not know how to include these alias sql in the crosstab query itself.

[Rabbit]

By derivation I mean something like this:

SELECT 1 + 1 AS derivedField
FROM someTable
WHERE 1 + 1 = 2

Instead of:

SELECT 1 + 1 AS derivedField
FROM someTable
WHERE derivedField = 2