CODEWITHSUNDEEP
sqlms-access
iShaymus
iShaymus

Reputation: 532

SQL - Alter query output based a boolean in the record

I have an MS Access table that contains staff contact information, staff maintain their own records, anyone can access the database. I need two queries that return a phone list. The first just simply returns all the names and numbers.

SELECT LastName, FirstName, HomePhone, MobilePhone FROM StaffDetails ORDER BY ASC;

For the second, there is a boolean column in the table call PrivateNumbers. If the staff members have set their numbers to be private I would like the query to label them as such. So, can I write an SQL query that will look at the boolean value and then change HomePhone and MobilePhone to "Private" in the query result?

The theory is that if any staff look at the phone list the private numbers are listed as such. If management look then they see it all.

Upvotes: 0

Views: 52

Answers (1)

Nate Anderson
Nate Anderson

Reputation: 690

You'll have to test this, as I don't have access to your database, but adding a case statement to your sql solve your problem.

SELECT 
    LastName
    ,FirstName
    ,IIF(PrivateNumbers, 'Private',HomePhone) AS HomePhone
    ,IIF(PrivateNumbers, 'Private',MobilePhone) AS MobilePhone 
FROM StaffDetails 
ORDER BY ASC;

Upvotes: 2

Related Questions