Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Duplicate Records Query

Status
Not open for further replies.
jbehrne

jbehrne

Programmer
Dec 18, 2002
484
US
Hi all,

I am in a bind with a query qryDuplicateITVMatchesCorrectedUS:
Code: 
SELECT qryDuplicateITVMatchesUS.ITandVoucherID, qryDuplicateITVMatchesUS.Entry, qryUnMatchedUSGLData.Entry, qryDuplicateITVMatchesUS.GLSLine_No, qryUnMatchedUSGLData.Line_No, qryDuplicateITVMatchesUS.GLSLupdtdte, qryUnMatchedUSGLData.Lupdtdte, qryDuplicateITVMatchesUS.BudgetCode, qryUnMatchedUSGLData.Acct, qryDuplicateITVMatchesUS.ObjectCode, qryUnMatchedUSGLData.Obj, qryDuplicateITVMatchesUS.Amount, qryUnMatchedUSGLData.Amt
FROM qryUnMatchedUSGLData INNER JOIN qryDuplicateITVMatchesUS ON (qryUnMatchedUSGLData.Amt = qryDuplicateITVMatchesUS.Amount) AND (qryUnMatchedUSGLData.Obj = qryDuplicateITVMatchesUS.ObjectCode) AND (qryUnMatchedUSGLData.Acct = qryDuplicateITVMatchesUS.BudgetCode) AND (qryUnMatchedUSGLData.Lupdtdte = qryDuplicateITVMatchesUS.GLSLupdtdte) AND (qryUnMatchedUSGLData.Entry = qryDuplicateITVMatchesUS.Entry);

This query returns records from two queries(qryUnMatchedUSGLDATA - I'll refer to this as qry1, qryDuplicateITVMatchesUS - I'll refer to this as qry2) where five fields in each table match:

Entry = Entry
Lupdtdte = USLupdtdte
Acct = BudgetCode
Obj = ObjectCode
Amt = Amt

When the query runs it returns records like this:

Code: 
ITandVoucherID qry1.Entry qry2.Entry GLSLine_No	Line_No
34774	83750	83750	1	9
34774	83750	83750	1	41
34774	83750	83750	1	27
34774	83750	83750	1	3
34774	83750	83750	1	33
34774	83750	83750	1	21
34780	83750	83750	1	41
34780	83750	83750	1	27
34780	83750	83750	1	21
34780	83750	83750	1	33
34780	83750	83750	1	9
34780	83750	83750	1	3
34785	83750	83750	7	14
34787	83750	83750	7	14
34792	83750	83750	1	9
34792	83750	83750	1	27
34792	83750	83750	1	33
34792	83750	83750	1	21
34792	83750	83750	1	3
34792	83750	83750	1	41
34798	83750	83750	1	41
34798	83750	83750	1	33
34798	83750	83750	1	3
34798	83750	83750	1	21
34798	83750	83750	1	27
34798	83750	83750	1	9
34804	83750	83750	1	3
34804	83750	83750	1	9
34804	83750	83750	1	27
34804	83750	83750	1	33
34804	83750	83750	1	21
34804	83750	83750	1	41
34807	83750	83750	34	37
34808	83750	83750	34	37
34811	83750	83750	1	21
34811	83750	83750	1	33
34811	83750	83750	1	27
34811	83750	83750	1	9
34811	83750	83750	1	41
34811	83750	83750	1	3
34812	83750	83750	1	33
34812	83750	83750	1	3
34812	83750	83750	1	9
34812	83750	83750	1	41
34812	83750	83750	1	27
34812	83750	83750	1	21
35102	86946	86946	3	16
35102	86946	86946	3	18
35102	86946	86946	3	14
35102	86946	86946	3	8
35102	86946	86946	3	21
35108	86946	86946	3	21
35108	86946	86946	3	18
35108	86946	86946	3	16
35108	86946	86946	3	8
35108	86946	86946	3	14
35110	86946	86946	3	21
35110	86946	86946	3	18
35110	86946	86946	3	8
35110	86946	86946	3	14
35110	86946	86946	3	16
35112	86946	86946	3	21
35112	86946	86946	3	8
35112	86946	86946	3	16
35112	86946	86946	3	18
35112	86946	86946	3	14
35115	86946	86946	3	21
35115	86946	86946	3	8
35115	86946	86946	3	14
35115	86946	86946	3	16
35115	86946	86946	3	18
35121	86946	86946	3	8
35121	86946	86946	3	21
35121	86946	86946	3	14
35121	86946	86946	3	16
35121	86946	86946	3	18
35479	93104	93104	3	4
35479	93104	93104	3	14
35488	93104	93104	3	14
35488	93104	93104	3	4
35491	93104	93104	16	17
35496	93104	93104	3	14
35496	93104	93104	3	4
35506	93104	93104	16	17

