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

 

[lameid]

Take a look at the Where clause of your main query. It uses in and a Select statment to identify that in addition to the right front postion (as that is the field), it must also match the left front in the where condition. You just need to add similar criteria use and for each additional field you want to check for duplicates.

 

[Remou]

I think I would be inclined to use a Union query to simulate a normal table.

 

[testkitt2]

Thanks lameid for your suggestion, but your suggestion sounds a bit complex..you see you can have many duplicates in any of the 14 tire positions that currently exist on multiple trucks and this is cause by not removing or updating old data. an example of what I mean is this..tire# 502 is on the right front position for trk#2 and the same tire# 502 also shows on the rear rear innner position for trk #3. You see there are so many diff possible combinations and Access 2003 dupe query only looks for dupes that are in the same position.
Thanks again.
Jz

**************
Hey Remou
How can I use a union...the positions are all in one table...the only thing is that the main form has a textbox that has a record of each tire number and its location.
any other suggestion would be appreciated.
Thank you
JZ

Testkitt2

 

[Remou]

First create the union query, then group it:

[tt]SELECT tblTirePosition.[Fleet Number] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Right Front] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Left Front] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Right Rear Inner] From tblTirePosition
<... etc>[/tt]

 

[testkitt2]

Good eve to all
and thanks Remou
I'm totally...?????
Ok there is only one table that holds all tire positions, Remou when you say make a "union", do you mean convert my existing query...
Your suggestion sounds like it would work..but I'm stuck..please explain or give me an example..
Thanks
JZ

Testkitt2

 

[Remou]

I did give you an example. Paste the lines above into the SQL view of a query. If they suit, add all the positons in the same format.

 

[testkitt2]

Remou,

I know I'm doing something wrong here, I took your example and added all the positions to the union SQL and came up with this

SELECT tblTirePosition.[Fleet Number] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Right Front] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Left Front] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Right Rear Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Right Tag axle Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Left Tag axle Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Right Rear Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Left Rear Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Left Rear Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Right Rear Rear Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Right Rear Rear Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Left Rear Rear Inner] From tblTirePosition
UNION ALL SELECT tblTirePosition.[Left Rear Rear Outer] From tblTirePosition;

Saved as a Union Query ..when I run it all the data trucks number and tire pos numbers all show up in one column.
I tried a few changes but to no avail.
If you can..I'm willing to try it again.
Maybe I'm missing a step or a few steps.
I created a regular select query from a table named "tblPositions" then I changed the view to SQL and pasted the code above in the SQL design.
All I get as a result is data that looks like this..
672540
672542
672642
2580
2658
1995
2249
2630
1456
2590
2330
2591
2472
0
0
0
0
0
2329
2281
2621

Hey thanks for your time anyway.
JZ


Testkitt2

 

[Remou]

That should be correct. You now have a query that approximates a normal table. You can now use the union query in a query to find duplicates. For example:

[tt]SELECT First([TID]) AS [TID Field], Count([TID]) AS NumberOfDups
FROM qryUnion
GROUP BY [TID]
HAVING Count([TID])>1;[/tt]

 

[lameid]

Ah now I see what you want... And I like the Union query idea but since you want to remove duplicates I would use Union and not Union All.

Union stacks records from 2 select staments and eliminates duplicates, specifying ALL returns duplicates. In my experience 99 out of 100 times I want Union ALL but clearly you want Union.

SELECT tblTirePosition.[Fleet Number] From tblTirePosition
UNION
SELECT tblTirePosition.[Right Front] From tblTirePosition
UNION
SELECT tblTirePosition.[Left Front] From tblTirePosition
UNION
SELECT tblTirePosition.[Right Rear Inner] From tblTirePosition
UNION
SELECT tblTirePosition.[Right Tag axle Outer] From tblTirePosition
UNION
SELECT tblTirePosition.[Left Tag axle Inner] From tblTirePosition
UNION
SELECT tblTirePosition.[Right Rear Outer] From tblTirePosition
UNION
SELECT tblTirePosition.[Left Rear Inner] From tblTirePosition
UNION
SELECT tblTirePosition.[Left Rear Outer] From tblTirePosition
UNION
SELECT tblTirePosition.[Right Rear Rear Outer] From tblTirePosition
UNION
SELECT tblTirePosition.[Right Rear Rear Inner] From tblTirePosition
UNION
SELECT tblTirePosition.[Left Rear Rear Inner] From tblTirePosition
UNION
SELECT tblTirePosition.[Left Rear Rear Outer] From tblTirePosition;

 

[Remou]

I do not think that testkitt2 wishes to remove duplicates, I think that he/she wishes to locate duplicates, hence Union All. It is clear that since all tires must be unique - they are barcoded - a duplicate must be a data entry error.

 

[lameid]

Ok... Identify which Fleet Numbers have duplicate tires...

I missed an error in the union query at first glance. You union the fleet number with tires. This will not be useful. You need the Fleet number and Tire Position for each field.

SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Fleet Number], tblTirePosition.[Right Front] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Front] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Rear Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Tag axle Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Tag axle Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Rear Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Rear Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Rear Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Rear Rear Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Rear Rear Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Rear Rear Inner] From tblTirePosition
UNION ALL SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Rear Rear Outer] From tblTirePosition;

