Exclude weekends and hols

[agibsonsw]

Hello. Access 2003.

I've done this before a while ago, but need to refresh my memory..

I've created a table tblDates which has sequential dates together with a yes/no field to indicate whether the date should be excluded from a count (based on weekends and bank holidays). I've built a query to only list dates between FirstDate and LastDate if they should not be excluded:

Code: Select all

SELECT tblHols.SomeName, tblDates.anyDate
FROM tblDates, tblHols
WHERE (((tblDates.anyDate) Between [FirstDate] And [LastDate]) AND ((tblDates.ExcludeIt)=No))
ORDER BY tblDates.anyDate;

I could then build a second query based on this one to count the days. Can this be done in one query? Andy.

[HansV]

You could use

SELECT tblHols.SomeName, tblHols.FirstDate, Count(*) AS Duration
FROM tblHols, tblDates
WHERE tblDates.anyDate Between [FirstDate] And [LastDate] AND tblDates.ExcludeIt=False
GROUP BY tblHols.SomeName, tblHols.FirstDate

[agibsonsw]

Cool