Access Help Please - re: query results

[mcmba]

Hi! I am hoping to get some guidance from the access experts.

I have a monthly data load spreadsheet that I receive and it has 6 columns.
Cat Code 1
Cat Code 2
Cat Code 3
Cat Code 4
Cat Code 5
Cat Code 6

Before I can load the data into my system, I have to see if there is any new information within the each of the 6 columns. So I have to compare each of the 6 columns (which are category codes) from the current month to the previous month, to look for anything that is new within the columns.

Currently have 6 different Access queries set up to run to compare, query one for Cat code 1, query 2 for cat code 3....etc. I cannot combine them into one query because if there is new data in cat code 6, in the combo query it shows me the data in Cat Codes 1-5 also and I can't tell which is new.

Is there an easier way to do this? If so, please let me know. It's easier than excel to do this work because each monthly file is tens of thousands of lines long. But, it's cumbersome having to run 6 queries also.

Thanks in advance for your advice!!!

 

[Joe4]

What you have here is a design issue, specifically your table structure is not normalized (see here for rules on normalization: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx).

The six different columns you mentioned should be replaced with these two:

Cat Code Number
Cat Code Value

Structuring your table like this will make queries much easier to accomplish and remove the redundancies you are experiencing.