How to eliminate Partly duplicated query? SOS!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I have beaten my head trying to resolve this problem of deleting partly
duplicate records whose Member IDs are equal while DATE OF QUESTION are not.

member id DATE of Question Date of Action

*44 4/1/2003 4/30/2003
*44 4/2/2003 4/4/2003
*44 5/1/2003
44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003
33 3/4/2003

Earlier DATE OF QUESTION Should be deleted. However when the equal DATE
OF QUESTION remain as a subject to decide which one to take away ...then
DATA OF ACTION which is empty should be the choice

In addition , non- duplicate members (as 33) are untouchable

So I marked subject for deleing with asterisks.

The following query does the part of work

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 deletes earlier dates of DATE of QUESTION but unfortunately when there
are the SAME ID and SAME [Date of Question] …then I do not know how to delete
just the one whose [DATE OF ACTION] is null (if any). Also non-duplicates are
untouchable



When equal members (I mean equal id, DATE OF QUESTION ] and DATE OF ACTION
not null left like the followings:

member id DATE of Question Date of Action

44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003

Then I do not care which one to delete and the following query can be used
and it works.

SELECT makepart1.[MEMBER ID], Last(makepart1.[MEMBER FIRST NAME]) AS [Member
First Name_], Last(makepart1.[MEMBER LAST NAME]) AS [Member Last Name_},
makepart1.[other field] as Last(makepart1.[other field_])
FROM makepart1
GROUP BY makepart1.[MEMBER ID];

Is there any easy way to adjust my query?

Can it be done without VBA loop which I am not familiar with? I desperately
need it…
 
Limit the query to to those that only have duplicate member ID's

You may have to do this in multiple queries.

First delete those records where the Date of Action is Null and there are more
than one record for the the same Member ID.

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION]
FROM makepart1
WHERE MemberID IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)
AND [Date of Action] is Null

Now, delete the remaining duplicate records where

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]) AND
[Member Id] IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)


Hi!

I have beaten my head trying to resolve this problem of deleting partly
duplicate records whose Member IDs are equal while DATE OF QUESTION are not.

member id DATE of Question Date of Action

*44 4/1/2003 4/30/2003
*44 4/2/2003 4/4/2003
*44 5/1/2003
44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003
33 3/4/2003

Earlier DATE OF QUESTION Should be deleted. However when the equal DATE
OF QUESTION remain as a subject to decide which one to take away ...then
DATA OF ACTION which is empty should be the choice

In addition , non- duplicate members (as 33) are untouchable

So I marked subject for deleing with asterisks.

The following query does the part of work

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 deletes earlier dates of DATE of QUESTION but unfortunately when there
are the SAME ID and SAME [Date of Question] â€|then I do not know how to delete
just the one whose [DATE OF ACTION] is null (if any). Also non-duplicates are
untouchable

When equal members (I mean equal id, DATE OF QUESTION ] and DATE OF ACTION
not null left like the followings:

member id DATE of Question Date of Action

44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003

Then I do not care which one to delete and the following query can be used
and it works.

SELECT makepart1.[MEMBER ID], Last(makepart1.[MEMBER FIRST NAME]) AS [Member
First Name_], Last(makepart1.[MEMBER LAST NAME]) AS [Member Last Name_},
makepart1.[other field] as Last(makepart1.[other field_])
FROM makepart1
GROUP BY makepart1.[MEMBER ID];

Is there any easy way to adjust my query?

Can it be done without VBA loop which I am not familiar with? I desperately
need itâ€|
Click to expand...
 
However what if the following scenario takes place:

Id Date of question Date of Action
44 10/30/2003 10/10/2003

44 12/13/2003

Then the 1st query will delete the 2nd record because [DATE OF Action] is
empty while DATE OF QUESTION is more recent than 10/30/2003.

My 1st condition is to keep recent DATE OF QUESTION . However if the same id
AND [DATE OF QUESTION] left then I need to delete the record whose [Date
of Action] is empty


