C
Cameron
I have a query from a legacy database that draws out record counts based on
employees over a given date range. But when I run the query some of the
values result in too many records. I am kind of baffled on why this query
doesn't work when it did in an early version of this exact database.
SELECT [All Members/Weeks].Team, [All Members/Weeks].[Last Name], [All
Members/Weeks].[First Name], Sum([Team Totals - CE].[CountOfEntry Date/Time])
AS CE, Sum([Team Totals - SR].[CountOfEntry Date/Time]) AS SR, Sum([Team
Totals - SRE].[CountOfEntry Date/Time]) AS SRE, Sum([Team Totals -
SWO].[CountOfEntry Date/Time]) AS SWO, Sum([Team Totals - LO].[CountOfEntry
Date/Time]) AS LO, Sum([Team Totals - RA].[CountOfEntry Date/Time]) AS RA
FROM ([Team Totals - LO] RIGHT JOIN ([Team Totals - SWO] RIGHT JOIN ([Team
Totals - SRE] RIGHT JOIN ([Team Totals - CE] RIGHT JOIN ([All Members/Weeks]
LEFT JOIN [Team Totals - SR] ON ([All Members/Weeks].[Last Name] = [Team
Totals - SR].[Last Name]) AND ([All Members/Weeks].[First Name] = [Team
Totals - SR].[First Name]) AND ([All Members/Weeks].Team = [Team Totals -
SR].Team)) ON ([Team Totals - CE].[Last Name] = [All Members/Weeks].[Last
Name]) AND ([Team Totals - CE].[First Name] = [All Members/Weeks].[First
Name]) AND ([Team Totals - CE].Team = [All Members/Weeks].Team)) ON ([Team
Totals - SRE].[Last Name] = [All Members/Weeks].[Last Name]) AND ([Team
Totals - SRE].[First Name] = [All Members/Weeks].[First Name]) AND ([Team
Totals - SRE].Team = [All Members/Weeks].Team)) ON ([Team Totals - SWO].[Last
Name] = [All Members/Weeks].[Last Name]) AND ([Team Totals - SWO].[First
Name] = [All Members/Weeks].[First Name]) AND ([Team Totals - SWO].Team =
[All Members/Weeks].Team)) ON ([Team Totals - LO].[Last Name] = [All
Members/Weeks].[Last Name]) AND ([Team Totals - LO].[First Name] = [All
Members/Weeks].[First Name]) AND ([Team Totals - LO].Team = [All
Members/Weeks].Team)) LEFT JOIN [Team Totals - RA] ON ([All
Members/Weeks].[Last Name] = [Team Totals - RA].[Last Name]) AND ([All
Members/Weeks].[First Name] = [Team Totals - RA].[First Name]) AND ([All
Members/Weeks].Team = [Team Totals - RA].Team)
GROUP BY [All Members/Weeks].Team, [All Members/Weeks].[Last Name], [All
Members/Weeks].[First Name];
employees over a given date range. But when I run the query some of the
values result in too many records. I am kind of baffled on why this query
doesn't work when it did in an early version of this exact database.
SELECT [All Members/Weeks].Team, [All Members/Weeks].[Last Name], [All
Members/Weeks].[First Name], Sum([Team Totals - CE].[CountOfEntry Date/Time])
AS CE, Sum([Team Totals - SR].[CountOfEntry Date/Time]) AS SR, Sum([Team
Totals - SRE].[CountOfEntry Date/Time]) AS SRE, Sum([Team Totals -
SWO].[CountOfEntry Date/Time]) AS SWO, Sum([Team Totals - LO].[CountOfEntry
Date/Time]) AS LO, Sum([Team Totals - RA].[CountOfEntry Date/Time]) AS RA
FROM ([Team Totals - LO] RIGHT JOIN ([Team Totals - SWO] RIGHT JOIN ([Team
Totals - SRE] RIGHT JOIN ([Team Totals - CE] RIGHT JOIN ([All Members/Weeks]
LEFT JOIN [Team Totals - SR] ON ([All Members/Weeks].[Last Name] = [Team
Totals - SR].[Last Name]) AND ([All Members/Weeks].[First Name] = [Team
Totals - SR].[First Name]) AND ([All Members/Weeks].Team = [Team Totals -
SR].Team)) ON ([Team Totals - CE].[Last Name] = [All Members/Weeks].[Last
Name]) AND ([Team Totals - CE].[First Name] = [All Members/Weeks].[First
Name]) AND ([Team Totals - CE].Team = [All Members/Weeks].Team)) ON ([Team
Totals - SRE].[Last Name] = [All Members/Weeks].[Last Name]) AND ([Team
Totals - SRE].[First Name] = [All Members/Weeks].[First Name]) AND ([Team
Totals - SRE].Team = [All Members/Weeks].Team)) ON ([Team Totals - SWO].[Last
Name] = [All Members/Weeks].[Last Name]) AND ([Team Totals - SWO].[First
Name] = [All Members/Weeks].[First Name]) AND ([Team Totals - SWO].Team =
[All Members/Weeks].Team)) ON ([Team Totals - LO].[Last Name] = [All
Members/Weeks].[Last Name]) AND ([Team Totals - LO].[First Name] = [All
Members/Weeks].[First Name]) AND ([Team Totals - LO].Team = [All
Members/Weeks].Team)) LEFT JOIN [Team Totals - RA] ON ([All
Members/Weeks].[Last Name] = [Team Totals - RA].[Last Name]) AND ([All
Members/Weeks].[First Name] = [Team Totals - RA].[First Name]) AND ([All
Members/Weeks].Team = [Team Totals - RA].Team)
GROUP BY [All Members/Weeks].Team, [All Members/Weeks].[Last Name], [All
Members/Weeks].[First Name];