0

I'm trying to INNER JOIN two tables: tbl_profile and tbl_employment

tbl_profile has:

    id_no (pk)
    lastName (String)
    firstName (String)

tbl_employment has:

    company_no (pk)
    id_no (fk)
    datehired (date)
    company (string)
    end_employment (date)

Each person in the profile table can have many employments or none at all in life. But I need to find the latest date_hired of that person.

These are the conditions I want to set:

  • if the date_hired of the person is not empty and the end_employment is empty, it means that the person is CURRENTLY EMPLOYED
  • if the date_hired of the person is not empty and the end_employment is also not empty, it means that the person is CURRENTLY UNEMPLOYED
  • if the date_hired of the person is empty, then it means that the person is UNEMPLOYED.

After determining the conditions above, my query will add another column named STATUS which will only state EMPLOYED or UNEMPLOYED based on the conditions above.

However, when I convert this logic into a query, it states

Syntax Error: Operator is missing.

I read a book about using when-then statement like if-else does in SELECT of a query is possible. But with my query, it doesn't work. What is wrong with this?

SELECT p.id_no, p.lastName ,
(CASE
    WHEN c.date_hired != "" AND c.end_employment = "" THEN 'employed'
    WHEN c.date_hired != "" AND c.end_employment != "" THEN 'unemployed'
    WHEN c.date_hired = "" THEN 'unemployed'
END)
AS employment_status
FROM tbl_profile AS p INNER JOIN tbl_employment AS c ON p.id_no= c.id_no;

It says that the error is within this part:

(CASE
       WHEN c.date_hired != "" AND c.end_employment = "" THEN 'employed'
       WHEN c.date_hired != "" AND c.end_employment != "" THEN 'unemployed'
       WHEN c.date_hired = "" THEN 'unemployed'    END)

P.S: I did the query in Microsoft Access

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
  • 1
    I'd say the first things that's wrong is that you try to compare NULL date values to empty string. Handle them as date values and check for NULL. – Filburt Feb 18 '18 at 08:19
  • tried it with is null and is not null. STILL NOT WORKING @Filburt –  Feb 18 '18 at 08:21
  • 2
    Why does your query use `tbl_company` while your question starts with `tbl_employment`? – trincot Feb 18 '18 at 08:22
  • @trincot sorry typo. i will correct it here. but on my ms access, its typed right and its still NOT WORKING. –  Feb 18 '18 at 08:23
  • 1
    No need to shout. What is `alumni_no, p.lname`? Those fields are not in your description. Is the first one the key? Please do an effort to make your question consistent. – trincot Feb 18 '18 at 08:25
  • I had several jobs - some of them overlapping, some of them interleaving - some starting ending after / in / between when I had others. Thats a case in your data? _Job1_ starts in Januar, Ends in Mai, _Job2_ starts in Februar, ends in _October_, Job3 starts in April, Ends in April, _Job4 starts in Februar and ends 2 years later. Employed all the time, sometims working more then 1 job. – Patrick Artner Feb 18 '18 at 08:26
  • @PatrickArtner What do you mean sir? I dont understand. I dont see backlashes in my query or in the error –  Feb 18 '18 at 08:27
  • See [coalesce alternative in Access SQL](https://stackoverflow.com/q/247858/205233) to handle NULL `date_hired`and `end_employment` values. Use something like `Nz(date_hired, your_default_date_here)`. – Filburt Feb 18 '18 at 08:30
  • @trincot oh sorry. im not shouting. sorry. and ive changed that. its still not working. im reading sql case-when-then now and i really dont find the error and what the syntax error and missing operator is. –  Feb 18 '18 at 08:31

2 Answers2

1

The error comes from != which is not a valid comparison operator in MS Access. There is <>, but although that would be valid it would not be what you need:

You are comparing dates with empty strings, which is a bit odd. I suppose your dates are stored in a column that has the appropriate date data type. You should instead check whether the value is null or not.

Also, your query will not find persons who do not have any record in tbl_employment. You need an outer join for that.

Finally, your query would not prevent you from getting multiple results for the same person: you need to group the results. I would suggest MIN so that cases where you get both employed and unemployed, employed will be the overall conclusion.

Untested (I don't have MS Access):

SELECT     p.id_no,
           p.lastName, 
           MIN(IIF(c.date_hired IS NOT NULL AND c.end_employment IS NULL,
                   'employed',
                   'unemployed'
           )) AS employment_status
FROM       tbl_profile AS p 
LEFT JOIN  tbl_employment AS c 
        ON p.id_no = c.id_no
GROUP BY   p.id_no,
           p.lastName;

Note also the change of the CASE WHEN expression to IIF: MS Access does not support the former.

Also, there is only one condition where the result is "employed": all other conditions signify unemployment, so they don't need to be verified separately.

trincot
  • 317,000
  • 35
  • 244
  • 286
  • How does `MIN` on the `case` work out? the earlier mentioned, the smaller or by `'em..' < 'un..'` due to lexicographical ordering? Probably the latter one? – Patrick Artner Feb 18 '18 at 08:37
  • Indeed, lexicographical ordering. – trincot Feb 18 '18 at 08:39
  • does not work. it still says syntax error (missing operator). is case-when-then-end even work on ms access? because the error it says is from MIN to END in your query –  Feb 18 '18 at 08:40
  • You could research that of course, but I have changed it to the non-standard `IIF` now, which I suppose MS Access supports. – trincot Feb 18 '18 at 08:43
-1

You can do it like this:

WHEN Coalesce(c.date_hired,"") != "" AND Coalesce(c.end_employment,"") = "" THEN 'employed' else 'unemployed'
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
M .Mousavi
  • 1
  • 3