You have written a subquery that can return more than one field without using EXISTS reserved word

[fluffyvampirekitten]

I'm getting this error.



any ideas where goes wrong?
Thanks in advance

SELECT DuplicatesUnionQ.PATIENT_ID, DuplicatesUnionQ.PATIENT_NAME, DuplicatesUnionQ.GENDER, DuplicatesUnionQ.DOB, DuplicatesUnionQ.C_SOURCE, DuplicatesUnionQ.CATEGORY_TYPE
FROM DuplicatesUnionQ
WHERE ((DuplicatesUnionQ.PATIENT_ID) IN (SELECT [DuplicatesUnionQ.PATIENT_ID], [DuplicatesUnionQ.PATIENT_NAME] FROM [DuplicatesUnionQ] As Tmp 
GROUP BY DuplicatesUnionQ.PATIENT_ID, DuplicatesUnionQ.PATIENT_NAME
HAVING count(DuplicatesUnionQ.PATIENT_ID) <2 ))
order by DuplicatesUnionQ.PATIENT_ID, DuplicatesUnionQ.PATIENT_NAME, DuplicatesUnionQ.GENDER, DuplicatesUnionQ.DOB, DuplicatesUnionQ.C_SOURCE, DuplicatesUnionQ.CATEGORY_TYPE;

[JamesDeckert]

I'm not a master at doing this kind of thing, so take my suggest or not.
On your WHERE you are matching the ID from DuplicatesUnionQ to the ID returned from a different query. Why are you returning the Patient_name in the inner query?
This seems to be what the error is saying also.

[orange]

In addition to James' comment, What exactly are you trying to do in plain English?

[June7]

Agree with James - PATIENT_NAME Is not needed in the inner query at all and is causing the error. Remove all references to PATIENT_NAME from the inner query.

[fluffyvampirekitten]

Agree with James - PATIENT_NAME Is not needed in the inner query at all and is causing the error. Remove all references to PATIENT_NAME from the inner query.

I have this table : DuplicatesUnionQ which consists of Duplicates.

Actually, I wanna retrieve the person data ( id , name , gender , dob , source , category_type)
Where names is not match .

[fluffyvampirekitten]

In addition to James' comment, What exactly are you trying to do in plain English?

I have this table : DuplicatesUnionQ which consists of Duplicates.

Actually, I wanna retrieve the person data ( id , name , gender , dob , source , category_type)
Where names is not match .

[fluffyvampirekitten]

Current Code: Only display Patient name and ID
Where names is not the same.

Example :
ID | Name
123 | Teddy
123 | Ted

SELECT DuplicatesUnionQ.PATIENT_ID, DuplicatesUnionQ.PATIENT_NAME
FROM DuplicatesUnionQ
GROUP BY DuplicatesUnionQ.PATIENT_ID, DuplicatesUnionQ.PATIENT_NAME
HAVING count(DuplicatesUnionQ.PATIENT_NAME) < 2;

However I wanna show this data
ID | Name | Gender | DOB | Category_Type
123 | Teddy | F | 12-2-2015 | Cross
123 | Ted | F | 12-2-2015 | Intra

[orange]

What exactly are you trying to do?
These are NOT duplicates,
123 | Teddy
123 | Ted

What is the ID field?
What is the PK of your table?

[fluffyvampirekitten]

What exactly are you trying to do?
These are NOT duplicates,
123 | Teddy
123 | Ted

What is the ID field?
What is the PK of your table?

Duplicate IDs. Different Name but Same ID
I dont have a Primary Key in my table .
ID field ? Data Type ? If its data type , its short text .


This is the final result i wanna achieve .

ID | Name | Gender | DOB | Category_Type
123 | Teddy | F | 12-2-2015 | Cross
123 | Ted | F | 12-2-2015 | Intra

I wanna Create a filter whereby ,
Display all those patient data with the SAME ID but Different Name .
Eg.

I wanna check if the Name has been updated in the "INTRA" system.
If it is not updated , it will show this:

ID | Name | Gender | DOB | Category_Type
123 | Teddy | F | 12-2-2015 | Cross
123 | Ted | F | 12-2-2015 | Intra

[fluffyvampirekitten]

What exactly are you trying to do?
These are NOT duplicates,
123 | Teddy
123 | Ted

What is the ID field?
What is the PK of your table?

Duplicate IDs. Different Name but Same ID
I dont have a Primary Key in my table .
ID field ? Data Type ? If its data type , its short text .


This is the final result i wanna achieve .

ID | Name | Gender | DOB | Category_Type
123 | Teddy | F | 12-2-2015 | Cross
123 | Ted | F | 12-2-2015 | Intra

I wanna Create a filter whereby ,
Display all those patient data with the SAME ID but Different Name .
Eg.

I wanna check if the Name has been updated in the "INTRA" system.
If it is not updated , it will show this:

ID | Name | Gender | DOB | Category_Type
123 | Teddy | F | 12-2-2015 | Cross
123 | Ted | F | 12-2-2015 | Intra

[orange]

Every table should have a primary key whose job is to uniquely identify each record in the table

[fluffyvampirekitten]

Every table should have a primary key whose job is to uniquely identify each record in the table

So without a PK , I can't do that "filter" function?

[orange]

I gave a response earlier using tblPeeps. I'm resending but using your "num" field (not a PK)

SELECT tblPeeps.pname, Count(tblPeeps.num) AS CountOfnum
FROM tblPeeps
GROUP BY tblPeeps.pname
HAVING (((Count(tblPeeps.num))<2));

This was using some of your sample data and found records where the NUM was not unique. That is, for a given NUM there could be different pNames.