Duplicate Query problem

[MadCatmk2]

Hi all

Having a real problem with an access query.

I have a table which stores data on contacts made to patients.

I want to get a list of patients that have duplicate contactsdetails. The problem with this is that there are a couple of fields which will be different. I want to get all the records with certain fields matching. Already i feel like i'm not making sense:

example:

pat_No date_of_visit time etc....
TW10 1/1/2003 13:00
WS12 1/1/2003 13:00
WS12 12/1/2003 14:00


in the data above i would like to return the top two records as everything matches apart from the patient number.

There are more fields in the table i am using but if someone could point me in the right direction as to how to go about this.

Thanks in advance (and apologies for this maybe not making as much sense as i'd have liked)

Thanks

 

[PHV]

Something like this ?
SELECT * FROM yourTableName A INNER JOIN
(SELECT date_of_visit, [time], Count(*) As cnt FROM yourTableName GROUP BY date_of_visit, [time] HAVING Count(*)>1) B
ON (A.date_of_visit=B.date_of_visit) AND (A.time=B.time)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MadCatmk2]

Thanks for the quick reply.

I've given that idea a go but i am getting a syntax error in the FORM clause though. Any ideas why this may be?

Thanks

 

[lespaul]

What's the actual SQL you are trying?

What version of Access are you using?



Leslie

 

[MadCatmk2]

I'm using access 97.

The SQL statement i have now is:

SELECT * FROM CONTACTS A INNER JOIN
(SELECT DATE_OF_VISIT, TIME_OF_VISIT, count(*) As CNT FROM CONTACTS GROUP BY DATE_OF_VISIT, TIME_OF_VISIT HAVING count(*) > 1) B ON (A.DATE_OF_VISIT=B.DATE_OF_VISIT) AND (A.TIME_OF_VISIT=B.TIME_OF_VISIT);

Maybe i've missed something but i'm working on the basis of the code suggested by PHV.

Thanks

 

[PHV]

In ac97 you have to play with saved queries.
Create a saved query, say qryDupVisits with this sql code:
SELECT DATE_OF_VISIT, TIME_OF_VISIT, count(*) As CNT FROM CONTACTS GROUP BY DATE_OF_VISIT, TIME_OF_VISIT HAVING count(*) > 1;
And now you may try this:
SELECT * FROM CONTACTS A INNER JOIN qryDupVisits B
ON (A.DATE_OF_VISIT=B.DATE_OF_VISIT) AND (A.TIME_OF_VISIT=B.TIME_OF_VISIT);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MadCatmk2]

I'll give that a go and get back to you.

Thanks for that.

 

[MadCatmk2]

hiya

PHV: that suggestion of the two queries worked great. Its returning the records i need it to. However..... I would like to run these queries from VB now on the database. Using recordsets in ADO i don't think i can reference another query within an SQL statement, is there a way round this at all?

thanks

 

[MadCatmk2]

Hi again

PHV, just used your first suggestion query and this seemed to work from VB and returned the correct records.

Thank you so much for your help.

Cheers