find duplicate records

E

Erik

I am tring to find duplicate records and i have that working just fine but i
would like to take the set_version and within the duplicate records only show
the records that are not duplicated under the set_version column. I hope this
makes sense. any help would be great. I have posted the SQL for my query.
SELECT DISTINCTROW [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING
LOG].LOT, [BILLING LOG].SET_VERSION, [BILLING LOG].[DATE COMPLETE], [BILLING
LOG].MEMO
FROM [BILLING LOG]
WHERE ((([BILLING LOG].DIVISION) In (SELECT [DIVISION] FROM [BILLING LOG] As
Tmp GROUP BY [DIVISION],[COMMUNITY],[LOT] HAVING Count(*)>1 And [COMMUNITY]
= [BILLING LOG].[COMMUNITY] And [LOT] = [BILLING LOG].[LOT])))
ORDER BY [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT;
 
K

KARL DEWEY

You did not show sample date so I am guessing. This is using a second query
instead of subquery.
Erik_Dup_Ck ---
SELECT [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT
FROM [BILLING LOG]
GROUP BY [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT
HAVING (((Count([BILLING LOG].LOT))>1));

SELECT [BILLING LOG].*, Erik_Dup_Ck.DIVISION, Erik_Dup_Ck.COMMUNITY,
Erik_Dup_Ck.LOT
FROM [BILLING LOG] LEFT JOIN Erik_Dup_Ck ON ([BILLING LOG].LOT =
Erik_Dup_Ck.LOT) AND ([BILLING LOG].COMMUNITY = Erik_Dup_Ck.COMMUNITY) AND
([BILLING LOG].DIVISION = Erik_Dup_Ck.DIVISION)
WHERE (((Erik_Dup_Ck.DIVISION) Is Null) AND ((Erik_Dup_Ck.COMMUNITY) Is
Null) AND ((Erik_Dup_Ck.LOT) Is Null));
 
E

Erik

What kind of sample data would you like to see? I will try to explain showing
the data i am using: I have a table called [billing log]. In that table i
have groups as follows: division,community,lot,set_version,date complete and
memo. I am trying to find the duplicate values for division,community, and
lot. I would like to show all that match in those records. I would also like
it to show only those records that match that criteria but have different
set_version. I am new to creating queries like this so i am not sure what hte
best way to go about this is. I started by using the wizard to create a find
duplicate records but now i am stuck trying to add the part to find within
the duplicate records the set_version that are different. I am also not sure
if i should be using a second query or a subquery. Maybe you can explain the
difference it if it not to complicated. thanks so much for the help.

KARL DEWEY said:
You did not show sample date so I am guessing. This is using a second query
instead of subquery.
Erik_Dup_Ck ---
SELECT [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT
FROM [BILLING LOG]
GROUP BY [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT
HAVING (((Count([BILLING LOG].LOT))>1));

SELECT [BILLING LOG].*, Erik_Dup_Ck.DIVISION, Erik_Dup_Ck.COMMUNITY,
Erik_Dup_Ck.LOT
FROM [BILLING LOG] LEFT JOIN Erik_Dup_Ck ON ([BILLING LOG].LOT =
Erik_Dup_Ck.LOT) AND ([BILLING LOG].COMMUNITY = Erik_Dup_Ck.COMMUNITY) AND
([BILLING LOG].DIVISION = Erik_Dup_Ck.DIVISION)
WHERE (((Erik_Dup_Ck.DIVISION) Is Null) AND ((Erik_Dup_Ck.COMMUNITY) Is
Null) AND ((Erik_Dup_Ck.LOT) Is Null));

--
KARL DEWEY
Build a little - Test a little


Erik said:
I am tring to find duplicate records and i have that working just fine but i
would like to take the set_version and within the duplicate records only show
the records that are not duplicated under the set_version column. I hope this
makes sense. any help would be great. I have posted the SQL for my query.
SELECT DISTINCTROW [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING
LOG].LOT, [BILLING LOG].SET_VERSION, [BILLING LOG].[DATE COMPLETE], [BILLING
LOG].MEMO
FROM [BILLING LOG]
WHERE ((([BILLING LOG].DIVISION) In (SELECT [DIVISION] FROM [BILLING LOG] As
Tmp GROUP BY [DIVISION],[COMMUNITY],[LOT] HAVING Count(*)>1 And [COMMUNITY]
= [BILLING LOG].[COMMUNITY] And [LOT] = [BILLING LOG].[LOT])))
ORDER BY [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT;
Click to expand...
Click to expand...
 
K

KARL DEWEY

What kind of sample data would you like to see?
Just a sample of the records in your tables.
Either one.

Did you try what I posted?

--
KARL DEWEY
Build a little - Test a little


Erik said:
What kind of sample data would you like to see? I will try to explain showing
the data i am using: I have a table called [billing log]. In that table i
have groups as follows: division,community,lot,set_version,date complete and
memo. I am trying to find the duplicate values for division,community, and
lot. I would like to show all that match in those records. I would also like
it to show only those records that match that criteria but have different
set_version. I am new to creating queries like this so i am not sure what hte
best way to go about this is. I started by using the wizard to create a find
duplicate records but now i am stuck trying to add the part to find within
the duplicate records the set_version that are different. I am also not sure
if i should be using a second query or a subquery. Maybe you can explain the
difference it if it not to complicated. thanks so much for the help.

KARL DEWEY said:
You did not show sample date so I am guessing. This is using a second query
instead of subquery.
Erik_Dup_Ck ---
SELECT [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT
FROM [BILLING LOG]
GROUP BY [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT
HAVING (((Count([BILLING LOG].LOT))>1));

SELECT [BILLING LOG].*, Erik_Dup_Ck.DIVISION, Erik_Dup_Ck.COMMUNITY,
Erik_Dup_Ck.LOT
FROM [BILLING LOG] LEFT JOIN Erik_Dup_Ck ON ([BILLING LOG].LOT =
Erik_Dup_Ck.LOT) AND ([BILLING LOG].COMMUNITY = Erik_Dup_Ck.COMMUNITY) AND
([BILLING LOG].DIVISION = Erik_Dup_Ck.DIVISION)
WHERE (((Erik_Dup_Ck.DIVISION) Is Null) AND ((Erik_Dup_Ck.COMMUNITY) Is
Null) AND ((Erik_Dup_Ck.LOT) Is Null));

--
KARL DEWEY
Build a little - Test a little


Erik said:
I am tring to find duplicate records and i have that working just fine but i
would like to take the set_version and within the duplicate records only show
the records that are not duplicated under the set_version column. I hope this
makes sense. any help would be great. I have posted the SQL for my query.
SELECT DISTINCTROW [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING
LOG].LOT, [BILLING LOG].SET_VERSION, [BILLING LOG].[DATE COMPLETE], [BILLING
LOG].MEMO
FROM [BILLING LOG]
WHERE ((([BILLING LOG].DIVISION) In (SELECT [DIVISION] FROM [BILLING LOG] As
Tmp GROUP BY [DIVISION],[COMMUNITY],[LOT] HAVING Count(*)>1 And [COMMUNITY]
= [BILLING LOG].[COMMUNITY] And [LOT] = [BILLING LOG].[LOT])))
ORDER BY [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT;
Click to expand...
Click to expand...
Click to expand...
 
E

Erik

I did try but not sure if it was what i needed or if i did it wrong. but here
is an example of what i need
division/community/lot/set_version
aaa/bc/0001/ava00_01
aaa/bc/0001/ava00_01
aaa/bc/0001/mah00_00
i would like the query to show
aaa/bc/0001/ava00_01
and
aaa/bc/0001/mah00_00
i do not want to see the records that the set_version are the same. I hope
thia helps

KARL DEWEY said:
Just a sample of the records in your tables.
Either one.

Did you try what I posted?

--
KARL DEWEY
Build a little - Test a little


Erik said:
What kind of sample data would you like to see? I will try to explain showing
the data i am using: I have a table called [billing log]. In that table i
have groups as follows: division,community,lot,set_version,date complete and
memo. I am trying to find the duplicate values for division,community, and
lot. I would like to show all that match in those records. I would also like
it to show only those records that match that criteria but have different
set_version. I am new to creating queries like this so i am not sure what hte
best way to go about this is. I started by using the wizard to create a find
duplicate records but now i am stuck trying to add the part to find within
the duplicate records the set_version that are different. I am also not sure
if i should be using a second query or a subquery. Maybe you can explain the
difference it if it not to complicated. thanks so much for the help.

KARL DEWEY said:
You did not show sample date so I am guessing. This is using a second query
instead of subquery.
Erik_Dup_Ck ---
SELECT [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT
FROM [BILLING LOG]
GROUP BY [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT
HAVING (((Count([BILLING LOG].LOT))>1));

SELECT [BILLING LOG].*, Erik_Dup_Ck.DIVISION, Erik_Dup_Ck.COMMUNITY,
Erik_Dup_Ck.LOT
FROM [BILLING LOG] LEFT JOIN Erik_Dup_Ck ON ([BILLING LOG].LOT =
Erik_Dup_Ck.LOT) AND ([BILLING LOG].COMMUNITY = Erik_Dup_Ck.COMMUNITY) AND
([BILLING LOG].DIVISION = Erik_Dup_Ck.DIVISION)
WHERE (((Erik_Dup_Ck.DIVISION) Is Null) AND ((Erik_Dup_Ck.COMMUNITY) Is
Null) AND ((Erik_Dup_Ck.LOT) Is Null));

--
KARL DEWEY
Build a little - Test a little


:

I am tring to find duplicate records and i have that working just fine but i
would like to take the set_version and within the duplicate records only show
the records that are not duplicated under the set_version column. I hope this
makes sense. any help would be great. I have posted the SQL for my query.
SELECT DISTINCTROW [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING
LOG].LOT, [BILLING LOG].SET_VERSION, [BILLING LOG].[DATE COMPLETE], [BILLING
LOG].MEMO
FROM [BILLING LOG]
WHERE ((([BILLING LOG].DIVISION) In (SELECT [DIVISION] FROM [BILLING LOG] As
Tmp GROUP BY [DIVISION],[COMMUNITY],[LOT] HAVING Count(*)>1 And [COMMUNITY]
= [BILLING LOG].[COMMUNITY] And [LOT] = [BILLING LOG].[LOT])))
ORDER BY [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT;
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try this --
SELECT DISTINCT [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING
LOG].LOT, [BILLING LOG].SET_VERSION
FROM [BILLING LOG];

--
KARL DEWEY
Build a little - Test a little


Erik said:
I did try but not sure if it was what i needed or if i did it wrong. but here
is an example of what i need
division/community/lot/set_version
aaa/bc/0001/ava00_01
aaa/bc/0001/ava00_01
aaa/bc/0001/mah00_00
i would like the query to show
aaa/bc/0001/ava00_01
and
aaa/bc/0001/mah00_00
i do not want to see the records that the set_version are the same. I hope
thia helps

KARL DEWEY said:
What kind of sample data would you like to see?
Click to expand...
Just a sample of the records in your tables.
I am also not sure if i should be using a second query or a subquery.
Click to expand...
Either one.

Did you try what I posted?

--
KARL DEWEY
Build a little - Test a little


Erik said:
What kind of sample data would you like to see? I will try to explain showing
the data i am using: I have a table called [billing log]. In that table i
have groups as follows: division,community,lot,set_version,date complete and
memo. I am trying to find the duplicate values for division,community, and
lot. I would like to show all that match in those records. I would also like
it to show only those records that match that criteria but have different
set_version. I am new to creating queries like this so i am not sure what hte
best way to go about this is. I started by using the wizard to create a find
duplicate records but now i am stuck trying to add the part to find within
the duplicate records the set_version that are different. I am also not sure
if i should be using a second query or a subquery. Maybe you can explain the
difference it if it not to complicated. thanks so much for the help.

:

You did not show sample date so I am guessing. This is using a second query
instead of subquery.
Erik_Dup_Ck ---
SELECT [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT
FROM [BILLING LOG]
GROUP BY [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT
HAVING (((Count([BILLING LOG].LOT))>1));

SELECT [BILLING LOG].*, Erik_Dup_Ck.DIVISION, Erik_Dup_Ck.COMMUNITY,
Erik_Dup_Ck.LOT
FROM [BILLING LOG] LEFT JOIN Erik_Dup_Ck ON ([BILLING LOG].LOT =
Erik_Dup_Ck.LOT) AND ([BILLING LOG].COMMUNITY = Erik_Dup_Ck.COMMUNITY) AND
([BILLING LOG].DIVISION = Erik_Dup_Ck.DIVISION)
WHERE (((Erik_Dup_Ck.DIVISION) Is Null) AND ((Erik_Dup_Ck.COMMUNITY) Is
Null) AND ((Erik_Dup_Ck.LOT) Is Null));

--
KARL DEWEY
Build a little - Test a little


:

I am tring to find duplicate records and i have that working just fine but i
would like to take the set_version and within the duplicate records only show
the records that are not duplicated under the set_version column. I hope this
makes sense. any help would be great. I have posted the SQL for my query.
SELECT DISTINCTROW [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING
LOG].LOT, [BILLING LOG].SET_VERSION, [BILLING LOG].[DATE COMPLETE], [BILLING
LOG].MEMO
FROM [BILLING LOG]
WHERE ((([BILLING LOG].DIVISION) In (SELECT [DIVISION] FROM [BILLING LOG] As
Tmp GROUP BY [DIVISION],[COMMUNITY],[LOT] HAVING Count(*)>1 And [COMMUNITY]
= [BILLING LOG].[COMMUNITY] And [LOT] = [BILLING LOG].[LOT])))
ORDER BY [BILLING LOG].DIVISION, [BILLING LOG].COMMUNITY, [BILLING LOG].LOT;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Group dates in query 1
Search for records by date 3
Need Mailing and Billing Address Displayed from Same Table 4
'Duplicate' records returned in query. 4
Query quandary 5
what's wrong with my query 2
ALL query not working 2
How to count 2 datasets and then subtract the difference? 3

Top