Hi, Am sorry am posting a huge message, but is required as have to explain 

the database desing before i ask for help.



Tables



1.State



StateCode

StateName

StateSeats



Data

StateCode	StateName	StateSeats

01		AAA		100

02		BBB		112





2.StateAlliances



StateCode

AllianceCode

PartyCode



Data

StateCode	AllianceCode	PartyCode

01		XYZ		SSS

01		XYZ		DDD

01		ABC		AAS

02		XYZ		SSS





3.Constituency



ConstCode

ConstName

StateCode

ValidVotes



Data

ConstCode	ConstName	StateCode	ValidVotes

01001		ASDFG		01		10000

01002		SSDDE		01		20000





4.Candidate



ConstCode

CandID

CandName

PartyCode

CurrVotes



Data

ConstCode	CandID	PartyCode	CurrVotes

01001		1	SSS		1111

01001		2	DDD		1000

01001		3	ZZZ		99

01002		4	DDD		10000

01002		5	SSS		9999

01002		6	XYZ		888







5.VotesHistory



ConstCode

Year

Party

HistVotes

TotalVotes





Data



ConstCode	Year	Party	HistVotes	TotalVotes

01001		1998    SSS	11965		60774

01001		1998    XYZ  	872		60774

01001		1998	ABC     604		60774



01001		1999    ABC	11000		60774

01001		1999    XYZ  	999		60774

01001		1999	ABC     500		60774







6.Setup



StateCode

ComparisionYear



Data



StateCode	ComparisionYear

01		1999

02		1998





Now the problem



Am developing a election reports system, as you would have guessed looking 

at the tables above, what I want to do is for a particular State (say 

statecode=01) find the %Vote and %Change for each alliance within that 

state (Alliances in a State I get from StateAllinaces Table), Now if there 

is a alliance XYZ contesting in constcode 01001 (Candidate Table), as you 

can see in the data example given by me above that from Alliance XYZ both 

the allies SSS and XYZ are contesting in constituency 01001, so what i 

have to do is see that which of the allie is leading in the said 

constituency and then calculate %vote and %Change for that allie in that 

constituency and so on in all the constituencies individually, if there is 

only one party standing in a constituency from a alliance then we 

obviously use that party only. this i have to do for each alliance within 

a state, for all Constituency where the said alliances are contesting)



To Calculate %Change what i have to do is go to VotesHistory Table and 

pick up the HistVotes and TotalVotes for the alliance in the same 

constituency for which we are calculating %Vote in the year mentioned as 

ComparisionYear in Setup Table for that State.



One thing i should tell you that all constituencies in State 01 will start 

with 01 for example 01001,01002,01003 and in State 02 would be 02004,02007 

and so on.





The Formula to calculate %Vote is (CurrentVotes for a alliance in a 

constituency <which is CurrVotes from Candidate Table>

/ ValidVotes <which is ValidVotes from Constituency Table> in that 

Constituency ) *100



The Formula to calculate %Change is ( %Vote - ( HistoryVote for the 

alliance in that Constituency / TotalVotes )*100)



The Final %Vote would be %Vote / number of constituencies where the said 

alliance is contesting from.

Similarly The Final %Change would be %Change / number of constituencies 

where the said alliance is contesting from.





In a procedural way this is how i would do it 







    SQL = "Select StateCode from State"

    CR.Open SQL, cn

    

    Do While Not CR.EOF

        SQL1 = "Select distinct alliancecode from statealliance where 

statecode='" & CR!StateCode & "' order by AllianceCode"

        CRs.Open SQL1, cn

                

        Alliance = 0

        AllianceVotes = 0

        ValidVotes = 0

        AllianceHistVotes = 0

        HistTotalVotes = 0

        VoteShare = 0

        VoteChange = 0

        HistVS = 0

        

        Do While Not CRs.EOF

            SQL2 = "Select constcode,ValidVotes from constituency where 

statecode='" & CR!StateCode & "'"

            CrsTemp.Open SQL2, cn

            i = 1

            Do While Not CrsTemp.EOF

                sql3 = "select curvotes, max(curvotes) AlliVotes from 

candidate where constcode='" & CrsTemp!constcode & "' and PartyCode in

(select partyCode from statealliance where alliancecode='" & CRs!

AllianceCode & "') group by curvotes"

                CrsTemp1.Open sql3, cn

                Do While Not CrsTemp1.EOF

                    AllianceVotes = AllianceVotes + CrsTemp1!AlliVotes

                    ValidVotes = ValidVotes + CrsTemp!ValidVotes

                    VoteShare = VoteShare + ((AllianceVotes / ValidVotes) 

* 100)

                    CrsTemp1.MoveNext

                Loop

                CrsTemp1.Close

                CrsTemp.MoveNext

                i = i + 1

            Loop

            CrsTemp.Close

            VoteShare = VoteShare / i

            MsgBox CR!StateCode & ", " & CRs!AllianceCode & ", " & 

VoteShare

            

            CRs.MoveNext

            

        Loop

        CRs.Close

        CR.MoveNext

    Loop





Can Somebody help



Thankyou



Sankalp