Cartesian Query

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

OK..I have been working on this query without much
success, I hope that someone can help me with this one.
I am sure that I need to use a Cartesion query but I am
having trouble making it work.
I need a query That shows a line for every date that
Click to expand...
corresponds with the checked boxes in the table entry.

Example:
These are the "Meeting Table" fields:
EmployeeID, StartDate, EndDate, S,M,T,W,TH,F,SA (the last
7 are check boxes)


Table entries:
John Smith 10-1-03, 10-10-03, M,W,F (checked Boxes)
Jane Doe 10-1-03, 10-10-03, T,TH (checked Boxes)

Query Result:
John Smith 10-1-03
John Smith 10-3-03
John Smith 10-6-03
John Smith 10-8-03
John Smith 10-10-03
Jane Doe 10-2-03
Jane Doe 10-7-03
Jane Doe 10-9-03
 
OUCH! The non-normalized check box set up for days of the week is going to make
this difficult. I might try something like the following SQL.

SELECT EmployeeID, DateAdd("d",NumTable.Num,StartDate) As CalcDate
FROM MeetingTable, NumTable
WHERE NumTable.Num <= DateDiff("d",StartDate,EndDate) AND
((WeekDay(DateAdd("d",NumTable.Num,StartDate)) = 1 AND S = True) OR
(WeekDay(DateAdd("d",NumTable.Num,StartDate)) = 2 AND M = True) OR
.... ) OR
(WeekDay(DateAdd("d",NumTable.Num,StartDate)) = 7 AND S = True))


NumTable would be a table with just the one field that holds one-up numbers.
From 1 to the highest number you would need (max number of days between Start
date and End Date plus 1)

This will probably be VERY slow.

You could also try using a union query to get the data normalized

SELECT EmployeeID, StartDate, EndDate, IIF(S = True, 1)
UNION ALL
SELECT EmployeeID, StartDate, EndDate, IIF(M = True, 2)
UNION ALL
....
SELECT EmployeeId, StartDate, EndDate, IIF(SA = True,7)

Then use that

SELECT EmployeeID, DateAdd("d",NumTable.Num,StartDate) As CalcDate
FROM UnionQuery, NumTable
WHERE NumTable.Num <= DateDiff("d",StartDate,EndDate)
 
Back
Top