John Spencer (MVP) said:
Limit the query to to those that only have duplicate member ID's

You may have to do this in multiple queries.

First delete those records where the Date of Action is Null and there are more
than one record for the the same Member ID.

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION]
FROM makepart1
WHERE MemberID IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)
AND [Date of Action] is Null

Now, delete the remaining duplicate records where

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]) AND
[Member Id] IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)


Hi!

I have beaten my head trying to resolve this problem of deleting partly
duplicate records whose Member IDs are equal while DATE OF QUESTION are not.

member id DATE of Question Date of Action

*44 4/1/2003 4/30/2003
*44 4/2/2003 4/4/2003
*44 5/1/2003
44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003
33 3/4/2003

Earlier DATE OF QUESTION Should be deleted. However when the equal DATE
OF QUESTION remain as a subject to decide which one to take away ...then
DATA OF ACTION which is empty should be the choice

In addition , non- duplicate members (as 33) are untouchable

So I marked subject for deleing with asterisks.

The following query does the part of work

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 deletes earlier dates of DATE of QUESTION but unfortunately when there
are the SAME ID and SAME [Date of Question] â€|then I do not know how to delete
just the one whose [DATE OF ACTION] is null (if any). Also non-duplicates are
untouchable

When equal members (I mean equal id, DATE OF QUESTION ] and DATE OF ACTION
not null left like the followings:

member id DATE of Question Date of Action

44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003

Then I do not care which one to delete and the following query can be used
and it works.

SELECT makepart1.[MEMBER ID], Last(makepart1.[MEMBER FIRST NAME]) AS [Member
First Name_], Last(makepart1.[MEMBER LAST NAME]) AS [Member Last Name_},
makepart1.[other field] as Last(makepart1.[other field_])
FROM makepart1
GROUP BY makepart1.[MEMBER ID];

Is there any easy way to adjust my query?

Can it be done without VBA loop which I am not familiar with? I desperately
need itâ€|
Click to expand...
Click to expand...
 
John,

I have the same type of issue where I about 20 people in a table that are
assigned to 2 groups.

Person_id,Name,Group

294986; Jones, Bob;Rehab
294986; Jones, Bob;Surgery

I used the first query you designed for Maurinnew as a select query to
identify the duplicates, but I want to delete the second entry and I know it
is a simple fix but it is escaping me. Could you give me a bit more
direction?

Mike



John Spencer (MVP) said:
Limit the query to to those that only have duplicate member ID's

You may have to do this in multiple queries.

First delete those records where the Date of Action is Null and there are more
than one record for the the same Member ID.

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION]
FROM makepart1
WHERE MemberID IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)
AND [Date of Action] is Null

Now, delete the remaining duplicate records where

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]) AND
[Member Id] IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)


Hi!

I have beaten my head trying to resolve this problem of deleting partly
duplicate records whose Member IDs are equal while DATE OF QUESTION are not.

member id DATE of Question Date of Action

*44 4/1/2003 4/30/2003
*44 4/2/2003 4/4/2003
*44 5/1/2003
44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003
33 3/4/2003

Earlier DATE OF QUESTION Should be deleted. However when the equal DATE
OF QUESTION remain as a subject to decide which one to take away ...then
DATA OF ACTION which is empty should be the choice

In addition , non- duplicate members (as 33) are untouchable

So I marked subject for deleing with asterisks.

The following query does the part of work

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 deletes earlier dates of DATE of QUESTION but unfortunately when there
are the SAME ID and SAME [Date of Question] â€|then I do not know how to delete
just the one whose [DATE OF ACTION] is null (if any). Also non-duplicates are
untouchable

When equal members (I mean equal id, DATE OF QUESTION ] and DATE OF ACTION
not null left like the followings:

member id DATE of Question Date of Action

44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003

Then I do not care which one to delete and the following query can be used
and it works.

