Exclude dates from a query

[Dan2kx]

Good aft peeps,

i have a a working solution for this already but i suppose you could say it is a bit lazy,

I have used ADezii's calendar to display dates in my own unique way using the following SQL (using dynamic parameters of course)

SELECT tblStaff.StaffID, tblStaff.[Forename] & ' ' & [Surname] AS Name, tbl_Holidays.Date
FROM tblStaff INNER JOIN tbl_Holidays ON tblStaff.StaffID = tbl_Holidays.StaffID
WHERE (((tbl_Holidays.Date) Between #4/1/2009# And #5/1/2009#-1))
GROUP BY tblStaff.StaffID, tblStaff.[Forename] & ' ' & [Surname], tbl_Holidays.Date, tblStaff.Department, tbl_Holidays.MinutesUsed, tbl_Holidays.Accepted, tbl_Holidays.AuthON, tbl_Holidays.CancON, tblStaff.Surname
HAVING (((tblStaff.Department)="Haematology") AND ((tbl_Holidays.MinutesUsed)>0) AND ((tbl_Holidays.Accepted)=True) AND ((tbl_Holidays.AuthON) Is Not Null) AND ((tbl_Holidays.CancON) Is Null))
ORDER BY tbl_Holidays.Date, tblStaff.Surname;

what i would like to do is exclude dates which reside in a second table

i tried the following SQL and some variations i could think of as so:

SELECT tblStaff.StaffID, tblStaff.[Forename] & ' ' & [Surname] AS Name, tbl_Holidays.Date
FROM tblBlockBookD INNER JOIN (tblStaff INNER JOIN tbl_Holidays ON tblStaff.StaffID = tbl_Holidays.StaffID) ON tblBlockBookD.Date = tbl_Holidays.Date
WHERE (((tbl_Holidays.Date) Between #4/1/2009# And #5/1/2009#-1) AND ((tblBlockBookD.Date) Is Null))
GROUP BY tblStaff.StaffID, tblStaff.[Forename] & ' ' & [Surname], tbl_Holidays.Date, tblStaff.Department, tbl_Holidays.MinutesUsed, tbl_Holidays.Accepted, tbl_Holidays.AuthON, tbl_Holidays.CancON, tblStaff.Surname
HAVING (((tblStaff.Department)="Haematology") AND ((tbl_Holidays.MinutesUsed)>0) AND ((tbl_Holidays.Accepted)=True) AND ((tbl_Holidays.AuthON) Is Not Null) AND ((tbl_Holidays.CancON) Is Null))
ORDER BY tbl_Holidays.Date, tblStaff.Surname;

so the second table is the one called tblBlockBookD and holds bank holidays basically, so i dont need them in the query. my lazy man way was to simply strip off the results from the particular text box and add "bank holiday".

please tel me if i am thinking too much about this, i just want something simple, i thought it would be but apparently not.

thanks,
Dan

[ADezii]

Good aft peeps,

i have a a working solution for this already but i suppose you could say it is a bit lazy,

I have used ADezii's calendar to display dates in my own unique way using the following SQL (using dynamic parameters of course)

SELECT tblStaff.StaffID, tblStaff.[Forename] & ' ' & [Surname] AS Name, tbl_Holidays.Date
FROM tblStaff INNER JOIN tbl_Holidays ON tblStaff.StaffID = tbl_Holidays.StaffID
WHERE (((tbl_Holidays.Date) Between #4/1/2009# And #5/1/2009#-1))
GROUP BY tblStaff.StaffID, tblStaff.[Forename] & ' ' & [Surname], tbl_Holidays.Date, tblStaff.Department, tbl_Holidays.MinutesUsed, tbl_Holidays.Accepted, tbl_Holidays.AuthON, tbl_Holidays.CancON, tblStaff.Surname
HAVING (((tblStaff.Department)="Haematology") AND ((tbl_Holidays.MinutesUsed)>0) AND ((tbl_Holidays.Accepted)=True) AND ((tbl_Holidays.AuthON) Is Not Null) AND ((tbl_Holidays.CancON) Is Null))
ORDER BY tbl_Holidays.Date, tblStaff.Surname;

what i would like to do is exclude dates which reside in a second table

i tried the following SQL and some variations i could think of as so:

SELECT tblStaff.StaffID, tblStaff.[Forename] & ' ' & [Surname] AS Name, tbl_Holidays.Date
FROM tblBlockBookD INNER JOIN (tblStaff INNER JOIN tbl_Holidays ON tblStaff.StaffID = tbl_Holidays.StaffID) ON tblBlockBookD.Date = tbl_Holidays.Date
WHERE (((tbl_Holidays.Date) Between #4/1/2009# And #5/1/2009#-1) AND ((tblBlockBookD.Date) Is Null))
GROUP BY tblStaff.StaffID, tblStaff.[Forename] & ' ' & [Surname], tbl_Holidays.Date, tblStaff.Department, tbl_Holidays.MinutesUsed, tbl_Holidays.Accepted, tbl_Holidays.AuthON, tbl_Holidays.CancON, tblStaff.Surname
HAVING (((tblStaff.Department)="Haematology") AND ((tbl_Holidays.MinutesUsed)>0) AND ((tbl_Holidays.Accepted)=True) AND ((tbl_Holidays.AuthON) Is Not Null) AND ((tbl_Holidays.CancON) Is Null))
ORDER BY tbl_Holidays.Date, tblStaff.Surname;

so the second table is the one called tblBlockBookD and holds bank holidays basically, so i dont need them in the query. my lazy man way was to simply strip off the results from the particular text box and add "bank holiday".

please tel me if i am thinking too much about this, i just want something simple, i thought it would be but apparently not.

thanks,
Dan

You could possibly create a Recordet based on your SQL Statement, and cross-reference each Date withing the Range against all the Dates in tblBlockBookID, and if it is a Holiday, exclude it.

[Dan2kx]

i suspect this would slow down the process (checking the BE) i thought it might be possible from the same query statement, i guess not then?

cheers,
Dan

[ADezii]

i suspect this would slow down the process (checking the BE) i thought it might be possible from the same query statement, i guess not then?

cheers,
Dan

Dan, you may be able to do this with a Sub-Query, but SQL is my Achilles Heel! I'll see if I can get some of the SQL Boys to have a look at this Thread. Be patient.

[NeoPa]

The usual way to handle an exclusion list (from a table) is to add a LEFT JOIN to the table, and say :

...
WHERE ExclusionTable.Field Is Null

This technique is used by the Find Unmatched Query Wizard. This is actually how I first picked it up.

[Dan2kx]

Yup works, i used the join type wiz and selected the third option.

Thanks for the help

Dan

[NeoPa]

No worries Dan. I find myself using that quite handy technique quite a lot. Well worth learning :)