Sven

Sven

  • NA
  • 5
  • 3k

Case when equivalent in MS access using VB.NEt code

Mar 13 2012 9:15 AM
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 :-)

Answers (4)