Solved a Problem in Access

I’ve been working a bit with Access the last few months because I’m working with a set of data that is huge. Huge as in I originally had to break up the problem into separate Excel spreadsheets in order to arrive at a smaller data set. Once I got Access, it cut that problem out pretty well.

But, I did encounter another kind of problem with Access that was causing me grief: joining two tables. Every time I joined tables, I would seemingly create additional rows of data so that the dollar amount would increase. For example, when I would join the main data table with another table with the latest email addresses, I would get more rows and the dollars increased.

I finally did a reconciliation by downloading first the core table and then downloading the data with additional rows. In each downloads, I inserted a column, which eventually became column B in the second download, so that I could make a “key” column which basically had a formula =C2&D2&E2…&AT2&AU2. In the download with additional rows, I added another column (column A) that would count up how many rows had the same “key”: =countif(B2:B50000,+B2). By filtering on column A for counts over 1 and then comparing those “keys” against the original core table download, I was able to identify the source of my problem.

It turns out that my table with email addresses listed the same names/ssn twice so these names would cause Access to list them twice in the main data. So when you do a join, be careful that one of the tables have data listed once as a unique item or you will find Access creating new rows of data.