SELECT makepart1.[MEMBER ID], Last(makepart1.[MEMBER FIRST NAME]) AS [Member
First Name_], Last(makepart1.[MEMBER LAST NAME]) AS [Member Last Name_},
makepart1.[other field] as Last(makepart1.[other field_])
FROM makepart1
GROUP BY makepart1.[MEMBER ID];

Is there any easy way to adjust my query?

Can it be done without VBA loop which I am not familiar with? I desperately
need itâ€|
Click to expand...
Click to expand...
 
The problem here is how does Access know which one is the second entry. Do you
have some data *** in the record *** that will tell you which is the first and
which is the second? A date time field, a reliable sequence number?

If not, then there is no way to know which record you want to keep and which you
want to delete.

Michael said:
John,

I have the same type of issue where I about 20 people in a table that are
assigned to 2 groups.

Person_id,Name,Group

294986; Jones, Bob;Rehab
294986; Jones, Bob;Surgery

I used the first query you designed for Maurinnew as a select query to
identify the duplicates, but I want to delete the second entry and I know it
is a simple fix but it is escaping me. Could you give me a bit more
direction?

Mike

John Spencer (MVP) said:
Limit the query to to those that only have duplicate member ID's

You may have to do this in multiple queries.

First delete those records where the Date of Action is Null and there are more
than one record for the the same Member ID.

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION]
FROM makepart1
WHERE MemberID IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)
AND [Date of Action] is Null

Now, delete the remaining duplicate records where

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]) AND
[Member Id] IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)


Hi!

I have beaten my head trying to resolve this problem of deleting partly
duplicate records whose Member IDs are equal while DATE OF QUESTION are not.

member id DATE of Question Date of Action

*44 4/1/2003 4/30/2003
*44 4/2/2003 4/4/2003
*44 5/1/2003
44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003
33 3/4/2003

Earlier DATE OF QUESTION Should be deleted. However when the equal DATE
OF QUESTION remain as a subject to decide which one to take away ...then
DATA OF ACTION which is empty should be the choice

In addition , non- duplicate members (as 33) are untouchable

So I marked subject for deleing with asterisks.

The following query does the part of work

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 deletes earlier dates of DATE of QUESTION but unfortunately when there
are the SAME ID and SAME [Date of Question] â€|then I do not know how to delete
just the one whose [DATE OF ACTION] is null (if any). Also non-duplicates are
untouchable

When equal members (I mean equal id, DATE OF QUESTION ] and DATE OF ACTION
not null left like the followings:

member id DATE of Question Date of Action

44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003

Then I do not care which one to delete and the following query can be used
and it works.

SELECT makepart1.[MEMBER ID], Last(makepart1.[MEMBER FIRST NAME]) AS [Member
First Name_], Last(makepart1.[MEMBER LAST NAME]) AS [Member Last Name_},
makepart1.[other field] as Last(makepart1.[other field_])
FROM makepart1
GROUP BY makepart1.[MEMBER ID];

Is there any easy way to adjust my query?

Can it be done without VBA loop which I am not familiar with? I desperately
need itâ€|
Click to expand...
Click to expand...
Click to expand...
 
Yes and it would delete two records if you had two records for 44 on 12/13/2003
and Date of action = to null. I have to admit that I am stumped at this point.
Or if you have two records where ID, Date of Question, and Date of Action are
all the same and they are the record you want to keep, then you are still going
to have two records left.
However what if the following scenario takes place:

Id Date of question Date of Action
44 10/30/2003 10/10/2003

44 12/13/2003

Then the 1st query will delete the 2nd record because [DATE OF Action] is
empty while DATE OF QUESTION is more recent than 10/30/2003.

My 1st condition is to keep recent DATE OF QUESTION . However if the same id
AND [DATE OF QUESTION] left then I need to delete the record whose [Date
of Action] is empty

John Spencer (MVP) said:
Limit the query to to those that only have duplicate member ID's

You may have to do this in multiple queries.

First delete those records where the Date of Action is Null and there are more
than one record for the the same Member ID.

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION]
FROM makepart1
WHERE MemberID IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)
AND [Date of Action] is Null

