Help with field

  • Thread starter Thread starter SF
  • Start date Start date
S

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
 
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.
Click to expand...
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 <--
 
It works, Thank you

SF

Stefan Hoffmann said:
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 <--
Click to expand...
 
Back
Top