Can I build delete query based on select query or just on real table? 2

[estersita]

Hi ,

I am trying to delete duplicate records considering that their date of question fields are not the same and I want to remain later date record
I never happened to submit delete query in access before.
When I try to run my query I got the following message:

Specify the table containing the records you want to delete

Does it mean that I can delete records just from table rather than from query? Actually part1 and part2 are select queries.

This is my query and I wonder what I did wrong as I think I specify part1 (name of the table or initial query)

DELETE part1.[MEMBER FIRST NAME], part1.[MEMBER LAST NAME], part1.[MEMBER ID], part1.SEX, part1.[DATE OF BIRTH], part1.PRODUCT, part1.[PROGRAM START DATE], part1.[PROGRAM END DATE], part1.[ASKED QUESTION], part1.[RESPONSE TO QUESTION], part1.[DATE OF SHOT IF KNOWN], part1.[DATE OF QUESTION], part1.[ACCEPTS CALLS], part1.[DATE ACCEPT CALLS SET], part1.[WRONG PHONE NUMBER], part1.[DATE OF WRONG PHONE NUMBER], part1.SOURCE, part1.ENROLLDT, part1.ENROLLDT2, part1.[PHONE NUMBER], part1.StartdtSept30
FROM part1 INNER JOIN part2 ON part1.[MEMBER ID] = part2.[MEMBER ID]
WHERE ((DateDiff("d",[part1].[date of question],[part2].[date of question])>"0"));


Thank you in advance

Estersita

 

[SkipVought]

Hi,

Yes. a query is just a temporary thing that exists when you run it.

You have to delete from a Table.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[SkipVought]

I meant to say the RESULTS are temporary.

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[estersita]

SkipVought,
Thank you for the quick and helpful response. Now when I used make-table queriesbased on my select queries, I was able to run the following DELETE query:

DELETE makepart1.*
FROM makepart1,MAKEPART2
WHERE [MAKEPART1].[MEMBER ID]=[MAKEPART2].[MEMBER ID] AND
DateDiff("d",[MAKEPART1].[DATE OF QUESTION],[MAKEPART2].[DATE OF QUESTION])>0;

Unfortunately It didn't delete anything and the very 1st thing I found out was that after running make-table query SOME values under DATE OF QUESTION field of the output table changed the format while others remained as before:

for ex:
from 10/30/2003 (AS IT WAS INITIALLY)
to 10/30/2003 3:19:03 PM

I wonder if it happened because of the very initial source table was implemented in Access 2000 while I create my queries in Access XP? Is there a difference in date type in two versions of Access which caused it?

If so...what can be done in order to make my Delete query work properly?

Thank you very much for your help!

Estersita

 

[BNPMike]

You should be able to delete from a query built on another query. I suspect somewhere in amongst the underlying queries there is some duplication of field names and Jet can't be sure which one you are deleting.

Either you've got lots of tables or you are over-speciying the delete. If you have a table with Member Last Name and Member First Name, you don't need to delete both fields. Delete any one field and it's bye-bye everything for that record.

Tell us in simpler terms what you are doing and with how many tables.

 

[cheerio]

Generally Access does not like 2 tables in the from clause of a delete query as it gets confused about what table to delete from. That's why you have the message
Specify the table containing the records you want to delete

Try a sub-query like

DELETE * FROM makepart1 WHERE [MAKEPART1].[MEMBER ID] IN 
(SELECT [MAKEPART2].[MEMBER ID] FROM MAKEPART2 WHERE DateDiff("d",[MAKEPART1].[DATE OF QUESTION],[MAKEPART2].[DATE OF QUESTION])>0);

 

[estersita]

My purpose is to eliminate duplicate records using DATE of QUESTION prior criteria

For ex, I have an only select query result like the following:

member id DATE of Question other fields
11 10/30/2003
11 10/14/2003
22 9/11/2004
22 8/2/2004
33 7/1/2004
33 7/1/2004

I need the following output:
member id DATE of Question other fields
11 10/30/2003
22 9/11/2004
33 7/1/2004
33 7/1/2004

I do not want to eliminate duplicate for member id=33 , however because the next step (next query) will be to eliminate duplicate records based on another (secondary) date field value criteria

p.s Probably I do not need 2 tables . I just used makepart2 as an exact copy of makepart1 ...as I do not see another way to do it...

Thank you!

Estersita

 

[BNPMike]

I think I see what you are doing. You are trying to eliminate all records except the latest in time. An alternative way is to use a sub-select. You want to delete all the records that are not in the query which selects the maximum date for each member id (plus the other key you have).

Generate a query that selects the maximum dates and then cut and paste that into a second query that selects all records. Then turn that into a delet query. No doubt someone who knows SQL (unlike me) will volunteer the code shortly.

 

[cheerio]

