Duplicate query appeared to be more complicated... 1

[estersita]

Hi
I have beaten my head to resolve this duplicate problem that appeared to be more complicated than I asked before.

I used the query below and it does eleiminate earlier DATE OF QUESTION record (which is my purpose) but unfortunately in case there are 3 records with 2 of them equal with DATE OF QUESTION and two records have EQUAL DATE OF QUESTION are older than the 3rd one…then sometimes this query choses to leave the record with Date OF ACTION which is null (while I need otherwise)


member id DATE of Question Date of Action
11 10/30/2003
11 10/14/2003
11 10/30/2003 11/31/2003
22 9/11/2004
22 8/2/2004
22 9/11/2004 9/11/2004
33 7/1/2004
33 7/1/2004 7/1/2004
44 5/1/2003
44 5/1/2003 5/1/2003
44 5/1/2003 5/1/2003


I need the following output:
member id DATE of Question Date of Action
11 10/30/2003 11/31/2003
22 9/11/2004 9/11/2004
33 7/1/2004 7/1/2004
44 5/1/2003 5/1/2003

Initial query was the following:

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION]
FROM makepart1
WHERE (((MAKEPART1.[Date of Question])<(select max( [Date of Question] ) from MAKEPART2 where Makepart2.[MEMBER ID]=MAKEPART1.[MEMBER ID])));

Is there any easy way to adjust the query above in order it eliminate recent DATE OF QUESTION and if we have 2 of them equal then the subject for taking away is the one which DATE OF ACTION IS NUL (if any)? Otherwise any record taken away is good…

I am exhausted with this query?

Thank you very much for your help and consideration…

 

[PHV]

Why not simply this ?
DELETE FROM Makepart1
WHERE [Date of Question]<(SELECT Max([Date of Question]) FROM MAKEPART2 WHERE Makepart2.[MEMBER ID]=Makepart1.[MEMBER ID])
OR [Date of Action] Is Null;

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

 

[estersita]

But in such a case this query take away those ones who has
the following scenario:


member id DATE of Question Date of Action
11 11/25/2003
11 11/25/2003 11/25/2003
11 8/28/2003 10/21/2002

I takes away the last record which is good but it also took two above and I do not have this id in the list anymore...
:-(

 

[PHV]

This one too ?
11 11/25/2003 11/25/2003

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

 

[estersita]

Yes, it dissappears.

 

[PHV]

Can you please post the EXACT SQL code and ALL the records with Member ID = 11 in BOTH tables ?

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

 

[estersita]

Your post made me think of it and to create a sample table in which query does not take it away . Now I am struggling with my real big table.

I checked all settings for real and sample tables (iNCLUDING INDEX SETTING) which are the same now but the same query work differently! Just a magic!
Pleeese let me know if you have ANY idea why it could happen!

 

[PHV]

Are the dates field really DateTime fields (as opposed to text fields) ?

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

 

[estersita]

Yes, they are Date/Time and I made sure they have short format.

The only thing is...I inherited the initial table and it looks like it was created in Access 2000 while I work in Access 2002 xp. Could it affect?

 

[PHV]

and I made sure they have short format
Shouldn't matter as DateTime are numbers (Double)
BTW they have perhaps Time values !
Try this:
DELETE FROM Makepart1
WHERE Int([Date of Question])<(SELECT Max(Int([Date of Question])) FROM MAKEPART2 WHERE Makepart2.[MEMBER ID]=Makepart1.[MEMBER ID])
OR [Date of Action] Is Null;

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

 

[estersita]

This query WITH INT() does work! I got just “correct” duplicates (those ones with the same MEMBER ID and same DATE OF QUESTION values

The last step I need is to eliminate duplicates (do not care about criteria anymore)

I assume I should create a query NODUPL with one field {MEMBER ID] and I did it using the following query:

Select DISTINCT [MEMBER ID]
From makepart1;

In such a way I can get what I need but Just one field. However I need all fields.

I made relation between table MAKEPART1 and table NODUPL:

Select makepart1.*
From makepart1,NODUPL
Where makepart1.[MEMBER ID]=nodupl.[MEMBER ID];

…But it give me all records from makepart1…
What I am doing wrong?

Thank you for all your help

Estersita

 

[PHV]

However I need all fields
Are they all the same when duplicated ?
If yes, then simply do this:
SELECT DISTINCT * FROM makepart1;
If not and you don't care which value to choose for the other fields:
SELECT [MEMBER ID], [Date of Question], Last([Date of Action]) AS [Date of Action], Last([another field]) AS [another field]
FROM makepart1
GROUP BY [MEMBER ID], [Date of Question]

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

 

[estersita]

I have 87 records in my makepart1 table.

These both queries return 86 records. I wonder...probably it happens because my table have not just duplicates but also includes singe records (which NEVER had duplicates).

That is why I tried the relation which I mentioned above...
But it didn't work anyway...What do you think?

 

[PHV]

What do you think?
About what ?
Is there duplicates in the 86 records ?
If yes, try this:
SELECT [MEMBER ID], Last([Date of Question]) AS [Date of Question], Last([Date of Action]) AS [Date of Action], Last([another field]) AS [another field]
FROM makepart1
GROUP BY [MEMBER ID]

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

 

[estersita]

It works! God bless You!