Table Relationship DB Help

[djspod]

I don't understand

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;

[CJ_London]

the links between staff, shifts and patterns should be clear. All query Q does is calculate the number of patterns per shift which is used in the criteria. You could copy the code between the brackets and paste into another query if you wanted and then link back in the main query. But unless there are performance issues, I prefer to keep everything in one query if possible. The 'Q' name is just an alias in the same way you can alias tables as well. I could have called it 'PatternsPerShift' but like to keep names short. Because it is in brackets, you can only write it in the sql window.

Aliasing is a good practice to adopt since it keeps your code shorter and generally easier to read.

tblSundays is just a list of dates instead of entering a date in a form. It is not joined to anything because you want records for all dates returned - but you could put a criteria on the list to limit it to the next 16 weeks, this month, whatever, in the same way you could add a criteria to limit to one or more shifts, certain employees, etc.

[djspod]

Okay, I'm a lot wiser now on how you created the query and deduced your answer towards a great solution.

My next goal is to:

Use some of the knowledge that you have taught + other Forum Users, books and Youtube videos, move that into practice to solve a problem.


That has not clicked yet with me, Is it a loop, a function like Datedif, IFF statement, Right, Left or select case statement for example.

I guess this will come in time as most problems will require a similar solution and it's knowing which code tool to pull out the box of tricks.

I've added to your Reputation and feedback


Thanks again

Darren

[CJ_London]

I've added to your Reputation and feedback

Thanks

Just post your problem in a new thread when you are ready