Now, delete the remaining duplicate records where

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]) AND
[Member Id] IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)


Hi!

I have beaten my head trying to resolve this problem of deleting partly
duplicate records whose Member IDs are equal while DATE OF QUESTION are not.

member id DATE of Question Date of Action

*44 4/1/2003 4/30/2003
*44 4/2/2003 4/4/2003
*44 5/1/2003
44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003
33 3/4/2003

Earlier DATE OF QUESTION Should be deleted. However when the equal DATE
OF QUESTION remain as a subject to decide which one to take away ...then
DATA OF ACTION which is empty should be the choice

In addition , non- duplicate members (as 33) are untouchable

So I marked subject for deleing with asterisks.

The following query does the part of work

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 deletes earlier dates of DATE of QUESTION but unfortunately when there
are the SAME ID and SAME [Date of Question] â€|then I do not know how to delete
just the one whose [DATE OF ACTION] is null (if any). Also non-duplicates are
untouchable

When equal members (I mean equal id, DATE OF QUESTION ] and DATE OF ACTION
not null left like the followings:

member id DATE of Question Date of Action

44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003

Then I do not care which one to delete and the following query can be used
and it works.

SELECT makepart1.[MEMBER ID], Last(makepart1.[MEMBER FIRST NAME]) AS [Member
First Name_], Last(makepart1.[MEMBER LAST NAME]) AS [Member Last Name_},
makepart1.[other field] as Last(makepart1.[other field_])
FROM makepart1
GROUP BY makepart1.[MEMBER ID];

Is there any easy way to adjust my query?

Can it be done without VBA loop which I am not familiar with? I desperately
need itâ€|
Click to expand...
Click to expand...
Click to expand...
 
Could I create a loop that would number each record where the Person_id is
the same 1, 2, 3 etc.? Basically have it start at line 1 and call it 1 then
go to line 2 have it compare the id from line 1 if it is the same as 1 then
call it 2. then move to line 3 and compare the person_id to the above line
and if it does not match ten go back to 1 and start the process over.

Then I could write a query to pull all the records with a 1.

I am not entirely sure how to pull something like this off ina query, is it
possible and could you give me some pointers?

Mike

John Spencer (MVP) said:
The problem here is how does Access know which one is the second entry. Do you
have some data *** in the record *** that will tell you which is the first and
which is the second? A date time field, a reliable sequence number?

If not, then there is no way to know which record you want to keep and which you
want to delete.

Michael said:
John,

I have the same type of issue where I about 20 people in a table that are
assigned to 2 groups.

Person_id,Name,Group

294986; Jones, Bob;Rehab
294986; Jones, Bob;Surgery

I used the first query you designed for Maurinnew as a select query to
identify the duplicates, but I want to delete the second entry and I know it
is a simple fix but it is escaping me. Could you give me a bit more
direction?

Mike

John Spencer (MVP) said:
Limit the query to to those that only have duplicate member ID's

You may have to do this in multiple queries.

First delete those records where the Date of Action is Null and there are more
than one record for the the same Member ID.

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION]
FROM makepart1
WHERE MemberID IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)
AND [Date of Action] is Null

Now, delete the remaining duplicate records where

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]) AND
[Member Id] IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)



Maurinnew wrote:

Hi!

I have beaten my head trying to resolve this problem of deleting partly
duplicate records whose Member IDs are equal while DATE OF QUESTION are not.

member id DATE of Question Date of Action

*44 4/1/2003 4/30/2003
*44 4/2/2003 4/4/2003
*44 5/1/2003
44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003
33 3/4/2003

Earlier DATE OF QUESTION Should be deleted. However when the equal DATE
OF QUESTION remain as a subject to decide which one to take away ...then
DATA OF ACTION which is empty should be the choice

In addition , non- duplicate members (as 33) are untouchable

So I marked subject for deleing with asterisks.

