query only those records that have multiple entries

[JulieAsking]

I have an Access97 database listing surgical operations for patients. Most patients will have only one operation but a few will have multiple operations. I want to know how to set up a query to ferret out those patients who have had more than one operation. There are two tables, one for patient details and one for operation details. The fields that would come into play here are PatientID, LastName, FirstName, from Patient Table and Procedure, BodyArea, Date from Operation Table.

How can I have Access look for ONLY those patients who have had more than one procedure performed and show me what they were. In other words, have Access search the subforms for each patient.

I know how to run a query showing the names of patients who have had more than one operation and the NUMBER of operations they had but I want to see the TYPE of operation as well.

Thanks for any help - Julie

 

[JulieAsking]

Sorry, I meant to mention re my question above, that when I run a query to show me the patients who have had multiple operations, Access gives me names and the total number of operations they had and, if I had the Procedure field to the query, the FIRSTLISTED type of operation. What I want is for Access to show me not just the first operation (ie. the first entry) but subsequent operations as well. It is not counting beyond the first line of the entries in the subforms.

Julie

 

[Pat Hartman]

You need to do this in two parts. First you need to find the patients that have had multiple operations. Then you need to obtain the operation detail.

query1:
Select PatientID, Count(*)
From YourPatientDetail
Where Count(*) > 1
Group By PatientID;

query2:
Select * from YourPatientDetail Inner Join query1 on YourPatientDetail.PatientID = query1.PatientID;

 

[JulieAsking]

Pat, Thank you for your reply. When trying:

Select PtID, Count(*)
From TPtDetails
Where Count(*) > 1
Group By PtID;

I get a message saying "Can't have aggregate function in WHERE clause [Count(*) > 1].

I'm a newbie and am not sure what this message means.

Julie

 

[WayneRyan]

Julie,

Not real strong on queries, but try:

Select PtID, Count(*)
From TPtDetails
Group By PtID
Having Count(*) > 1;

Wayne

 

[angshubh]

Julie,

Why dont you consider a "Find Duplicate Query Wizard" to get the required result automatically? This wizard will take you thru the process where u only have to spcify the field for which u r seeking multiple entries. This shd be the easiest way.

Actually, the solns provided also give the same result, but the wizard is always is easier, since it is a wizard!

Angshu

 

[angshubh]

On a second thought, julie, I came out with a better soln. Why dont u have 2 tables like patient_master and patient_operation? Patient master will keep all the patients bio-data (!) and in patient_operation table u have patient id and the operation details. now if a paqtient has more than 1 operation, his id will be present as many times in patient_operation table. Then u can run a "find duplicate query wizard" to get the multiple opertaed patients list.

This soln requires some reworking and data entry initially, but when set up, will work more efficiently in terms of management a storage space required. give a thought

Angshu
India

 

[JulieAsking]

ANGSHU - Many thanks. What a simply wonderful solution you provided me with. My db is set up just the way you suggested in your second post. I ran the wizard to find the duplicates in the Patient ID field of the Operations Table and once this was up I simply added the Patient Details table and added the names of the patients from that. I also found that I could then tell Access to search for >2 or >3 operations etc by merely altering the number in the Select sentence the wizard set up - being:

In (SELECT [PtID] FROM [TOpDetails] As Tmp GROUP BY [PtID] HAVING Count(*)>2 )

Simple solutions are such a joy! I'm grateful for your interest.

Thanks also to Pat and Wayne for your responses - I'm just not as evolved as you two when it comes to Access

Julie

 

[angshubh]

My Pleasure!