MS Access 2010: Crosstab Query in Presence of Alias Variables

[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

[June7]

I don't work with crosstabs much so don't understand the error you encountered. If you want to provide sample data I will give it a try.

VBA is another method to 'transpose' the data. Review this example http://forums.aspfree.com/microsoft-...ry-322123.html

[help123]

Wow, many many thanks for your speedy response! Attached is my attempt at a database. I need the report to only include first and last 4 labs for each person and include the lab number. I need to set up a form to create this report for each patient (each patientID) separately. The pivot table view of qrynormalize is correctly limited to the first and last four labs, but I cannot get this format on a report.

[My database currently includes a module a found at http://support.microsoft.com/kb/182822 with the resulting table tblResultsTran but this table is not in the correct date order and I do not know how to set this up to run for each patient..or how to activate the module in a database]

Thank you, thank you, thank you!

[help123]

...and thanks for the link. I have used DLookup tables in the past, and the results are much slower, especially when I have a lot of data as I will for this lab database. The pdf of the vba seems EXACTLY what I need, but I do not know how to

1) modify it for my data setup (how do I insert the correct lab values to "fill" the table"
2) make it run to create the report (I will need to run the report for each ID, but after spending all of today with different examples and different VBA, I do not know how to label my new transformed tables which after applying mdltranspose from above link, the columns are now just numbers)

June 7, I am really hoping that you will be able to help me. I am at a complete loss of how to proceed. Many thanks for any help that you can offer!

[June7]

Note that my example procedure has a report set up to print 20 records per page in two tiers horizontally. Twenty records are transposed to a single record of 43 fields in a temp table. Every 20 records of a batch will create a new record in the temp table. When the batch is finished processing to temp table, the report prints one temp table record per page.

With your data you want to transpose up to 5 test dates for every patient into columns with the test results as rows. The problem here is that your records batch could result in more fields than can fit on a page. Even though could write up to 255 fields to a table, can't put 255 columns horizontally on a report. Also, your records batch could eventually exceed 255 columns, just depends on how many patients and tests.

I think the approach for this code will have to be to process one page of records, print the page and repeat.

Before proceeding, consider the form you want the report to take. Do you want new page for each patient or just run them continuously? How many columns should each page have? Portrait orientation might hold 5 or 6. So actually, effectively 1 page for each patient.

As for field names in the table, the 1,2,3 sequence is fine (note this is basically what I do in my example). In a report you make labels say whatever you want.

With this now in mind, do you think you can attempt some code that will produce what you want? I think you have adequate examples to draw from. The fact that you could implement code to do what you have so far is a good indicator of your capabilities in programming. I suggest you open recordset of the patients and their tests that you want to report. Loop through the recordset, process one patient's data to temp table, print report. Probably an output to print only procedure although could open report in preview and 'pause' the code with a message box.

Rats! I just remembered an issue with UNION query. I tried to have VBA open recordset from a UNION query and it failed. VBA would not even open recordset from a query based on a UNION query. If UNION was involved anywhere in the sequence, VBA would not work with the data. I had to redesign a procedure. Your data can be processed by VBA without involving UNION.

[help123]

Thank you so much for your response. I have spent the last week studying your code nonstop and trying to apply it to my data, but no clue how to proceed. After several sleepless nights trying to figure this out and looking up any relevant info, I am lost. I was able to transpose the data with the MS Access Transposer module if I limited to the PatientID currently open on the form, but then the ID was transposed to text, and I cannot connect anything together and cannot submit query to create page report for several patients and I am stuck yet again.

Your example is exactly what I am looking for-but I cannot figure out how to adapt your code!
I am desperate to find a way to set up a report with one page per PatientID, 5 columns (first and last 4 labs) with 23 rows of the different lab results. As someone who has been attempting to use VBA for only three weeks, I am not able to successfully modify the code. I could not figure out how to create the temporary data table and fill in the values. I thought my PatientID is like your ProjectID variable, and records are lab dates so your labnum is my labnum and your fieldnum is my labdate but where is the actual values and how do I change your code to fit my example?

[June7]

My example was to demonstrate a concept and not designed as a universal procedure. It presents methodology for opening and manipulating recordsets and loop structures that would be needed. These would have to be tailored to the data.

So, just to get you rolling, review the attached.

This procedure will produce report on all patients included in qryAllLabs. The tricky part is managing the report output. Since each patient must be processed as an independent report, each must be generated and output before the next. I used PrintPreview and a message box for testing. Could be an issue with sequentially printing and closing report in the same procedure if message box removed. Might close before the report can be fully rendered. Did not test sending straight to printer.

The Homocyst and UricAcid fields in tblLabResults have captions in the InputMask property.

[help123]

Wow! I cannot thank you enough! I will study your code and try to learn how it works. Many many many many thanks! Hopefully, I will be able to move forward with the forms and reports. Thanks so much!

Back to the drawing board!