1. Open the database window to tables, right click on TblItems and copy.
2. Move the cursor to a blank area, right click and paste. Name the copy
TblItemsLatLongNull.
3. Create a query based on TblItems and set the criteria of ItemLat and
ItemLong to Is Not Null.
4. Create a query based on TblItemsLatLongNull and set the criteria of ItemLat
and ItemLong to Is Null.
5. Create a third query and include both queries in 3 and 4.
6. Join the ItemLabel field in both queries.
7. Run the query.
The join on ItemLabel will cause the query to return all records with duplicate
ItemLabel. Since the first query contains only records where ItemLat and
ItemLong are not null and the second query contains records where they are null,
the third query returns matching records where ItemLat and ItemLong are not null
in at least one of the matching records and null in one or more of the other
matching records.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com www.pcdatasheet.com
"Tom Mitchell" <rt*****@swbell.net> wrote in message
news:70*************************@posting.google.co m...
All:
I'm stumped on a query. How do I find duplicates in a table where one
of the duplicates has values is a certain field and the other doesn't.
For example, I have the following table:
tblItems
ItemID
ItemLabel
ItemLat
ItemLong
(many other fields)
I want to return all the fields of all the records where there are two
or more identical ItemLabels, but only where ItemLat and ItemLong are
not null in one record and null in the other record(s).
Thanks
Tom