472,328 Members | 1,922 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,328 software developers and data experts.

Crosstab Query with Variables Created using Alias

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
Feb 15 '12 #1
7 7571
12,516 Expert Mod 8TB
Without knowing what the data looks like, there's no way to say.
Feb 15 '12 #2
The tblLabResults has data as
PatientID LabDate TotChol Trig.... (20 lab measures)
1 1/12/99 14
1 2/15/00

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:


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
Feb 15 '12 #3
12,516 Expert Mod 8TB
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.
Feb 15 '12 #4
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?
Feb 15 '12 #5
12,516 Expert Mod 8TB
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.
Feb 15 '12 #6
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.
Feb 15 '12 #7
12,516 Expert Mod 8TB
By derivation I mean something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT 1 + 1 AS derivedField
  2. FROM someTable
  3. WHERE 1 + 1 = 2
Instead of:
Expand|Select|Wrap|Line Numbers
  1. SELECT 1 + 1 AS derivedField
  2. FROM someTable
  3. WHERE derivedField = 2
Feb 15 '12 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless...
by: Sherman H. | last post by:
How to set the blank cell to 0 when a crosstab query is created? Thanks.
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These...
by: tizmagik | last post by:
I am having a lot of difficulty generating a CrossTab Query based report. I have looked online for several tutorials and whatnot but I have not...
by: bruce24444 | last post by:
What I have is a database which tracks assigned files to a certain people which is generated by a form and then recorded into a table. Table are as...
by: bruce24444 | last post by:
First of all I'm new to the forum and am working on my first database. So far I think I've done not too bad but have hit a stumbling block for which...
by: m.wanstall | last post by:
I have a crosstab query that compiles data for Months of the year. I have a stacked select query on top of that crosstab query that uses the latest...
by: keyur1719 | last post by:
Hi, I have a crosstab query which is based on a simple select query. Here is how the query works.. The base query gets it date from employee...
by: Alagas | last post by:
Hi, I'm new to access 2003 & would like to know how to create a report that shows the grand total of counts, details are as the following: 1) I've a...
by: gattkisson | last post by:
I am using Access 2002. I have created a query that has a crosstab query linked to it. The query runs fine initially. but I went back to add criteria...
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.