Switch function

[Svekke]

Hi all,

I am currently working on a project that "calculates" scores. I currently have 4 tables (I have more, but I don't need them now):

PARTICIPANTS tables
(
participantID (primary key, autonumber)
....
)

PROGNOSTIC table
(
prognosticID (primary key, autonumber)
MatchID (foreign key to MATCHES, number)
ParticipantID (foreign key to PARTICIPANTS, number)
HomeScore (number)
OutScore
)

MATCHES table
(MatchID (primary key, autonumber)
TournamentID (foreign key to tournaments)
HomeScore
Outscore
)

SCORES table
(ScoreId (primary key, autonumber)
Matchid (foreign key to MATCHES)
ParticipantID (foreign key to PARTICIPANTS)



The idea is to do the following:

1) Delete from SCORES where MatchID in (select matchid from matches where tournamentID = 1) (i can do this)
2) Insert into SCORES (Participant, Match, Score)
select a.participantID, b.Matchid,
case when a.homescore = b.homescore and a.outscore = b.outscore then '10'
when a.homescore > a.outscore and b.homescore > b.outscore then '5'
.....
from MATCHES a, PROGNOSTICS b
where a.matchid = b.matchid


However, the code above would work in Oracle, but apparantly in access it does not. I have searched the internet, but I only come across "Switch" or "Iif" functions. Doing the above with an if function seems like A LOT of work, and the Switch function doesn't work the way I hoped it does

I tried the following:

Select Switch(a.homescore = b.homescore, 1, 0) from .... but that already gave an error. It just displays #FOUT (fout = error in Dutch) in the outcome.

Does anyone have an idea on how to handle this? I'm just a newbie in VB.NET/ACCESS

 

[Estuardo]

G'd morning Svekke,
The error might be because of your sintax. The Switch function accepts nested conditions, you may want to try something like this:

SELECT SWITCH([a.homescore]=[b.homescore] AND [a.outscore]=[b.outscore],"10"), SWITCH([a.homescore] > [a.outscore] AND [b.homescore] > [b.outscore],'5')

The switch function is not your only one option, you can have an hybrid. You can build your own Public Function in a module and use it in your query, or do the whole insert process from code.
G'd luck