MS Access Solution

Jubal77

It has been awhile since I messed around with Access and I want to know if I am getting the correct results. I have 5 long lists that I need to compare to a master record. So what I ended up doing is importing the excel lists into access to compare them. So for instance:

Master List:
1
2
3
5

Import List
1
1
1
3
4
4
5

The lists are very long and are just direct comparisons. What I ended up doing after importing the lists into access I created a many to one relationship between the Import list and the Master List and created a query that basically shows List1.No and List2.No. This list is over 40000 entries so I was trying to find a way to only show entries where the Master list is blank. Under criteria I put "Is Null" and it seemed to work but I only got two entries that show a difference.

Now either this Import files doesnt have that many discrepancies or there is something wrong. Let me know if I didnt describe the issue well enough and I will try to describe it better. Thanks for the responses.

DietarySupplement

So are you trying to find things that are in the master list that aren't in the import list? Or vice-versa?

Jubal77

Trying to find things that are listed in the import list that dont exist in the master list. I think it works as I got many more discrepancies in the other lists.

DietarySupplement

Do both tables have the same data element? Same data type? Then try this:

SELECT * FROM [import list table name] WHERE [field] NOT IN (SELECT [field] FROM [master table])

Elvenshae

DietarySupplement wrote: »

Do both tables have the same data element? Same data type? Then try this:

SELECT * FROM [import list table name] WHERE [field] NOT IN (SELECT [field] FROM [master table])

Or, if you're more of a GUI guy ...

Open up a new query, and bring in both tables.

Drag the field from the import list over to the appropriate field in the master list, creating a join. Double click on the line, and select "Include ALL records from 'Import_List' and only those records from 'Master_List' where the joined fields are equal."

Then, bring the information from the Import_Table you'd like to review into the query, and the appropriate field from the master list. In the criteria, for the master list's field, type "Is Null."

Assuming you've named your tables "Master_List" and "Import_List," and that the field you care about is titled "Name_," in MS Access SQL code you'll have:

SELECT Import_List.Name_, Master_List.Name_
FROM Import_List LEFT JOIN Master_List ON Import_List.Name_ = Master_List.Name_
WHERE (((Master_List.Name_) Is Null));

You can obviously modify it from there.

Given your example, if you want the query to spit out the answer:

4

... you'll need to change it to a Group By query. If, instead, you want

4
4

... you don't need to change anything.

Jubal77

Bah now I remember why I hate Access. It would be so much easier if you could just run a query in a terminal based tool. Thanks for the help.

SiliconStew

Um, if you have two sheets in excel with a common key column you can use the vlookup() function to compare them. Say Sheet1-ColumnA and Sheet2-ColumnA are your key columns. In Sheet1-ColumnB add this formula: =vlookup(A1,Sheet2!A:A,1,false). Just fill down that column and any entries with "#N/A" are the values in Sheet1 that do not have a corresponding entry in Sheet2. Just make sure you sort both pages by the key columns before you run the formula or you could end up with incorrect results. No need to involve SQL queries for this.

Jubal77

Thanks Silicon I will check that out. Does it matter if one column would have repeating entries?

SiliconStew

Jubal77 wrote: »

Thanks Silicon I will check that out. Does it matter if one column would have repeating entries?

If your Master list column has repeating entries, the formula will return the value for the first row it finds. It doesn't matter if the Import list has multiple entries (assuming that the Import list is Sheet1 in my example).

Elvenshae

SiliconStew wrote: »

Just make sure you sort both pages by the key columns before you run the formula or you could end up with incorrect results. No need to involve SQL queries for this.

You don't need to sort them, actually*, as long as the last argument in your VLOOKUP function is "False."

"False" forces an exact match, and searches the entire list top-to-bottom until it finds one.

"True" forces a closest match, and requires a sorted master list to avoid errors. It first looks for an exact match, skims down the list from top to bottom, and stops once it moves "alphabetically" past the thing you're looking for.

So, if you've got a master list that goes:

1
3
4
5
1.5

And you're searching for "2", setting the last argument in the VLOOKUP function to False will return "#N/A," and setting it to True will return "1".

* And, in actuality, you'll almost never need to sort the import list.

Jubal77

SiliconStew wrote: »

Jubal77 wrote: »

Thanks Silicon I will check that out. Does it matter if one column would have repeating entries?

If your Master list column has repeating entries, the formula will return the value for the first row it finds. It doesn't matter if the Import list has multiple entries (assuming that the Import list is Sheet1 in my example).

Thanks again. This method is much easier and worked like a charm. Now time to update all these master lists.