some query help please

[Guest]

I need some help trying to figure out how to do something. In table rows
when the data has the same NHA and PART, the ITEM_NO should be equal.(see
sample data) I am trying to figure out how to write such a query where I can
bring back results of the NHA, Part and Item_no if the Item_no do not match.
I am trying to catch user entry errors. This is on one table. Any Ideas?
(At least until I can get the company to normalize their data)


Sample data (Bad data Item_no should be same)

NHA PART ITEM_NO
10190635-2 10190642-1 002
10190635-2 10190642-1 003

Sample data (Proper Data)
NHA PART ITEM_NO
10190635-2 10190642-1 002
10190635-2 10190642-1 002

 

[strive4peace]

Hi Dave,

make a query

add 2 copies of your table

link NHA to NHA
and PART to PART

on the grid:

field --> ITEM_NO
table --> Tablename
criteria --> <> Tablename_1.ITEM_NO

and add any other fields to the grid that you would like to see

.... but, if ITEM_NO depends on something else, why are you
storing it when you can just make a reference to it?
Storing calculated fields and storing information in more
than one place is not a good idea

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Tom Ellison]

Dear Dave:

Start with this:

SELECT DISTINCT NHA, PART, ITEM_NO
FROM YourTable

From this, count how many rows this returns with NHA and PART the same, but
with different ITEM_NOs.

SELECT NHA, PART, COUNT(*) AS DupCt
FROM (
SELECT DISTINCT NHA, PART, ITEM_NO
FROM YourTable) T
GROUP BY NHA, PART
HAVING COUNT(*) > 1

To continue, save the above as Q1.

If you wish, you can then use this list of NHA and PART to show all the
different ITEM_NOs that have been generated:

SELECT NHA, PART, ITEM_NO
FROM YourTable T
LEFT JOIN Q1 ON Q1.NHA = T.NHA
AND Q1.PART = T.PART

In all the above, replace YourTable with the actual name of the table.

Does this do anything like what you wanted?

Tom Ellison

 

[Guest]

Just did something similar at work. This is about the only time I use First
and Last. You need to insert the proper table name in the right places. After
fixing the problem records, you need to run it again as this won't catch a
third, fourth, etc., record. As always, make a backup before messing with
data this much.

SELECT NHA.NHA,
NHA.PART,
First(NHA.ITEM_NO) AS FirstOfITEM_NO,
Last(NHA.ITEM_NO) AS LastOfITEM_NO
FROM NHA
GROUP BY NHA.NHA, NHA.PART
HAVING First(NHA.ITEM_NO)<>Last([ITEM_NO]);

 

[Guest]

Tom,

Thanks for the reply. I was trying something similar to what your query is
doing.
I was first doing

SELECT DISTINCT NHA, PART, ITEM_NO
FROM TableA
Saved the query as queryA and then was trying

select Distinct QueryA.NHA , QueryA.Part ,
from QueryA, QueryA_1
where QueryA.NHA = QueryA_1.NHA and QueryAPart = QueryA_1.Part
group by QueryA.NHA , QueryAPart
having count(QueryA_1.Item_NO) > 1

but this did not work. I will give yours a try
Thanks for the response!

 

[Guest]

Tom,

Thanks it worked beautifully.

Dear Dave:

Start with this:

SELECT DISTINCT NHA, PART, ITEM_NO
FROM YourTable

From this, count how many rows this returns with NHA and PART the same, but
with different ITEM_NOs.

SELECT NHA, PART, COUNT(*) AS DupCt
FROM (
SELECT DISTINCT NHA, PART, ITEM_NO
FROM YourTable) T
GROUP BY NHA, PART
HAVING COUNT(*) > 1

To continue, save the above as Q1.

If you wish, you can then use this list of NHA and PART to show all the
different ITEM_NOs that have been generated:

SELECT NHA, PART, ITEM_NO
FROM YourTable T
LEFT JOIN Q1 ON Q1.NHA = T.NHA
AND Q1.PART = T.PART

In all the above, replace YourTable with the actual name of the table.

Does this do anything like what you wanted?

Tom Ellison