Help with query

  • Thread starter Thread starter Nelson
  • Start date Start date
N

Nelson

I have a table with the following fields:
complaintid (key)
patientid
md
pt
ap
psyc
other1
other2

There can be up to three records with the same patientid. I want to create
query that will combine the information of the three records to one line per
patientid.

Example:
Records:
complaintid patientid md pt ap psyc
other1 other2
1 31 28 63
2 31 17
24
3 31
96
Output:
31 28 17 63
24 96


I am guessing a union query with a group by would do it but I have not tried
working with a union query before. (Access 2000) Any help will be
appreciated. Thanks!
 
It's very difficult to view your table values. I think you can use a totals
query but I'm not sure. It looks like you might have a normalization issue.
 
Dwane, thanks for the response. I rewrote the example to make it clearer:

Record1:
complaintid (key) = 1
patientid = 31
md = 28
pt = null
ap = 63
psyc = null
other1 = null
other2 = null

Record2:
complaintid (key) = 2
patientid = 31
md = null
pt = 17
ap = null
psyc = null
other1 = 24
other2 = null

Record3:
complaintid (key) = 3
patientid = 31
md = null
pt = null
ap = null
psyc = null
other1 = null
other2 = 96

Query Output:
patientid = 31
md = 28
pt = 17
ap = 63
psyc = null
other1 = 24
other2 = 96

I tried playing with a totals query but was stumped with how to pull the md,
pt, etc values into the total output. Those values could be in any of the
complaint records. In one complaint, a patient may be seeing an md & a pt.
In another complaint he may be seeing an ap & psyc. Another patient may
have a defferent group of people he is seeing in each complaint.
 
Your samples never have a value for the same field in more than one record
other than the ComplaintID and the PatientID. Is this always the case? What
would you like for a result if two records each had a value for MD? If you
want to total them, use
SELECT PatientID, Sum(MD) as SumMD, Sum(PT) as SumPT, Sum(AP) as SumAP,
Sum(Psyc) as SumPsyc, Sum(Other1) as SumOth1, Sum(Other2) As SumOth2
FROM tblPatientVisits
Group By PatientID;

Again, you have committed spreadsheet by using values (MD, PT, AP,...) as
field names. What happens when you want to include MT (massage therapist)
and VD (voodoo doctor)? Are you going to add more fields, controls, ...?
 
Back
Top