The following query does the part of work

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 deletes earlier dates of DATE of QUESTION but unfortunately when there
are the SAME ID and SAME [Date of Question] â€|then I do not know how to delete
just the one whose [DATE OF ACTION] is null (if any). Also non-duplicates are
untouchable

When equal members (I mean equal id, DATE OF QUESTION ] and DATE OF ACTION
not null left like the followings:

member id DATE of Question Date of Action

44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003

Then I do not care which one to delete and the following query can be used
and it works.

SELECT makepart1.[MEMBER ID], Last(makepart1.[MEMBER FIRST NAME]) AS [Member
First Name_], Last(makepart1.[MEMBER LAST NAME]) AS [Member Last Name_},
makepart1.[other field] as Last(makepart1.[other field_])
FROM makepart1
GROUP BY makepart1.[MEMBER ID];

Is there any easy way to adjust my query?

Can it be done without VBA loop which I am not familiar with? I desperately
need itâ€|
Click to expand...
Click to expand...
Click to expand...
 
Considering what you said to Mike...
Does it mean that I cannot do what I need (I mean my particular problem )
with Access?

Thanks,

Maurin

Michael N said:
Could I create a loop that would number each record where the Person_id is
the same 1, 2, 3 etc.? Basically have it start at line 1 and call it 1 then
go to line 2 have it compare the id from line 1 if it is the same as 1 then
call it 2. then move to line 3 and compare the person_id to the above line
and if it does not match ten go back to 1 and start the process over.

Then I could write a query to pull all the records with a 1.

I am not entirely sure how to pull something like this off ina query, is it
possible and could you give me some pointers?

Mike

John Spencer (MVP) said:
The problem here is how does Access know which one is the second entry. Do you
have some data *** in the record *** that will tell you which is the first and
which is the second? A date time field, a reliable sequence number?

If not, then there is no way to know which record you want to keep and which you
want to delete.

Michael said:
John,

I have the same type of issue where I about 20 people in a table that are
assigned to 2 groups.

Person_id,Name,Group

294986; Jones, Bob;Rehab
294986; Jones, Bob;Surgery

I used the first query you designed for Maurinnew as a select query to
identify the duplicates, but I want to delete the second entry and I know it
is a simple fix but it is escaping me. Could you give me a bit more
direction?

Mike

:

Limit the query to to those that only have duplicate member ID's

You may have to do this in multiple queries.

First delete those records where the Date of Action is Null and there are more
than one record for the the same Member ID.

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION]
FROM makepart1
WHERE MemberID IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)
AND [Date of Action] is Null

Now, delete the remaining duplicate records where

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]) AND
[Member Id] IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)



Maurinnew wrote:

Hi!

I have beaten my head trying to resolve this problem of deleting partly
duplicate records whose Member IDs are equal while DATE OF QUESTION are not.

member id DATE of Question Date of Action

*44 4/1/2003 4/30/2003
*44 4/2/2003 4/4/2003
*44 5/1/2003
44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003
33 3/4/2003

Earlier DATE OF QUESTION Should be deleted. However when the equal DATE
OF QUESTION remain as a subject to decide which one to take away ...then
DATA OF ACTION which is empty should be the choice

In addition , non- duplicate members (as 33) are untouchable

So I marked subject for deleing with asterisks.

The following query does the part of work

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 deletes earlier dates of DATE of QUESTION but unfortunately when there
are the SAME ID and SAME [Date of Question] â€|then I do not know how to delete
just the one whose [DATE OF ACTION] is null (if any). Also non-duplicates are
untouchable

When equal members (I mean equal id, DATE OF QUESTION ] and DATE OF ACTION
not null left like the followings:

member id DATE of Question Date of Action

44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003

Then I do not care which one to delete and the following query can be used
and it works.

SELECT makepart1.[MEMBER ID], Last(makepart1.[MEMBER FIRST NAME]) AS [Member
First Name_], Last(makepart1.[MEMBER LAST NAME]) AS [Member Last Name_},
makepart1.[other field] as Last(makepart1.[other field_])
FROM makepart1
GROUP BY makepart1.[MEMBER ID];

