vacation query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report off of a query that shows people on vacation, it only shows
people from certian dept numbers. It asks for a date then shift, on 2nd and
3rd shift i want it to show different depts then the fist shift, what code do
i put in my query to do this? I already have this in my dept column: Not
In('03','04','05','15'). For our 2nd &3rd shifts i want 04 to be counted.
Any ideas?

thanks
 
Dear Dean:

I guess the criteria might read:

WHERE (Shift = 1 AND Dept IN('03', '04', '05', '15')
OR (Shift IN(2,3) AND Dept IN('03', '05', '15'))

Change the column names to match what you are using.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Sorry, I left off the NOT.

WHERE (Shift = 1 AND Dept NOT IN('03', '04', '05', '15')
OR (Shift IN(2,3) AND Dept NOT IN('03', '05', '15'))

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I put that in there but now it says syntax error i may have entered an
operand with out an operator?


Thanks
Dean
 
Dear Dean:

It would be easier to try to pinpoint the problem if you'd post the
SQL you now have so we can see it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,
Here is what i have now:
SELECT tblVacation.REQUEST_DATE, tblVacation.NO_DAYS,
tblEmployeePasswords.SHIFT, tblVacation.SENIORITY_NUMBER,
tblEmployeePasswords.FIRST_NAME, tblEmployeePasswords.LAST_NAME,
tblEmployeePasswords.DEPT, tblVacation.DATE_SIGNED
FROM tblEmployeePasswords INNER JOIN tblVacation ON
tblEmployeePasswords.SENIORITY_NUMBER = tblVacation.SENIORITY_NUMBER
WHERE (((tblVacation.REQUEST_DATE)=[EnterDate]) AND
((tblEmployeePasswords.SHIFT)=[EnterShift]) AND ((tblEmployeePasswords.DEPT)
Not In ('03','04','05','15')))
ORDER BY tblVacation.REQUEST_DATE, tblVacation.NO_DAYS,
tblEmployeePasswords.SHIFT, tblVacation.SENIORITY_NUMBER;

When i put in your code i get an error, am i missing something?
thanks for your help!!

Dean
 
Dear Dean:

SELECT V.REQUEST_DATE, V.NO_DAYS, E.SHIFT, V.SENIORITY_NUMBER,
E.FIRST_NAME, E.LAST_NAME, E.DEPT, V.DATE_SIGNED
FROM tblEmployeePasswords E
INNER JOIN tblVacation V
ON E.SENIORITY_NUMBER = V.SENIORITY_NUMBER
WHERE V.REQUEST_DATE = [EnterDate] AND E.SHIFT = [EnterShift])
AND (E.SHIFT = 1 AND E.DEPT IN('03', '04', '05', '15')
OR (E.SHIFT IN(2,3) AND E.DEPT IN('03', '05', '15'))
ORDER BY V.REQUEST_DATE, V.NO_DAYS, E.SHIFT, V.SENIORITY_NUMBER;

I have used aliases for the tables to make this a bit shorter and
incorporated the query fragment I provided within your pre-existing
logic. My statement included a WHERE which you already had, and you
cannot have WHERE twice. I'm just guessing this may have been part of
the problem.

Does this get it for you?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,
Here is what i have now:
SELECT tblVacation.REQUEST_DATE, tblVacation.NO_DAYS,
tblEmployeePasswords.SHIFT, tblVacation.SENIORITY_NUMBER,
tblEmployeePasswords.FIRST_NAME, tblEmployeePasswords.LAST_NAME,
tblEmployeePasswords.DEPT, tblVacation.DATE_SIGNED
FROM tblEmployeePasswords INNER JOIN tblVacation ON
tblEmployeePasswords.SENIORITY_NUMBER = tblVacation.SENIORITY_NUMBER
WHERE (((tblVacation.REQUEST_DATE)=[EnterDate]) AND
((tblEmployeePasswords.SHIFT)=[EnterShift]) AND ((tblEmployeePasswords.DEPT)
Not In ('03','04','05','15')))
ORDER BY tblVacation.REQUEST_DATE, tblVacation.NO_DAYS,
tblEmployeePasswords.SHIFT, tblVacation.SENIORITY_NUMBER;

When i put in your code i get an error, am i missing something?
thanks for your help!!

Dean

Tom Ellison said:
Dear Dean:

It would be easier to try to pinpoint the problem if you'd post the
SQL you now have so we can see it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
Click to expand...
 
Back
Top