Relative newbie here

[kujhawk]

Newcomer to Access…and of course, everyone needs stuff NOW!!!

I have 2 voter ID databases (combined they would be nearly 3GB in heft…hence the 2 files) that share a VoterID but no relationship set up between the two. That's not my problem…that's easy enough to straighten out.

The smaller of the 2 has columns for several primary and general elections going back several years…12Prim, 12Gen, 10Prim, 10Gen, 08Prim, 08 Gen, etc. I need to find voters who have voted in in an combination of 1, 2 or 3 out of the last 3 primaries (12Prim, 10Prim, and/or 08Prim) and then sort them in descending order…3 primaries, 2 primaries, 1 primary. And eventually sort in descending order by voting precinct, but I can work on that later.

Does this make sense? Any help is massively appreciated.

[NTC]

I would avoid attempting this type calculation in a single query and chop it into a bunch of steps: it helps give you test points. As an aside I would also make a temp test db that is a lot smaller size because if it works on 100 records it will work on a million - but the wait time during development will be a lot less.....

first find the all 3s with a : Not Is Null criteria in all 3 fields side by side
then find the 2s: with Not is Null in 2 and an Is Null in 1 : you have to do 3 versions of this one
repeat the concept with the 1s : not is null in 1 and Is Null in 2: you have to do 3 versions of this one

and so you end up with 7 queries... then you have to No Match them because the 3s are going to show up in the 2s and 1s. While the 2s are going to show up in the 1s. So that's another set of queries to set up.

I think you get the idea.... Since you are straining the size you might really consider writing the results to temp tables which is a little of a complication but will help to cut down record sizes as each step is run....