DELETE MAKEPART.id, MAKEPART.[Date of Question]
FROM MAKEPART
WHERE (((MAKEPART.[Date of Question])<(select max( [Date of Question] ) from MAKEPART M2 where M2.ID=MAKEPART.ID)));

There is need for only one table which I have assumed is MAKEPART but the query needs to look back into the table and see if there is a greater date.

As drafted this query will leave the last pair because they have the same date and same ID.

 

[estersita]

Thank to all of you!!

I tried the very last query which cheerio provided me with and I built it on make-table queries:

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])));

It works!!!

However it looks like it deletes all duplicates INCLUDING one of the records below. In other words, it deletes duplicate records even in case when DATE OF QUESTION values in both record are equal

33 7/1/2004
33 7/1/2004

This is the next part of my problem :

because after running make-table query SOME values under DATE OF QUESTION field of the output table changed the format while others remained as before:

for ex:
from 7/1/2004 (AS IT WAS INITIALLY)
to 7/1/2004 3:19:03 PM

So it looks like system consider the 1st record as earlier one and deletes it which would be reasonable...but..

However initially both records in the original table looked the SAME way (without hours and minutes and sec). I just assume that it happened because original table was implementaed in Access 2000 while all other staff including make-table queries were done in ACCESS XP. How can it be overcome?

Thank you very much for all your help!

 

[PHV]

WHERE CLng(MAKEPART1.[Date of Question])<(SELECT Max(CLng([Date of Question])) FROM MAKEPART2 WHERE MAKEPART2.[MEMBER ID]=MAKEPART1.[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

 

[cheerio]

I tested my query using your data and building a table with access 2000 and it did not delete either of the duplicates on ID33.

However, if you have a datetime field with a value in it of 7/1/2004 3:19:03 PM

but formatted as mm/dd/yyyy then you are not going to see the time.

BUT if you then copy the table using a maketable query it will not copy the column formatting and the time will suddenly become visible in the copied table.

In effect the change suggested by PHV is designed to cause the query to disregard the time of day component.

If the original data for ID 33 had one record with a datetime value of exactly 7/1/2004 and the other with a value of 7/1/2004 3:19:03 PM then my query should have deleted the first (as 7/1/2004 is before 7/1/2004 3:19:03 PM .)

The key point about my delete query is that you do not need Make Table queries to do the deletion (although you might want to copy the table as a backup while testing).

 

[estersita]

I tried to run the query below:

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION]
FROM makepart1
WHERE CLng(MAKEPART1.[Date of Question])<(SELECT Max(CLng([makepart2].[Date of Question])) FROM MAKEPART2 WHERE MAKEPART2.[MEMBER ID]=MAKEPART1.[MEMBER ID]);

However When I run Access generates the following message:

"Invalid use of Null" ...without any Help option

Does it mean that Function CLng() does not allow name of field [Date of Question]...containing NULL??

 

[PHV]

What do you want to do with the records having null date ?

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

 

[estersita]

Those records are not a subject for deleting. I want them to remain in the file...

 

[PHV]

WHERE MAKEPART1.[Date of Question] Is Not Null AND
CLng(Nz(MAKEPART1.[Date of Question],0))<(SELECT Max(CLng(Nz([makepart2].[Date of Question],0))) FROM MAKEPART2 WHERE MAKEPART2.[MEMBER ID]=MAKEPART1.[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]

Thank you so much! Query does works in a perfect way for this task!

Now I assume I can go to the next step: next criteria for eliminating a duplicate record WITHIN those duplicates which were remained on purpose.

1.For those who has equal value of DATE OF QUESTION I should delete null duplicate record under DATE OF ACTION .

2.For those ones who have values equal under BOTH headings I should delete anyone anyway (for ex, for id 44 I have to delete no matter 1st or 2nd record )

Member id date of question date of action
88 3/30/2003 3/30/2003
88 3/30/2003
66 1/11/2004
66 1/11/2004 1/11/2004
33 2/1/2004 2/1/2004
33 2/1/2004
44 4/2/2004 4/2/2004
44 4/2/2004 4/2/2004

The output should be:
Member id date of question date of action
88 3/30/2003 3/30/2003
66 1/11/2004 1/11/2004
33 2/1/2004 2/1/2004
44 4/2/2004 4/2/2004


I assumed I just should add next criteria at the end of your query (AND MAKEPART1.[Date of ACTION] Is Null) to do the 1st part as I did below

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION], makepart1.[DATE OF ACTION]
FROM makepart1
WHERE MAKEPART1.[Date of Question] Is Not Null AND
CLng(Nz(MAKEPART1.[Date of Question],0))<(SELECT Max(CLng(Nz([makepart2].[Date of Question],0))) FROM MAKEPART2 WHERE MAKEPART2.[MEMBER ID]=MAKEPART1.[MEMBER ID]) AND MAKEPART1.[Date of ACTION] Is Null;

But system give me a message saying :

Enter Parameter value
Makepart1.DATE OF ACTION which means I did it wrong!

How can I do it?