problems with counting and summing records in a query

C

Cameron

Hello,

I have a query that is giving me issue. Intially several queries draw data
from a number of tables based on a date range. In these queries the date
entry is counted.

In the main query the other queries come together and are compared with
employees that are not excluded from participating. In the main query the
records that where intially counted in the other queires are summed up. And
for some reason some people in the main query show with incorrect summed
numbers. And yet when checking the individual records for these people prior
to them being drawin into the queries the value of their records amount to 3
times less. And yet there is no where that I multiply records 3 times.



Intial query:

SELECT [Close Encounters].[Last Name], [Close Encounters].[First Name],
[Close Encounters].Team, Count([Close Encounters].[Entry Date/Time]) AS
[CountOfEntry Date/Time]
FROM [Close Encounters]
GROUP BY [Close Encounters].[Last Name], [Close Encounters].[First Name],
[Close Encounters].Team, [Close Encounters].Week, [Close
Encounters].[Activity Date]
HAVING ((([Close Encounters].[Activity Date])>=[Forms]![Main
Form]![txtBGdate] And ([Close Encounters].[Activity Date])<=[Forms]![Main
Form]![txtEDdate]));


Main Query:

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];


Any suggestions on what might be happening?
 
J

Jerry Whittle

Do you have any people who have the same first and last name? As you are
joining by name fields, that could do it. You'd probably be better off
joining everything by a single field that should be unique like an
EmployeeNumber.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Cameron said:
Hello,

I have a query that is giving me issue. Intially several queries draw data
from a number of tables based on a date range. In these queries the date
entry is counted.

In the main query the other queries come together and are compared with
employees that are not excluded from participating. In the main query the
records that where intially counted in the other queires are summed up. And
for some reason some people in the main query show with incorrect summed
numbers. And yet when checking the individual records for these people prior
to them being drawin into the queries the value of their records amount to 3
times less. And yet there is no where that I multiply records 3 times.



Intial query:

SELECT [Close Encounters].[Last Name], [Close Encounters].[First Name],
[Close Encounters].Team, Count([Close Encounters].[Entry Date/Time]) AS
[CountOfEntry Date/Time]
FROM [Close Encounters]
GROUP BY [Close Encounters].[Last Name], [Close Encounters].[First Name],
[Close Encounters].Team, [Close Encounters].Week, [Close
Encounters].[Activity Date]
HAVING ((([Close Encounters].[Activity Date])>=[Forms]![Main
Form]![txtBGdate] And ([Close Encounters].[Activity Date])<=[Forms]![Main
Form]![txtEDdate]));


Main Query:

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];


Any suggestions on what might be happening?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trouble with Query doubling values 1
list items from one table in another table based on value in first table 2
SUM in a UNION query 2
Complex Query Slowdown... 1
Query that joins other queries is not calculating 2
Format Cells in a query created using SQL 3
Totals Query question 6
counting contiguous series of similar field contents 3

Top