?
Solved

Conditionals in SQL

Posted on 2011-10-30
15
Medium Priority
?
394 Views
Last Modified: 2012-05-12
I have the following SQL statement (in the code box below) that is used as the record source of a listbox

The columns in the listbox are:

EventID (not displayed), Member (ID), Last Name, First Name, Address Line 1, Town/City, Postcode/Zip, Balance to Pay, Status (not displayed), B&B Ref (not displayed), Booking ID (not displayed) and Reserved (Boolean – not displayed)

For all Bookings the listbox shows the records but on occasions an Event is more popular than the available spaces.  In these circumstances the application allows Reserved bookings to be taken.  The tJDWBookings.jdwb_Reserved field is True if the ‘booking’ is ‘Reserved’.

Ideally I want to make Reserved bookings look different in the listbox

Normally a line in the listbox looks like:

7464      Smith      Frank      119 High Street      Bath      BA1 2LX      £195.00

I would like ‘Reserved bookings' to look like this in the linebox

7481      Jones      Anne      1 Long Road      Ware      SG12 1JU      RESERVE

As ‘Reserved’ bookings do not have any financial data there is no Balance to Pay so the extreme right column that normally shows a balance would always show £0.00 for 'Reserved bookings'.

How do I rewrite the SQL statement to achieve this please?

SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, tJDWBookings.jdwb_Reserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND((tJDWBookings.jdwb_Status)='OK'));

Open in new window

0
Comment
Question by:MikeDTE
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 11

Expert Comment

by:dougaug
ID: 37053883
if you are using ms sql server, see if this works:

SELECT tJDWBookings.jdwb_Event,
tJDWBookings.jdwb_Member,
tContact.con_LastName,
tContact.con_FirstName,
tContact.con_Address1,
tContact.con_TownCity,
tContact.con_PostcodeZip,
case when tJDWBookings.jdwb_Reserved then 'RESERVED'
                                     else convert(varchar, tJDWBookings.jdwb_Balance, 1)
end [jdwb_Balance],
tJDWBookings.jdwb_Status,
tJDWBookings.jdwb_BB,
tJDWBookings.jdwb_BookingID,
tJDWBookings.jdwb_Reserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND((tJDWBookings.jdwb_Status)='OK'));
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37053895
In Access, it would look like the following.  This adds another field to the recordset, but you could just as easily delete the balance and Reserved columns, once you have created this computed column.

SELECT tJDWBookings.jdwb_Event,
tJDWBookings.jdwb_Member,
tContact.con_LastName,
tContact.con_FirstName,
tContact.con_Address1,
tContact.con_TownCity,
tContact.con_PostcodeZip,
IIF(tJDWBookings.jdwb_Reserved, 'RESERVED', Format(tJDWBookings.jdwb_Balance, "currency") as ResOrBal,
tJDWBookings.jdwb_Balance,
tJDWBookings.jdwb_Status,
tJDWBookings.jdwb_BB,
tJDWBookings.jdwb_BookingID,
tJDWBookings.jdwb_Reserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND((tJDWBookings.jdwb_Status)='OK'));
0
 

Author Comment

by:MikeDTE
ID: 37053925
Hi

It is Access SQL as fyed noted so I tried his SQL statement but I get an missing operator syntax error in teh IIF statement.  It indicates the 'as' as being the problem.

I just pasted the SQL code into the access Query builder (Access 2003) and tried to view in Datasheet view.
SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, IIF(tJDWBookings.jdwb_Reserved, 'RESERVED', Format(tJDWBookings.jdwb_Balance, "currency") as ResOrBal, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, 
tJDWBookings.jdwb_Reserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON JDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND((tJDWBookings.jdwb_Status)='OK'));

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 37053984
I missed a closing ")", add it after "Currency"

SELECT tJDWBookings.jdwb_Event
, tJDWBookings.jdwb_Member
, tContact.con_LastName
, tContact.con_FirstName
, tContact.con_Address1
, tContact.con_TownCity
, tContact.con_PostcodeZip
, IIF(tJDWBookings.jdwb_Reserved, 'RESERVED', Format(tJDWBookings.jdwb_Balance, "currency")) as ResOrBal
, tJDWBookings.jdwb_Balance
, tJDWBookings.jdwb_Status
, tJDWBookings.jdwb_BB
, tJDWBookings.jdwb_BookingID
, tJDWBookings.jdwb_Reserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON JDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND((tJDWBookings.jdwb_Status)='OK'));
0
 

