0

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.

iShaymus
  • 512
  • 7
  • 26

1 Answers1

2

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;
Nate Anderson
  • 690
  • 4
  • 20
  • 1
    Don't think access supports case in SQL; they call it switch or something: https://stackoverflow.com/questions/14920116/does-ms-access-support-case-when-clause-if-connect-with-odbc – xQbert Jul 12 '17 at 16:23
  • Got a circular referece error with the 'AS HomePhone' on the end. Removed that AS from both statements and it worked perfectly. Many thanks. – iShaymus Jul 13 '17 at 14:05