Need Help Parsing Voting Records

  • Thread starter Thread starter Douglas Thigpen
  • Start date Start date
D

Douglas Thigpen

I have a database with voter information and ten fields that tell the
elections they voted in or if they were absentee. I basically need to be
able to filter everyone who has a "V" in at least 3 out of these 10 fields
or a "A" in at least 2 out of the most recent 3 fields. Right now I'm
trying to do it by converting the values to a numerical value via the "Asc"
function and then adding them all together and going off the generated
number compared against a list of 'valid' numbers for people matching those
criteria; a bit half-arsed, and more importantly, it doesn't work, since
some voters are missing voting data and a null field will return an #Error
for the whole equation. Does anyone have a solution for this problem?

Thanks,

Douglas Thigpen
 
Hi Douglas,

The Nz() function will get you round the immediate problem: something
like
Asc(Nz([FieldName],Chr(0)))
will return 0 if the field value is Null.

A better long-term approach is to restructure the database, converting
the (at present up to) ten fields in each voter record into records in a
related table:

tblVoters
VoterID - primary key
Name etc

tblVotingRecord
VoterID ) both fields in
Year ) primary key
Record ("V" or "A")

That way, it's fairly simple to write queries to (e.g.) count the Vs or
the As in the three most recent elections - and no modification of the
structure or rewriting code is needed after the next election.
 
Thank you very much, got a working model running.

Douglas Thigpen

John Nurick said:
Hi Douglas,

The Nz() function will get you round the immediate problem: something
like
Asc(Nz([FieldName],Chr(0)))
will return 0 if the field value is Null.

A better long-term approach is to restructure the database, converting
the (at present up to) ten fields in each voter record into records in a
related table:

tblVoters
VoterID - primary key
Name etc

tblVotingRecord
VoterID ) both fields in
Year ) primary key
Record ("V" or "A")

That way, it's fairly simple to write queries to (e.g.) count the Vs or
the As in the three most recent elections - and no modification of the
structure or rewriting code is needed after the next election.

I have a database with voter information and ten fields that tell the
elections they voted in or if they were absentee. I basically need to be
able to filter everyone who has a "V" in at least 3 out of these 10 fields
or a "A" in at least 2 out of the most recent 3 fields. Right now I'm
trying to do it by converting the values to a numerical value via the
"Asc"
function and then adding them all together and going off the generated
number compared against a list of 'valid' numbers for people matching
those
criteria; a bit half-arsed, and more importantly, it doesn't work, since
some voters are missing voting data and a null field will return an #Error
for the whole equation. Does anyone have a solution for this problem?

Thanks,

Douglas Thigpen
Click to expand...
Click to expand...
 
Back
Top