Author Comment

by:MikeDTE
ID: 37054002
Hi fyed

I am now getting a Sytax error in JOIN operation

FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON JDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37054100
See if this does what you need -

SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, "RESERVED" AS isReserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE tJDWBookings.jdwb_Event =[Forms]![fJDWEvents]![jdwe_EventID] ANDtJDWBookings.jdwb_Status ='OK' AND tJDWBookings.jdwb_Reserved = True
0
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 37054120
Or if you need them all in the same listbox:

SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, iif(tJDWBookings.jdwb_Reserved = True
, "Reserved", tJDWBookings.jdwb_Reserved)
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE tJDWBookings.jdwb_Event =[Forms]![fJDWEvents]![jdwe_EventID] AND tJDWBookings.jdwb_Status='OK'


(Not sure why Fyed's statement didn't work)
0
 

Author Comment

by:MikeDTE
ID: 37054125
Hi mbizup

Your statement had a rogue bracket which I removed:

SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, "RESERVED" AS isReserved
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE tJDWBookings.jdwb_Event=[Forms]![fJDWEvents]![jdwe_EventID] AND tJDWBookings.jdwb_Status ='OK' AND tJDWBookings.jdwb_Reserved = True

What this does is isolate the reserved booking rather than showing all bookings with a different final colum

Fyed's solution is almost there!
0
 

Author Comment

by:MikeDTE
ID: 37054142
Hi mbizup

Yes - your second solution works like a (my) dream!

Thanks once again

Regards
Mike
0
 

Author Comment

by:MikeDTE
ID: 37054179
Precipitate

... it works ... nearly

The idea is to have one common column that holds either the balance due or the work 'RESERVE' - the latter in the case of a Reserved booking.

In mbizup's statement the Expr1 column contains only '0' or RESERVE and column jdwb_Balance shows the balance only.

fyed's follwing IIF statement needs incorporating into mbizup's statement.

IIF(tJDWBookings.jdwb_Reserved, 'RESERVED', Format(tJDWBookings.jdwb_Balance, "currency")) as ResOrBal







SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID, IIf(tJDWBookings.jdwb_Reserved=True,"RESERVE",tJDWBookings.jdwb_Reserved) AS Expr1
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE (((tJDWBookings.jdwb_Event)=[Forms]![fJDWEvents]![jdwe_EventID]) AND ((tJDWBookings.jdwb_Status)='OK'));

Open in new window

0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37054216

Try this FROM/WHERE clause

FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId)
INNER JOIN tJDWEvents ON JDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE tJDWBookings.jdwb_Event=[Forms]![fJDWEvents]![jdwe_EventID]
AND tJDWBookings.jdwb_Status='OK'

0
 

Author Comment

by:MikeDTE
ID: 37054233
OK Guys

By combining fyed's and mbizups statements I have arrived at th following - and it works!
SELECT tJDWBookings.jdwb_Event, tJDWBookings.jdwb_Member, tContact.con_LastName, tContact.con_FirstName, tContact.con_Address1, tContact.con_TownCity, tContact.con_PostcodeZip, IIF(tJDWBookings.jdwb_Reserved, 'RESERVE', Format(tJDWBookings.jdwb_Balance, "currency")) as ResOrBal, tJDWBookings.jdwb_Balance, tJDWBookings.jdwb_Status, tJDWBookings.jdwb_BB, tJDWBookings.jdwb_BookingID
FROM (tJDWBookings INNER JOIN tContact ON tJDWBookings.jdwb_Member = tContact.con_ContactId) INNER JOIN tJDWEvents ON tJDWBookings.jdwb_Event = tJDWEvents.jdwe_EventID
WHERE tJDWBookings.jdwb_Event =[Forms]![fJDWEvents]![jdwe_EventID] AND tJDWBookings.jdwb_Status='OK'

Open in new window

0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37054258
glad we could help
0
 

Author Closing Comment

by:MikeDTE
ID: 37054288
Thanks to both of you.  Once I could see how ... then the rest was just logical.  Works a treat!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37054477
Glad to help out, Mike!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how the fundamental information of how to create a table.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question