Help with field

[SF]

Hi,

I have a table for storing election result as below

CEID Autonumber
CE_CommuneID > Foreign Key to Commune Table
CE_PartyID Number Foreign Key to Party Table
CE_NumberOfVote Text
CE_NumberOfSeat Text

I use Text filed type for CE_NumberOfVotes and CE_NumberOfSeat as I want
also to display "No Candidate" in place of CE_NumberOfVote. Now I am facing
a problem of summarize the NumberOfVote and NumberOfSeat.

Could someone advice on the table structure

SF

 

[Stefan Hoffmann]

hi,

CE_NumberOfVote Text
CE_NumberOfSeat Text
I use Text filed type for CE_NumberOfVotes and CE_NumberOfSeat as I want
also to display "No Candidate" in place of CE_NumberOfVote. Now I am facing
a problem of summarize the NumberOfVote and NumberOfSeat.

Make it a Long Integer field and allow Null. If you want to store "No
Candidate" store Null, or if you need Null to indicate no entry, use a
magic number, e.g. -666.

So you can sum it

SELECT Sum(CE_NumberOfVote)
FROM Table
WHERE NOT IsNull(CE_NumberOfVote)

or

SELECT Sum(CE_NumberOfVote)
FROM Table
WHERE CE_NumberOfVote <> -666


mfG
--> stefan <--

 

[SF]

It works, Thank you

SF

hi,

Make it a Long Integer field and allow Null. If you want to store "No
Candidate" store Null, or if you need Null to indicate no entry, use a
magic number, e.g. -666.

So you can sum it

SELECT Sum(CE_NumberOfVote)
FROM Table
WHERE NOT IsNull(CE_NumberOfVote)

or

SELECT Sum(CE_NumberOfVote)
FROM Table
WHERE CE_NumberOfVote <> -666


mfG
--> stefan <--