Is there a way to rewrite the query listed above so that the records returned are not duplicated multiple times? The data should look like this:
Code: 
ITandVoucherID qry1.Entry qry2.Entry GLSLine_No	Line_No
34774	83750	83750	1	3
34780	83750	83750	1	9
34787	83750	83750	7	14
34792	83750	83750	1	21
34798	83750	83750	1	27
34804	83750	83750	1	33
34808	83750	83750	34	37
34811	83750	83750	1	41
35102	86946	86946	3	8
35108	86946	86946	3	14
35110	86946	86946	3	16
35112	86946	86946	3	18
35115	86946	86946	3	21
35479	93104	93104	3	4
35488	93104	93104	3	14
35506	93104	93104	16	17

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations
 
Sort by date Sort by votes
How 'bout SELECT DISTINCT?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
I've tried that but records returned are not any different without it.. This particular query has got me good and stumped!

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations
 
Upvote 0 Downvote
Term "duplicate" is obviously subjective... plz show two or more rows you consider duplicate - and why.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]
[banghead]
 
Upvote 0 Downvote
Sure,

Here is a little back ground info to. The data in the query 'qryDuplicateITVMatchesUS' are records entered by an user that track department purchases (the underlying table is called ITV). The data in the query 'qryUnMatchedUSGLDATA' are records that have been processed by a billing department.

A matching process is run where the listed 5 fields above are compared to each record and if the fields matched the table ITV is updated so that the record is marked as cleared (ie. a boolean field is set to true). However, sometimes the program matches multiple records from the table ITV to one record from 'qryUnMatchedUPGLDATA'., instead of one-to-one match.

When I say duplicate records I mean that the duplicate records are the records that are mismatched.

For example (duplicate records):
Code: 
34774    83750    83750    1    3
34774    83750    83750    1    9
34774    83750    83750    1    21
34774    83750    83750    1    27
34774    83750    83750    1    33
34774    83750    83750    1    41

34780    83750    83750    1    3
34780    83750    83750    1    9
34780    83750    83750    1    21
34780    83750    83750    1    27
34780    83750    83750    1    33
34780    83750    83750    1    41

34792    83750    83750    1    3
34792    83750    83750    1    9
34792    83750    83750    1    21
34792    83750    83750    1    27
34792    83750    83750    1    33
34792    83750    83750    1    41

34798    83750    83750    1    3
34798    83750    83750    1    9
34798    83750    83750    1    21
34798    83750    83750    1    27
34798    83750    83750    1    33
34798    83750    83750    1    41

34804    83750    83750    1    3
34804    83750    83750    1    9
34804    83750    83750    1    21
34804    83750    83750    1    27
34804    83750    83750    1    33
34804    83750    83750    1    41

34811    83750    83750    1    3
34811    83750    83750    1    9
34811    83750    83750    1    21
34811    83750    83750    1    27
34811    83750    83750    1    33
34811    83750    83750    1    41

These are 'duplicate' records because the field 'qry1.Entry' has multiple values of '1' (the underlying records from table ITV should have been originally matched to the correct values from the field 'qry2.Entry'). The query should return the results (which should be what the original match would have made...:
Code: 
34774    83750    83750    1    3
34780    83750    83750    1    9
34792    83750    83750    1    21
34798    83750    83750    1    27
34804    83750    83750    1    33
34811    83750    83750    1    41

Thanks for your help,

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Locked
  • Question
Parameterised sql server query
Replies
8
Views
482
Chriss Miller
C
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
620
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
868
Andrzejek
Andrzejek
Cpreston
  • Locked
  • Question
Adding field duplicates records
Replies
16
Views
623
Chris Miller
Chris Miller
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
760
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top