Duplicates that meet other criteria

[erikjhayes]

Hello,

I am having trouble figuring out how to build a query for what I need. I have a single table with the following fields

ID (auto number)
Item_Type
Serial_Number
Location

With data entries like the following



Item_Type Serial_Number Location
Chair 12345 Storage
Chair 6789 xfer out
Chair 6789 xfer in
Chair 12345 xfer out
Chair 22222 storage

What I need to do is build a query that will identify the duplicates by serial number but only return the records where one of the duplicate serial numbers has a location of storage. So out of the above example it should return

Item_Type Serial_Number Location
Chair 12345 Storage
Chair 12345 xfer out

Any help would be greatly appreciated.

[June7]

Domain aggregates can probably get that:

SELECT *
FROM table1
WHERE (((DLookUp("Location","table1","Serial_Number=" & [Serial_Number])="Storage")=True) AND ((DCount("Serial_Number","table1","Serial_Number=" & [Serial_Number]))>1));

Is Serial_Number a number type field?

[erikjhayes]

Good morning June7,

Thank you for the reply. Serial_Number is a text field as actual serial numbers can very in length and format. I tried the code you provided and received a "Syntax error (missing operator) in query expression 'Serial_Number=201CJZ3166' " error. This is one of the serial numbers in the table.

Thank you for the help.

[erikjhayes]

I have attached the db to hopefully make it easier.

Thank you.

[June7]

If Serial_Number is a text field, then modify the expressions to include apostrophe delimiters for the parameter.

"Serial_Number='" & [Serial_Number] & "'")

DateTime field would use # for parameters.