help understanding a query

[Guest]

I'm trying to understand this Select statement.

SELECT tTech.Name, Sum(tTickets.[Time to Fix]) AS Total, Min(tTickets.Date)
AS [Start Date], Max(tTickets.Date) AS [End Date]
FROM tTech INNER JOIN (tDivisions INNER JOIN tTickets ON
tDivisions.[Division ID] = tblTickets.[Division ID]) ON tblTech.[Tech ID] =
tblTickets.[Tech ID]
WHERE (((tTickets.[Date Completed])>#1/31/2007# And (tTickets.[Date
Completed])<#3/1/2007#))
GROUP BY tTech.Name
ORDER BY Sum(tTickets.[Time to Fix]) DESC;

I assume there is a relationship from table Division to table Tech to table
Ticket. Are the Inner Joins from Tech to Division and Tech to Ticket?

The sum of the "time to fix" field and min and max are aggregates. So this
query shows all the dates worked on one issue but the min and max show the
date completed and the date started? The AS gives the names an alias name of
Start Date and End DAte?

The where clause groups by technican and sorts by length of time to fix an
issue?

thanks,

 

[Michel Walsh]

The matches to be seek can be read from the ON segments, mainly:


ON tDivisions.[Division ID] = tblTickets.[Division ID]

and

ON tblTech.[Tech ID] = tblTickets.[Tech ID]

The sum of the "time to fix" field and min and max are aggregates. So
this
query shows all the dates worked on one issue but the min and max show the
date completed and the date started? The AS gives the names an alias name
of
Start Date and End DAte?

sounds like an acceptable interpretation, yes, but more about it later. You
are absolutely right about the AS keyword construction.

The where clause groups by technician and sorts by length of time to fix
an
issue?

The ORDER BY is just for presentation, and nothing more.

The GROUP BY define the lines to be seen. So, here, the aggregate do no
define 'by issue', but 'by technician', ie, the SUM is the time by
technician, over all the issue he/she may have worked on. If you want get by
technician, by issue, add the issue field in the GROUP BY clause (and
probably want to see it in the SELECT clause too).



Hoping it may help,
Vanderghast, Access MVP

I'm trying to understand this Select statement.

SELECT tTech.Name, Sum(tTickets.[Time to Fix]) AS Total,
Min(tTickets.Date)
AS [Start Date], Max(tTickets.Date) AS [End Date]
FROM tTech INNER JOIN (tDivisions INNER JOIN tTickets ON
tDivisions.[Division ID] = tblTickets.[Division ID]) ON tblTech.[Tech ID]
=
tblTickets.[Tech ID]
WHERE (((tTickets.[Date Completed])>#1/31/2007# And (tTickets.[Date
Completed])<#3/1/2007#))
GROUP BY tTech.Name
ORDER BY Sum(tTickets.[Time to Fix]) DESC;

I assume there is a relationship from table Division to table Tech to
table
Ticket. Are the Inner Joins from Tech to Division and Tech to Ticket?

The sum of the "time to fix" field and min and max are aggregates. So
this
query shows all the dates worked on one issue but the min and max show the
date completed and the date started? The AS gives the names an alias name
of
Start Date and End DAte?

The where clause groups by technican and sorts by length of time to fix an
issue?

thanks,

 

[Guest]

Since they are all inner joins, if any table is missing a matching record,
nothing will be returned.

The Min(tTickets.Date) shows the earliest date for that tTech.Name within
the limits of the WHERE clause. Same things goes for the Max. This assumes
the Date field is an actual date/time datatype and not a text field which has
something in it which looks like a date. BTW: Date is a reserved words in
SQL. It's possible that you could get an error from them if you didn't have
the table name appended to the front of them. Something like TicketDate would
be better.

AS does provide an alias which creates a column heading.

WHERE and GROUP BY have different jobs.
WHERE limits the records being returned.
GROUP BY tells what to sum, min, and max is this query.

To make matters more confusing, sometimes you need a HAVING clause instead
of WHERE.

After all the records are collected up, then the ORDER BY sorts them before
being returned.