Is there any easy way to adjust my query?

Can it be done without VBA loop which I am not familiar with? I desperately
need itâ€|
Click to expand...
Click to expand...
Click to expand...
 
I don't think yours should be a problem. I was just using your post as I
have a similar problem it is is better etiqute to use an open discussion
rather than starting another one. I also used some of John's solution for
you to find the items I had duplicated.

Hope yours works out

Mike

maurinnew said:
Considering what you said to Mike...
Does it mean that I cannot do what I need (I mean my particular problem )
with Access?

Thanks,

Maurin

Michael N said:
Could I create a loop that would number each record where the Person_id is
the same 1, 2, 3 etc.? Basically have it start at line 1 and call it 1 then
go to line 2 have it compare the id from line 1 if it is the same as 1 then
call it 2. then move to line 3 and compare the person_id to the above line
and if it does not match ten go back to 1 and start the process over.

Then I could write a query to pull all the records with a 1.

I am not entirely sure how to pull something like this off ina query, is it
possible and could you give me some pointers?

Mike

John Spencer (MVP) said:
The problem here is how does Access know which one is the second entry. Do you
have some data *** in the record *** that will tell you which is the first and
which is the second? A date time field, a reliable sequence number?

If not, then there is no way to know which record you want to keep and which you
want to delete.

Michael N wrote:

John,

I have the same type of issue where I about 20 people in a table that are
assigned to 2 groups.

Person_id,Name,Group

294986; Jones, Bob;Rehab
294986; Jones, Bob;Surgery

I used the first query you designed for Maurinnew as a select query to
identify the duplicates, but I want to delete the second entry and I know it
is a simple fix but it is escaping me. Could you give me a bit more
direction?

Mike

:

Limit the query to to those that only have duplicate member ID's

You may have to do this in multiple queries.

First delete those records where the Date of Action is Null and there are more
than one record for the the same Member ID.

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION]
FROM makepart1
WHERE MemberID IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)
AND [Date of Action] is Null

Now, delete the remaining duplicate records where

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]) AND
[Member Id] IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)



Maurinnew wrote:

Hi!

I have beaten my head trying to resolve this problem of deleting partly
duplicate records whose Member IDs are equal while DATE OF QUESTION are not.

member id DATE of Question Date of Action

*44 4/1/2003 4/30/2003
*44 4/2/2003 4/4/2003
*44 5/1/2003
44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003
33 3/4/2003

Earlier DATE OF QUESTION Should be deleted. However when the equal DATE
OF QUESTION remain as a subject to decide which one to take away ...then
DATA OF ACTION which is empty should be the choice

In addition , non- duplicate members (as 33) are untouchable

So I marked subject for deleing with asterisks.

The following query does the part of work

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 deletes earlier dates of DATE of QUESTION but unfortunately when there
are the SAME ID and SAME [Date of Question] â€|then I do not know how to delete
just the one whose [DATE OF ACTION] is null (if any). Also non-duplicates are
untouchable

When equal members (I mean equal id, DATE OF QUESTION ] and DATE OF ACTION
not null left like the followings:

member id DATE of Question Date of Action

44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003

Then I do not care which one to delete and the following query can be used
and it works.

SELECT makepart1.[MEMBER ID], Last(makepart1.[MEMBER FIRST NAME]) AS [Member
First Name_], Last(makepart1.[MEMBER LAST NAME]) AS [Member Last Name_},
makepart1.[other field] as Last(makepart1.[other field_])
FROM makepart1
GROUP BY makepart1.[MEMBER ID];

Is there any easy way to adjust my query?

Can it be done without VBA loop which I am not familiar with? I desperately
need itâ€|
Click to expand...
Click to expand...
Click to expand...
 
Mike,

Thank you for your response.

