Help on avoiding dupplicates (need distinct ID fields)

[HerrSober]

Hi all,

Due to a corrupt worker_ID table which I don't have write rights to, I get cartesian product on my query table below:

workhours_id    Date    Amount    worker_ID Productive
27567    05.07.2014    8,78    sein01    TRUE
27567    05.07.2014    8,78    sein01    FALSE
27568    05.07.2014    8,35    smith01    TRUE
27568    05.07.2014    8,35    smith01    FALSE
27569    05.07.2014    6,80    john01    FALSE

I'll therefore need your help to do a workaround:

If the field Productive is both FALSE and TRUE then I want it to return TRUE only like this:

workhours_id    Date    Amount    worker_ID Productive
27567    05.07.2014    8,78    sein01    TRUE
27568    05.07.2014    8,35    smith01    TRUE
27569    05.07.2014    6,80    john01    FALSE

Anyone have any ideas on how I could write a query for this?

 

[Micron]

Back up your tables before experimenting!!
I would try creating a select query using only the worker id field. Add it a second time to the grid, change the query to a totals query, use Count on that second field and put >1 as criteria. Create another query with your table and this query linked by worker id. Use the true/false field in this query and set the criteria to True. I'm thinking the first query will return only records where the worker id is duplicated and the second will only return records where the worker id has True in the record. You could make this an append query if you're trying to move these records into another table to clean things up.
BTW, just because you have duplicates doesn't make the results a Cartesian product. As far as I know, it happens when you include two or more tables and don't link any fields between at least one pair.