Returning dates within a range

  • Thread starter Thread starter jeff
  • Start date Start date
J

jeff

I have a table with StartDate, EndDate and check boxes
for every day of the week (Mon, Tues, etc). What is the
best way to produce a query or document that shows all
dates that coincide with the respective check boxes
within the dates?
 
-----Original Message-----
Please paste 2-3 records into a reply with the desired output.

--
Duane Hookom
MS Access MVP





.
Entry In the table:
Click to expand...
ClientName, EmployeeName,StartRangeDate, EndRangeDate,
SundayBox=No, MondayBox=yes, TuesdayBox=No,
WednesdayBox=Yes, ThursdayBox=No, FridayBox=Yes,
SaturdayBox=No.

Data to show: (using 8-1-03 thru 8-31-03 as the range)
John Smith, Jane Doe,8-4-03
John Smith, Jand Doe,8-6-03
John Smith, Jand Doe,8-8-03
John Smith, Jane Doe,8-11-03
John Smith, Jand Doe,8-13-03
John Smith, Jand Doe,8-15-03
John Smith, Jane Doe,8-25-03
John Smith, Jand Doe,8-27-03
John Smith, Jand Doe,8-29-03

The form resembles the Weekly recurrence form in
Microsoft Outlook.
 
Your data to show doesn't match your table data. However, I would create a
table of dates tblDates with a single date field named ADate and values from
8/1 to whatever. Then create a union query based on your table (tblEmpSched)
=== quniEmpSchedule======================
SELECT EmployeeName, StartRangeDate, EndRangeDate, 1 as DOW
FROM tblEmpSched
WHERE SundayBox=True
UNION ALL
SELECT EmployeeName, StartRangeDate, EndRangeDate, 2 as DOW
FROM tblEmpSched
WHERE MondayBox=True
UNION ALL
SELECT EmployeeName, StartRangeDate, EndRangeDate, 3 as DOW
FROM tblEmpSched
WHERE TuesdayBox=True
UNION ALL
SELECT EmployeeName, StartRangeDate, EndRangeDate, 4 as DOW
FROM tblEmpSched
WHERE WednesdayBox=True
UNION ALL
SELECT EmployeeName, StartRangeDate, EndRangeDate, 5 as DOW
FROM tblEmpSched
WHERE ThursdayBox=True
UNION ALL
SELECT EmployeeName, StartRangeDate, EndRangeDate, 6 as DOW
FROM tblEmpSched
WHERE FridayBox=True
UNION ALL SELECT EmployeeName, StartRangeDate, EndRangeDate, 7 as DOW
FROM tblEmpSched
WHERE SaturdayBox=True;

Then create a query with quniEmpSchedule and tblDates
==========================================
SELECT EmployeeName, ADate
FROM quniEmpSchedule, tblDates
WHERE ADate Between [StartRangeDate] And [EndRangeDate] AND
Weekday([ADate])=[DOW]
ORDER BY ADate;
 
-----Original Message-----
Your data to show doesn't match your table data. However, I would create a
table of dates tblDates with a single date field named ADate and values from
8/1 to whatever. Then create a union query based on your table (tblEmpSched)
=== quniEmpSchedule======================
SELECT EmployeeName, StartRangeDate, EndRangeDate, 1 as DOW
FROM tblEmpSched
WHERE SundayBox=True
UNION ALL
SELECT EmployeeName, StartRangeDate, EndRangeDate, 2 as DOW
FROM tblEmpSched
WHERE MondayBox=True
UNION ALL
SELECT EmployeeName, StartRangeDate, EndRangeDate, 3 as DOW
FROM tblEmpSched
WHERE TuesdayBox=True
UNION ALL
SELECT EmployeeName, StartRangeDate, EndRangeDate, 4 as DOW
FROM tblEmpSched
WHERE WednesdayBox=True
UNION ALL
SELECT EmployeeName, StartRangeDate, EndRangeDate, 5 as DOW
FROM tblEmpSched
WHERE ThursdayBox=True
UNION ALL
SELECT EmployeeName, StartRangeDate, EndRangeDate, 6 as DOW
FROM tblEmpSched
WHERE FridayBox=True
UNION ALL SELECT EmployeeName, StartRangeDate, EndRangeDate, 7 as DOW
FROM tblEmpSched
WHERE SaturdayBox=True;

Then create a query with quniEmpSchedule and tblDates
==========================================
SELECT EmployeeName, ADate
FROM quniEmpSchedule, tblDates
WHERE ADate Between [StartRangeDate] And [EndRangeDate] AND
Weekday([ADate])=[DOW]
ORDER BY ADate;

--
Duane Hookom
MS Access MVP


Jeff said:
ClientName, EmployeeName,StartRangeDate, EndRangeDate,
SundayBox=No, MondayBox=yes, TuesdayBox=No,
WednesdayBox=Yes, ThursdayBox=No, FridayBox=Yes,
SaturdayBox=No.

Data to show: (using 8-1-03 thru 8-31-03 as the range)
John Smith, Jane Doe,8-4-03
John Smith, Jand Doe,8-6-03
John Smith, Jand Doe,8-8-03
John Smith, Jane Doe,8-11-03
John Smith, Jand Doe,8-13-03
John Smith, Jand Doe,8-15-03
John Smith, Jane Doe,8-25-03
John Smith, Jand Doe,8-27-03
John Smith, Jand Doe,8-29-03

The form resembles the Weekly recurrence form in
Microsoft Outlook.
Click to expand...


.
Duane,
Click to expand...
Thanks for the help...this is a good method that I had
never thought of....Thanks for the help, I am sure I can
get there now!
 
Back
Top