I have a feeling that you did yours using a loop which you mentioned above
in your previous post. Unfortunately I am not familiar with VBA. Do you think
that this kind of problem cannot be resolved without a loop?

Thank you ,

Maurin

Michael N said:
I don't think yours should be a problem. I was just using your post as I
have a similar problem it is is better etiqute to use an open discussion
rather than starting another one. I also used some of John's solution for
you to find the items I had duplicated.

Hope yours works out

Mike

maurinnew said:
Considering what you said to Mike...
Does it mean that I cannot do what I need (I mean my particular problem )
with Access?

Thanks,

Maurin

Michael N said:
Could I create a loop that would number each record where the Person_id is
the same 1, 2, 3 etc.? Basically have it start at line 1 and call it 1 then
go to line 2 have it compare the id from line 1 if it is the same as 1 then
call it 2. then move to line 3 and compare the person_id to the above line
and if it does not match ten go back to 1 and start the process over.

Then I could write a query to pull all the records with a 1.

I am not entirely sure how to pull something like this off ina query, is it
possible and could you give me some pointers?

Mike

:

The problem here is how does Access know which one is the second entry. Do you
have some data *** in the record *** that will tell you which is the first and
which is the second? A date time field, a reliable sequence number?

If not, then there is no way to know which record you want to keep and which you
want to delete.

Michael N wrote:

John,

I have the same type of issue where I about 20 people in a table that are
assigned to 2 groups.

Person_id,Name,Group

294986; Jones, Bob;Rehab
294986; Jones, Bob;Surgery

I used the first query you designed for Maurinnew as a select query to
identify the duplicates, but I want to delete the second entry and I know it
is a simple fix but it is escaping me. Could you give me a bit more
direction?

Mike

:

Limit the query to to those that only have duplicate member ID's

You may have to do this in multiple queries.

First delete those records where the Date of Action is Null and there are more
than one record for the the same Member ID.

DELETE makepart1.[MEMBER ID], makepart1.[DATE OF QUESTION]
FROM makepart1
WHERE MemberID IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)
AND [Date of Action] is Null

Now, delete the remaining duplicate records where

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]) AND
[Member Id] IN
(SELECT [Member Id]
FROM MakePart1
GROUP BY [Member ID]
HAVING Count([Member Id])>1)



Maurinnew wrote:

Hi!

I have beaten my head trying to resolve this problem of deleting partly
duplicate records whose Member IDs are equal while DATE OF QUESTION are not.

member id DATE of Question Date of Action

*44 4/1/2003 4/30/2003
*44 4/2/2003 4/4/2003
*44 5/1/2003
44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003
33 3/4/2003

Earlier DATE OF QUESTION Should be deleted. However when the equal DATE
OF QUESTION remain as a subject to decide which one to take away ...then
DATA OF ACTION which is empty should be the choice

In addition , non- duplicate members (as 33) are untouchable

So I marked subject for deleing with asterisks.

The following query does the part of work

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 deletes earlier dates of DATE of QUESTION but unfortunately when there
are the SAME ID and SAME [Date of Question] â€|then I do not know how to delete
just the one whose [DATE OF ACTION] is null (if any). Also non-duplicates are
untouchable

When equal members (I mean equal id, DATE OF QUESTION ] and DATE OF ACTION
not null left like the followings:

member id DATE of Question Date of Action

44 5/1/2003 5/1/2003
44 5/1/2003 5/3/2003

Then I do not care which one to delete and the following query can be used
and it works.

SELECT makepart1.[MEMBER ID], Last(makepart1.[MEMBER FIRST NAME]) AS [Member
First Name_], Last(makepart1.[MEMBER LAST NAME]) AS [Member Last Name_},
makepart1.[other field] as Last(makepart1.[other field_])
FROM makepart1
GROUP BY makepart1.[MEMBER ID];

Is there any easy way to adjust my query?

Can it be done without VBA loop which I am not familiar with? I desperately
need itâ€|
Click to expand...
Click to expand...
Click to expand...
 
Back
Top