Unique Records with Duplicate Fields 1

[JW61]

I need a query to tell me when I have the same item but with a different range stored in the same location. Example, using the data below I would like the query to return Item 1, P1, A1, and 1, M1, A1 because it has two different ranges of the same item stored in the same location.

Also need to know when different Item are stored in the same location regardless of range. Results would be 2, C1 and 3, C1.

Data:

Item Range Location Qty
1 P1 A1 50
1 P3 B1 25
1 M1 A1 50
2 OK C1 25
2 P1 A1 25
3 OK C1 25


I would like to have this in one query but two is acceptable.

Thanks,
JW

 

[PWise]

Also need to know when different Item are stored in the same location regardless of range. Results would be 2, C1 and 3, C1

Why not also
1,a1
2,a1

 

[JW61]

Sorry missed that when I was creating the sample data.

Yes I would need that one also.

 

[PWise]

A simple sort should do

 

[BitZero]

Regarding dups for a given item/location:

Say the data is in a table named tblData. Create a query named qryDataDupItemLocation1 with the following SQL:

SELECT Item, Location
FROM tblData
GROUP BY Item, Location
HAVING Min(Range)<>Max(Range);

Next, create a query named qryDataDupItemLocation2 with this SQL:

SELECT tblData.*
FROM tblData INNER JOIN qryDataDupItemLocation1 ON (tblData.Location=[qryDataDupItemLocation1].Location) AND (tblData.Item=[qryDataDupItemLocation1].Item);

The first query will give you a list of all of the item/locations that have multiple values for range. The second query uses an inner join to return only the detail for the dups.

 

[BitZero]

Regarding dups for a given location:

Create a query named qryDataDupLocation1 with this SQL:

SELECT Location
FROM tblData
GROUP BY Location
HAVING Min(Item)<>Max(item)

Next, create a query named qryDataDupLocation2 with this SQL:

SELECT tblData.*
FROM tblData
INNER JOIN qryDataDupLocation1 ON tblData.Location = qryDataDupLocation1.Location;

The first query gives you a list of all of the locations that have multiple items. The second query then gives you the detail for those locations. Note that if you have say 100 records for location A1, and 99 of them have item 1, and one record has item 2, then when you run the second query, you will get all 100 records.

 

[JW61]

Thanks!!