Layout of query result

[KateB]

I have a database that has expanded over time so probably isn't set up
exactly as it should be! However, I will continue and hope someone can help
with this.

I have a main form that is used to collect patient information - name, DOB,
doctor, infection, etc. On this form is a sub-form that records resistance
to antibiotics. Each patient could have an antibiotic result for anything
from one to eleven different drugs. Each result is either Resistant or
Sensitive. Currently the query returns:
PATIENT ANTI-B RESULT
patient1 abx1 resistant
patient1 abx2 resistant
patient1 abx3 sensitive
patient1 abx4 sensitive
patient2 abx1 resistant
patient2 abx2 sensitive etc.

What I need is:
PATIENT ABX1 ABX2 ABX3 ABX4 etc.
Patient1 resist resist sens sens
Patient2 resist sens

Is this possible? Currently I have to export to excel and then "turn it
around" using look-ups and manual checking. Any suggestions?

Kate

 

[John Spencer]

If you need that result for a report or for an query that does not need
to be updated then you should be using a cross-tab query.

The SQL statement would look something like the following. But you can
build a cross-tab query in design view.

TRANSFORM First(Result) as TheResult
SELECT Patient
FROM SomeTable
GROUP BY Patient
PIVOT [Anti-B]

If you want to be able to use this on a form and update it, then you
should use the vertical orientation you now have. You can use VBA code
and work table that has a field for patient id and one field for each
anti-b. The code would have to take the values from the current table
and populate the data into the work table. After the record was
updated/changed the code would have to take the data from the work table
and populate/update the current table.

This is NOT a good solution as you will need to modify the table each
time a new Anti-B is added to the list or an existing Anti-B is deleted
or changed.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Dale_Fye via AccessMonster.com]

Kate,

I would not change the table structure. Actually, this is exactly what
crosstab queries are designed to do. However, you might have a problem if
abx1 for patient1 is different than abx1 for patient2.

To create a crosstab query, you will have to drag the patient, antibiotic,
and result fields into your query, then select the crosstab query option from
the queries menu. Finally, you will have to indicate how you want the data
displayed. In the "Crosstab" row of the query grid, select "Row Heading"
under the Patients column, "Column Heading" under the Anti-biotic column, and
Value under the Result column. Then, change the GroupBy in the Total row of
the Result column to First.

HTH

 

[KateB]

Thanks Dale, this was just what I needed! The data is recorded by abx name
rather than 1, 2, 3, so that part doesn't matter (I just simplified it for
the question). I have never used 'First' in the total column before - the
downside to being self-taught I suppose.

Kate

 

[Dale_Fye via AccessMonster.com]

Kate,

The only time I use First is when I know that there can only be one record
that matches the combination of two other values (in your case Patient and
Anti-biotic).

First will select the first value that Access finds that matches the row and
column header values, but it might not be the value you expect if you have
more than one record with that particular combination of field values. The
way I know that there can only be one of these values is that I generally
create a unique index on those fields.

You could also use the Last, Min, or Max in your situation, where you know
you only have one value.

The down side of using a cross tab query is that if you have a large number
of anti-biotics in your Antibiotics table, you will get a column for each one,
and will have a lot of empty fields.

HTH
Dale

Thanks Dale, this was just what I needed! The data is recorded by abx name
rather than 1, 2, 3, so that part doesn't matter (I just simplified it for
the question). I have never used 'First' in the total column before - the
downside to being self-taught I suppose.

Kate

Kate,

[quoted text clipped - 38 lines]