Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting weekdays (2000)

    I have a table tblDates with two columns, listing all calendar dates as ActualDate and Exclude which is a Yes/No field. In a separate tblHolidays I list FirstDate and LastDate for holidays booked. So the question is... How can I count up the number of days between FirstDate and LastDate WHERE the ActualDate is not 'Exclude'-ed? That is, a count of working days taken. Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting weekdays (2000)

    I suspect you haven't provided all relevant information, but perhaps something like this:

    SELECT Count(*) AS WorkingDays
    FROM tblDates, tblHolidays
    WHERE (tblDates.ActualDate Between tblHolidays.FirstDate And tblHolidays.LastDate) And (tblDates.Exclude = False)

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting weekdays (2000)

    Brilliant again. The tblHolidays also includes a StaffID field, so I need the total for each staff member?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting weekdays (2000)

    Add StaffID to the query as the first field in the grid, and group on that field (i.e. you must make it into a Totals query)

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting weekdays (2000)

    Doing that creates the error message 'Cannot have aggregate function in GROUP BY clause (Count(*))'.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting weekdays (2000)

    Change the Total option to Expression.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •