Hi Ajax,
I was referring to the Ajax query.
I can see that you used 2 tables in the query,
I get that Shifts can have 1 to many staff, Shifts can have 1 to many Patterns. (Tables)
Q - Table/Query in the Query design plays a big part in the Query result I can see that in working the number of weeks out etc.
I am unable view how that part of the Query works, what action does the SQL language look like in terms of writing it out before you pasted into the Query?
Brilliant piece of work and the speed it pulls that data out for a whole year, I just need to understand the logic of it all (Query)
Would you mind posting your way of thinking when you looked at this, a breakdown of your logicial method and how you worked it out for the whole Query.
I'm very new to SQL, I get the below up and to the Q Query then my thinking gets a bit muddy.
I have now viewed in SQL view to get a little more understanding, I'm guessing the Q element is a query that works part of the weeks out and then works together with the rest of the other elements of the Ajax Query but how is what I don't understand.
A breakdown of your logical thinking to the below will help me understand how you deduced the below and also help myself with similar database problems.
Thanks for you help
SELECT tblSundays.Sunday, tblShifts.ShiftName, tblStaff.FullName, tblPatterns.WeekNum, tblPatterns.SUN, tblPatterns.MON, tblPatterns.TUE, tblPatterns.WED, tblPatterns.THU, tblPatterns.FRI, tblPatterns.SAT
FROM tblSundays, (tblShifts INNER JOIN (tblPatterns INNER JOIN (SELECT ShiftFK, Count(*) AS Weeks FROM tblPatterns GROUP BY ShiftFK) AS Q ON tblPatterns.ShiftFK = Q.ShiftFK) ON tblShifts.ShiftPK = tblPatterns.ShiftFK) INNER JOIN tblStaff ON tblShifts.ShiftPK = tblStaff.ShiftFK
WHERE (((tblPatterns.WeekNum)=((DateDiff("ww",[CommencingDate],[Sunday])+[offset]) Mod [Weeks])+1))
ORDER BY tblSundays.Sunday, tblShifts.ShiftName, tblStaff.FullName;