Layout of query result

  • Thread starter Thread starter KateB
  • Start date Start date
K

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
 
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
'====================================================
 
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
 
Back
Top