TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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 :-)
Reply
Answers (
4
)
Images from back end to front end
Subquery returns more than 1 row