If statements

[Bob Griendling]

I'm designing a report based on a query. The report lists precincts and how
two candidates fared in each:

Precinct
cand A # votes
cand B # votes

I wrote a query that gives me the total for each candidate for all
precincts.

But I want another column next to each precinct report that has a 1 or other
marker when cand B vote totals exceeds cand A. And then at the end of the
report (in my sub report in the report footer) I want a total number of
precincts where cand B beat cand A.

Any ideas how I do that?

Bob

 

[Al Camp]

Bob,
Add 2 calculated fields to your query behind the report. (perhaps 3 if you expect ties)
CandAWins : IIF(CandAVotes > CandBVotes, 1, 0)
CandBWins : IIF(CandBVotes > CandAVotes, 1, 0)
Ties : IIF(CandBVotes = CandAVotes, 1, 0)
Now place those "bound" calculated fields on your report, and Sum them in the
ReportFooter.
=Sum(CandAWins) etc... for all three.

 

[Bob Griendling]

Thanks. Here's what I wrote in the query:

IIF(T M Kaine (D)) [Votes Recd] > (J W Kilgore (R))[Votes Recd]), 1)

(T M Kaine (D) represents the exact syntax of the candidate field and [Votes
Recd] is the exact field name.

I did so because I only want a "1" if Kaine wins.

But the dialog box I get says the "expression has the wrong number of
arguments."

What does that mean?

Bob

 

[Al Camp]

Bob,
This problem is more difficult than it appears...
Sounds like your table design is "basically" like this...
Precinct Candidate VotesRcvd
2 B Smith 20
2 J Jones 30
I think this one case where a normal one to many realtionship would be difficult to
work with. Since the VotesRcvd are in a common field, a direct IIF comparison won't work.
Particularly if there might be more than 2 candidates!

I'd suggest a flat table...
Main table...
ElectionID ElectionDate ElectionName CandA CandB
142 1/1/06 2006 Mayoral Smith Jones
Sub table...
ElectionID PrecinctNo AVotes BVotes
142 12 20 14
142 13 34 21

I'd also lay the candidate fields horizontally across the report, and add down the
column.
Now, adding AWin and BWin columns to your report query...
AWin : IIF(AVotes>BVotes,1,0)
BWin : IIF(BVotes>AVotes,1,0)
then add Awins and BWins (may be made invisible) in the the appropriate report footer.
(probably ElectionID or the report footer)
You need the 0 in the loser column to avoid nulls.

While the flat table design appears to be a bit clumsy, I think it may be much easier
than using a common candidate and vote field for all candidates.

Please feel free to post a new question regrading... "How do I set up a voting
database". Explain your data fields and needs. (I couldn't find anything in the Google
groups)
***While my solution will work... I think I'd like some other folks to take a look at
this problem. (not so much the IIF, but the table layout and mechanics)

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Thanks. Here's what I wrote in the query:

IIF(T M Kaine (D)) [Votes Recd] > (J W Kilgore (R))[Votes Recd]), 1)

(T M Kaine (D) represents the exact syntax of the candidate field and [Votes Recd] is
the exact field name.

I did so because I only want a "1" if Kaine wins.

But the dialog box I get says the "expression has the wrong number of arguments."

What does that mean?

Bob

Bob,
Add 2 calculated fields to your query behind the report. (perhaps 3 if you expect
ties)
CandAWins : IIF(CandAVotes > CandBVotes, 1, 0)
CandBWins : IIF(CandBVotes > CandAVotes, 1, 0)
Ties : IIF(CandBVotes = CandAVotes, 1, 0)
Now place those "bound" calculated fields on your report, and Sum them in the
ReportFooter.
=Sum(CandAWins) etc... for all three.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions