Finding specific records in Access 2012

Topic

New Reply

I have a database that keeps track of risk checks that need to be done.
It is possible to find all records that needed the check done in 2012, and it is easy to find all records that had the check done in 2012. If I use an unmatched query to find the records that still need to be done, I get a bad result. Example 2444 recs to be done, 2184 recs done. It should show 260 still need to be done. However, I get 931 still to be done. The record to be done and the one done are 2 different records.
This is driving me crazy.
Anybody have any ideas?
I am attaching a databse as an example.

Thanks,

itconc

Reply | Quote

Replies

The use of a text field for a unique identifier is a very poor choice. There are too many chances of accidently inserting a leading space, control code, etc in the data. I would suggest to first find unmatched UI’s, to see if this is where your problem lies. It may be necessary to search for unmatched values in each field. This problem appears to be data related, but since you only have 3 fields, it should not be difficult to clean up. I would suggest that you also convert your UI field to numeric.

Hope this helps….

Reply | Quote

The unique identifier is not a key field. The key field is the id.

Reply | Quote

An unmatched query does not work well with fields that contain duplicates and are not required. Please post the SQL that is executed, and we may be able to solve your problem with a distinct clause, but I would suggest a rewrite to make the field required and no duplicates allowed, otherwise you are looking for problems whern you try to build on this.

HTH
Peter

Reply | Quote

Definately a Data problem. There are two primary problems that you need to address. First, your “Unique IDs” aren’t unique. There are duplicates in both tables. This will throw off your totals since each record in tblDone can “match” multiple records in tblToBeDone. Run a “Find Duplicates” query on each table to identify and fix these. Next, there are numerous records in tblDone that don’t have a corresponding record in tblToBeDone. Therefore your total records in the tblDone table is more than the total records in the tblToBeDone table with a corresponding tblDone record.

You can run a few of queries to see what I mean.

First, join all records from tblDone to the corresponding records in tblToBeDone. You should get 1269 matches (this includes dupes).

Next, change the join to show all records from tblDone and only the records from tblToBeDone that match. Filter for NULL [ID]s on the tblToBeDone side to display tblDone records with no corresponding tblToBeDone record. There are 916 tblDone records with no corresponding “to be done”, which is why you have more “undone” records than you expect.

Finally, change the join to show all records from tblToBeDone and only the tblDone records that match. Filter for NULL [ID]s on the tblDone side to display tblToBeDone records with no corresponding tblDone record. This is the total number of records which are not “done”. I get 1185 records that are not “Done”.

Hope this helps.

Reply | Quote

Once you have the data problem corrected, I suggest the following:
Use all 3 fields as a primary key. This will insure uniqueness, and not allow any row to be duplicated. Fields may be duplicated, but not the entire row.

HTH
Peter

Reply | Quote

Thank you all. I will follow your advice.

Reply | Quote

Is there a reason that you need two tables? If every tblDone record should correspond to a tblToBeDone record, it would make more sense to have these in the same table. This would prevent problems with miskeying the unique ID field, prevent duplicates, and make your queries easier in the long run by avoiding an extra join.

Reply | Quote