Problems with Access 2003 Wizard

September 2, 2008 2:06 PM

Access 2003 UnMatched Query Wizard is not returning expected values.

I am attempting to use Access 2003 to find unmatched records between two tables. The story so far...

Table_A and Table_B both contains account numbers, names, addresses, etc for customers. My task is to compare the two tables and produce a list of customers from Table_B that are not in Table_A. Table_A is about 2mb and Table_B is about 200k.

Initially, I had the data in .xls and used the import wizard to bring the data into a fresh Access database, creating two tables. I tried to use the UnMatched query wizard to produce a list of customers. It returned no matches. However I *know* there are matches. I then tried different formats for the data (.txt & .csv) but received the same results.

To test the problem, I created two tables inside Access and used the UnMatched wizard on them. That process produced expected results; it found the no-matches records.

I then created two small versions of Table_A & Table_B (less than 50 records each), imported them, and ran the UnMatched wizard. That process also produced the expected results; it found the no-match records.

Anyone have any thoughts on this? It seems I'm running into some kind of record limit but I can't find any reference to that sort of problem in Google-Land.

(Office 2003, with SP2 installed)

Thanks!

SandPine

posted by sandpine to computers & internet (8 comments total)

Alternative:
1) Add a flag column to Table B;
2) Set all values of flag to FALSE ("No" in Access);
3) Construct a query that finds all Table B customers that ARE in Table A also;
4) Change the query to update Table B's flag column to TRUE;
5) Now go to Table B and filter for flag=FALSE;
posted by blue_wardrobe at 2:34 PM on September 2, 2008

Troubleshooting:
Are you linking the xls/txt/csv files or are you actually importing them as fresh tables?
Are you sure the data types are the same between the relevant fields of the tables?
What field are you using to compare? Is it a unique ID? Is that unique ID the same data type in both tables?

Access can handle more records than Excel, so if your data fits into an Excel workbook, Access should have no trouble with it.

The easiest/fastest way to get your list would be to keep it in Excel and use the VLOOKUP function to find all the matches between your tables. Then filter the matches out, and voila, you have the unmatched records.
posted by desjardins at 4:07 PM on September 2, 2008

If you need help with VLOOKUP, send me a memail. If you really want to do this in Access (i.e., if this is going to be an ongoing task), then follow blue_wardrobe's suggestion.
posted by desjardins at 4:09 PM on September 2, 2008

You're (probably) not running into a row limits, you've probably got data that contains whatever column separator you're using.

As your test showed (good thinking), the bad data's somewhere after line 50.

Export the data as CSV making sure you specify that the data be quoted, and re-import.

Then run the following queries:

select count(*) from table_a;

select count(*) from table_b;

(Obviously, replacing the table names with the real table names.)

Make sure you have all the rows you expect. If you don't, import half the rows, and do the count again, to determine if the bad data is in the first half of the data or the second half (it may be in both). Keep halving the size of the "bad half" and repeating the import, until you can figure out the row(s) with bad data. Correct the bad data, and repeat from the top.
posted by orthogonality at 4:17 PM on September 2, 2008

If you look at the query generated by the Unmatched wizard, it'll give you the idea of how to do this and how to tinker with it if it's not giving you what you want.

Basically, the two tables need to be joined on a unique identifier. However, it's the *type* of join that is where the action is. Instead of using a regular (equi) join, which returns records where both tables have values, you want to use a left or right join, which, in Access, is represented by an arrow on the join line in the query by example grid. Right click on the join line between the two tables in your query, choose Join Properties, and you'll see the options. Choose the one which displays all the values in TableB, but only the matched values in TableA.

Then, in your query grid pull down the unique identifier twice - once from TableA and once from TableB. Have as your criteria that the identifer in TableA "is null" and that should return ID values where TableB has a record but TableA doesn't.

As desjardins says, there should be no record limit issue. It's probably a linking or datatype issue that's occuring during the import. When you did your test run did you use the import wizard also?
posted by jasper411 at 4:26 PM on September 2, 2008

Just a quick thanks to everyone who posted a response. I'm working my way through the suggestions and will post my results later in the day.

Once Again THANKS !

SandPine
posted by sandpine at 7:31 AM on September 3, 2008

sandpine - I got your email. Bizarrely, I can read my gmail at work but cannot reply to it.

Anyway, here's a simple explanation of VLOOKUP with an example image.

Table A is your big list of customers and Table B is your small list. (In the example, they're really really small, but I regularly do this with huge lists all the time.)

Make sure both tables are open. In Table B, next to the ID field, insert a column. In the second row of that column, type the formula in the example image. In the example, A2 is referencing the first cell that contains the ID# (i.e., what you want to compare). [TABLE_A.csv] TABLE_A!$A:$A references the entire column in Table A that you want to compare (i.e., the column containing the IDs). Don't worry what the 1,0 does right now, but put it in there.

Drop the formula all the way down the column you've inserted. Anything from table B that's not in table A will show up as N/A. Filter that column (or just sort it) and there you have your list of unmatched customers - in the example, Mike Hernandez and Barbara Martin.
posted by desjardins at 2:11 PM on September 3, 2008

Thanks for the reply and example. This is something I can also use elsewhere.

As far as the original problem goes, it turned out to be a data problem in the
end. Not anything to do with Access at all.

SandPine
posted by sandpine at 7:59 AM on September 8, 2008