SQL Problem in access with dates

tucker · December 17th, 2003, 07:47 AM

Im having problems getting a query to run which i hope somebody can help me with. I have the following table.

RecordId
Name
Department
DateFrom
DateTo

What I need to do is write a query that takes the values from DateFrom and DateTo and Display a count of the department split into the dates from the range until the last day is reached

For example
rid name department dateFrom dateTo
Row1 1 mr bloggs a 15/12/2003 18/12/2003

The Query would result as

Department CountOfDeprtment Date
a 1 15/12/2003
a 1 16/12/2003
a 1 17/12/2003
a 1 18/12/2003

Jeff Mason · December 17th, 2003, 06:14 PM

I'm not sure I completely understand your requirement, but I'll guess.

The trick here is to make another table that is a calendar containing just dates. This sort of table is very useful. You can add indicators to the rows in this table to indicate holidays and other non-business days if you want; then you can use the indicator in a WHERE clause to restrict the dates to just non-holidays, for example. But we'll assume a table that has just the following:

Code:

CREATE TABLE Calendar (
    CalendarDate datetime NOT NULL PRIMARY KEY
);

You can populate this any number of ways; spreadsheets have functions which will generate dates easily and they are easy to import. You could also write a stored procedure (if you weren't using Access :)) to generate the dates to INSERT. In any case, this is only something you have to do every few years or so.

Once you have the calendar table, you just CROSS JOIN it to your input table, selecting only the dates in question, group by Department and Date and count them:

Code:

SELECT Department, Count(*) as DeptCount, CalendarDate
    FROM yourtable CROSS JOIN Calendar
    WHERE CalendarDate BETWEEN DateFrom AND DateTo
    GROUP BY Department, CalendarDate
    ORDER BY Department, CalendarDate;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

tucker · December 19th, 2003, 04:27 AM

Jeff,

Having a little problem with the code that you sent me. I have changed it so it represents the tables and fields within my database but when i try to run the query it tells me there is a syntax error with the CROSS JOIN syntax. Is this an illegal syntax in ACCESS???

Jeff Mason · December 19th, 2003, 09:05 AM

Try eliminating the 'CROSS JOIN' in the FROM clause and replacing it with a comma between the table names:

Code:

SELECT Department, Count(*) as DeptCount, CalendarDate
    FROM yourtable, Calendar
    WHERE CalendarDate BETWEEN DateFrom AND DateTo
    GROUP BY Department, CalendarDate
    ORDER BY Department, CalendarDate;

Specifying the query this way is the "old style" syntax; the CROSS JOIN is "new-style" SQL-92 syntax and Access may not support that...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

tucker · December 19th, 2003, 12:16 PM

John,

Thanks the query is now working fine.

Cheers