How can I edit this SQL or VB equivalent to search all pos for Dupes?

[testkitt2]

This in SQL view is a query that only spots dupes if the dupe is in the same position. I like to expand that to search all positions. As always thanks.

SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Front], tblTirePosition.[Left Front], tblTirePosition.[Right Rear Inner], tblTirePosition.[Left Rear Inner], tblTirePosition.[Right Rear Rear Inner], tblTirePosition.[Left Rear Rear Inner], tblTirePosition.[Right Rear Outer], tblTirePosition.[Left Rear Outer], tblTirePosition.[Right Rear Rear Outer], tblTirePosition.[Left Rear Rear Outer]
FROM tblTirePosition
WHERE (((tblTirePosition.[Right Front]) In (SELECT [Right Front] FROM [tblTirePosition] As Tmp GROUP BY [Right Front],[Left Front] HAVING Count(*)>1  And [Left Front] = [tblTirePosition].[Left Front])))
ORDER BY tblTirePosition.[Fleet Number], tblTirePosition.[Right Front], tblTirePosition.[Left Front];

JZ

Testkitt2

 

[pdldavis]

Hi, How about this:

The Union Query:

SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Front] AS TireNo, "Right Front" AS RF
FROM tblTirePosition
Union
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Front], "Left Front" as LF
FROM tblTirePosition
union
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Rear Inner], "Right Inner Rear" as RIR
FROM tblTirePosition
union
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Rear Inner], "Left Inner Rear" as LIR
FROM tblTirePosition
union
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Rear Outer], "Right Rear Outer" as RRO
FROM tblTirePosition
UNION SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Rear Outer], "Left Rear Outer" as LRO
FROM tblTirePosition;

Name it as Qry-Union

Find Dupes:

SELECT [Qry-Union].TireNo, [Qry-Union].[Fleet Number], [Qry-Union].RF AS [Tire Position]
FROM [Qry-Union]
WHERE ((([Qry-Union].TireNo) In (SELECT [TireNo] FROM [Qry-Union] As Tmp GROUP BY [TireNo] HAVING Count(*)>1 )))
ORDER BY [Qry-Union].TireNo;

That ought to work, adjusting the field names as necessary.

 

[testkitt2]

Hello,
I haven't been on for a while...computer was donated...have a new one now...
the last post has not been tried..but I will give it a shot.

The other codes did not work for me...I need something that can compare 10 individual positions at one time and find if there is a duplicate.
Thanks
Jz

Testkitt2

 

[lameid]

You want to know if a tire is duplicated on any truck (what the examples are trying to do) OR show you a tire duplicated on A truck?

 

[Remou]

[blue]lameid[/blue], there is a bit of a saga behind this post. The last installment is, I think, thread705-1362084

 

[testkitt2]

lameid
Access has a query that finds dupes...but since my DB does not have all tables relating or allowing to create a dupe. the access query will find a dupe only if its in the same column or row.
What I have if you can picture this a picture of a Truck and in place of the trucks wheels a text box which represents a barcode (a number stamped on the actual trucks tire). Each tire has its own unique number. entering data in this DB by more than one person can cause a typo or dupe entry. so my original question was can you take the "find a dupe" query that access lets you make and modify it so it can look in multiple tire positions within the same table and spot dupe barcodes. that's it.
JZ

Testkitt2

 

[lameid]

What is wrong with pdldavis's code? What doesn't it do or what does it do? It is an example of what I was suggesting.

 

[pdldavis]

Boy, been a while since I have seen this one. I use something very similar in a couple db's where normalizing is out of the question.

The find dupes routine as posted should work if the correct field names are applied.