Validation

[MTSPEER]

Hello,

I have a table1 that looks like this:

GroupNumber ID1 ID2
111 0 000
111 1 100
111 4 025
111 4 050



And I want to validate this info to another table, table2. Has the same fields. Just want to validate it against each other to make sure that is the same data. And I want it to show the records that don't match.

[JoeM]

Access has a built in "Unmatched Query Wizard" which should walk you through how to do this (find all the records in Table A that aren't in Table B).

The only issue is that the Wizard only allows you to link the tables on one field. However, that is pretty easy to rectify. When complete, just open the query in Design View and join the other fields. Note that direction of the arrow on the join that it created. You will want to make the others to match. To do this, just double-click on the arrow and change the join type (to option 2 or 3). When done right, all your arrows will point in the same direction.

If you need to do it the other way too (find all the records in Table B that aren't in Table A), just repeat the same process going in the other direction.

[MTSPEER]

Hello, yes I can get it to show the ones that are are not matched with table1. But I can't get it to show records that are missing altogether

[JoeM]

I can get it to show the ones that are are not matched with table1. But I can't get it to show records that are missing altogether

I am not sure I understand. "Missing altogether" when compared to what?

Maybe a few examples will help illustrate what you are trying to do.

[MTSPEER]

Like for example:
In Table1 GroupNumber ID1 ID2
111 1 100
111 4 100
111 4 025


In Table2 GroupNumber ID1 ID2
111 1 100
111 4 100

But the one record is missing, the GroupNumber 111 ID1 4 ID2 025. I want it to showif there is a record missing also.

[JoeM]

That's exactly what the Unmatched Query should do. Did you create an unmatched Query?
If so, open the Query, switch to SQL View, and copy and paste the code here so we can see how you have it set-up.
Also, when comparing these two tables, do you want to compare all three fields, or just one or two?

[MTSPEER]

SELECT PGMF.GROUP_NUMBER, PGMF.LICS_LEVEL, PGMF.LIPS_LEVEL
FROM SubGroupNumber RIGHT JOIN PGMF ON (SubGroupNumber.LIPS_LEVEL = PGMF.LIPS_LEVEL) AND (SubGroupNumber.LICS_LEVEL = PGMF.LICS_LEVEL) AND (SubGroupNumber.[GROUP_NUMBER] = PGMF.[GROUP_NUMBER])
WHERE (((SubGroupNumber.GROUP_NUMBER) Is Null));

Here is the code in SQL view. the name of the fields and tables are different because I just wanted to make things easier to write in here. Think of Table1 as SubGroupNumber and Table2 as PGMF. I'm validating PGMF against SubGroupNumber. And think of ID1 as LICS_LEVEL and ID2 as LIPS_LEVEL.

I have all 3 of the joins on GroupNumber, LICS_LEVEL, and LIPS_LEVEL.

What I want to do is validate the LICS and LIPS Levels. Right now it shows the record where the data is different, but doesnt show when a record is missing altogether.

[MTSPEER]

It's displaying the PGMF records where the data is different because they are joined. But how do I show the SubGroupNumber records also where they are missing in the PGMF table.

[JoeM]

That's because you have only gone one way. You are checking to see if there are any records in the PGMF table that are not in SubGroupNumber table. There are not (there are only 2 records in the PGMF table and they are both in the SubGroupNumber table).

Notice what I said in my first reply:

If you need to do it the other way too (find all the records in Table B that aren't in Table A), just repeat the same process going in the other direction.

So now you also need to do an Unmatched Query going in the other direction (are there are records in your SubGroupNumber table that are not in the PGMF table). So the SQL code for that is very similar, just going in the other direction.

SELECT SubGroupNumber.GROUP_NUMBER, SubGroupNumber.LICS_LEVEL, SubGroupNumber.LIPS_LEVEL
FROM SubGroupNumber LEFT JOIN PGMF ON (SubGroupNumber.[GROUP_NUMBER] = PGMF.[GROUP_NUMBER]) AND (SubGroupNumber.LICS_LEVEL = PGMF.LICS_LEVEL) AND (SubGroupNumber.LIPS_LEVEL = PGMF.LIPS_LEVEL)
WHERE (((PGMF.GROUP_NUMBER) Is Null));

So whenever you want to do something like this (compare two tables for missing records), you will need to do two unmatched queries (or a nested SQL query that does both, but that cannot be done solely with the Query Builder - you need to do some writing directly in SQL).

[MTSPEER]

Hello, I tried this, but in my SubGroupNumber table I have way more records in it than the PGMF table. I want so it will just match up with the GroupNumbers that match and only find the ones that are missing that relate in the PGMF table. Because otherwise so many more records appear that dont even relate to the PGMF table

[JoeM]

You can add another criteria line to limit it to just records where the Group_Number field from the SubGroupNumber table also exists in the PGMF table.
That extra line of code at the end would look like:

AND (SubgroupNumber.[GROUP_NUMBER] In (SELECT [GROUP_NUMBER] FROM PGMF));

So the total SQL code would look like:

SELECT SubGroupNumber.GROUP_NUMBER, SubGroupNumber.LICS_LEVEL, SubGroupNumber.LIPS_LEVEL
FROM SubGroupNumber LEFT JOIN PGMF ON (SubGroupNumber.[GROUP_NUMBER] = PGMF.[GROUP_NUMBER]) AND (SubGroupNumber.LICS_LEVEL = PGMF.LICS_LEVEL) AND (SubGroupNumber.LIPS_LEVEL = PGMF.LIPS_LEVEL)
WHERE (PGMF.GROUP_NUMBER Is Null) 
AND (SubgroupNumber.[GROUP_NUMBER] In (SELECT [GROUP_NUMBER] FROM PGMF));

[MTSPEER]

Thank you very much sir!