At this point it is as Remou stated that you can use this query as a basis to identify duplicates.

In a perfect world you could fix the table structure so that you have a table with the Fleet number, tire postion and tire as the three fields. Then finding duplicates would be much easier and faster plus the data would be normalized to at least third normal form (almost always a good thing). But I digress... I am sure there is probably way to much database to go trying to fix the table structure now. I have a similar situation where I am going to have to start over and completely replace the database. Since I brought it up, here is an URL to something about data normalization...

http://www.datamodel.org/NormalizationRules.html

 

[Remou]

So, lameid, you are recommending that testkitt2 should follow my idea?

 

[lameid]

Remou, yes but with correct SQL.

I completely missed the bus on the issue, initially.

Poor testkitt2 probably has a ton of e-mail notifications by now

 

[Remou]

What I posted was an example, in the spirit, I think, of Tek-Tips. It should not have been difficult for the OP to provide some input to complete the idea.

 

[testkitt2]

hello to all and good eve
Thanks to all who posted... suggestions
Thanks Remou (TechnicalUser)

What I posted was an example, in the spirit,
[/quote}
its all appreciated and I was not trying to be otherwise. I'm not that good when it comes to coding. so suggesting in part just made it that much more confusing.
Thank you lameid for your input... I tried your suggestion only to be presented with this error message.
*******
The number of columns in the two selected tables or queires of a union query does not match
********
here is the code I replaced into the union query.

SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Fleet Number], tblTirePosition.[Right Front] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Front] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Rear Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Tag axle Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Tag axle Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Rear Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Rear Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Rear Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Rear Rear Outer] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Right Rear Rear Inner] From tblTirePosition
UNION ALL
SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Rear Rear Inner] From tblTirePosition
UNION ALL SELECT tblTirePosition.[Fleet Number], tblTirePosition.[Left Rear Rear Outer] From tblTirePosition;

Hey.. I'll keep trying
JZ



Testkitt2

 

[testkitt2]

Lameid
Stated this
In a perfect world you could fix the table structure so that you have a table with the Fleet number, tire postion and tire as the three fields. Then finding duplicates would be much easier.
Hey I agree but you have a truck that has 2 front tires 2 tag axels, 4 rear inner and outer tires and 4 rear rear inner and outer tires, that's 12 tires positions and some trucks have 4 tag axles tires making it a 14 tire position truck.
Also you point out of first spotting a dupe instead of just deleting them off the bat is correct..because due to a likely data entry error you don't acutally know what's right and what's wrong until you look at the truck yourself.
again
Thank you
JZ

Testkitt2

 

[Remou]

As a general rule, a database should be set up correctly, that is according to the rules of relational database deisgn. I suggested this to you before, and lameid has mentioned it, albeit, very politely. How the data is then displayed is a completely different issue. You are now running into one of the problems that will occur when your tables are not normalized. There will be more.

People in Tek-Tips like to see a little give and take, there are many who are not up-to-speed, but a stab at solving the problem for yourself will go a long way.

The error message you quoted is quite precise:

The number of columns in the two selected tables or queires of a union query does not match

Have you counted the columns in each statement? It is not impossible that a person replying to your posts is busy and, makes a small error in the code supplied. It is a good exercise to spot that error.

 

[lameid]

Ironically, I left the same error of unioning the fleet number with the tires. I thought I had deleted the select statement before adding the Fleet number field to all the select statements, aparently not.

As for normalization... What you are suggesting by describing your situation is that you need more tables than what I proposed to capture axles. In all honesty this may be excessive to what the database needs to do but if you wanted to know which axle had the most tires made after 3 months ago, you would need to keep track of axles or write another query specifically to manhandle your data.

FleetAxleID
Fleet Number
Axle

Axle ID
Tire Position (Left or Right, Inner or Outer?; possibly two separate fields)
Tire

Not having data in at least third normal form is like trying to cook popcorn with a magnifying glass... It is just obnoxious.

Data normalization...

http://www.datamodel.org/NormalizationRules.html

 

[testkitt2]

Hello and good eve to all.
As Remou quoted.

As a general rule, a database should be set up correctly, that is according to the rules of relational database deisgn. I suggested this to you before, and lameid has mentioned it, albeit, very politely. How the data is then displayed is a completely different issue. You are now running into one of the problems that will occur when your tables are not normalized. There will be more.

He is completely correct in saying this...but guess what I'm too deep in the Mud to back out now, and I should have not strayed away from my original question. Ok maybe this can't be done. My question was this...
I created with Access built in "find duplicates" and it did..but only if the dupe existed in the same row or column of the table.
It's too late to re create a new DB with new tables ...too much data is already in the DB... however I can have someone in the JOB go one by one to try and local dupes..but I thought that maybe someone had a better way.

I give you guys lots of credit...you've have helped me in the past tremendously... if this is a dead horse then lets just call it that.
I work at a truck shop (big company) fixing trucks...Access is something I'd like to learn...but it's not that easy..I'll admit..
Do please guys ...post something I could try...
Thank you
JZ

Testkitt2