Why does it multiply my 'sum' by 17?

[Love Buzz]

Ok. I am working with a select queury and all I am trying to do is sum two
numbers in a column together that share the same 'queue'.

For some reason it's adding the two numbers and then multiplying by 17 for
every 'group'.

Any ideas? Thanks for your help.

 

[KARL DEWEY]

Post your SQL.

 

[Love Buzz]

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

 

[KARL DEWEY]

I do not see anything that would do it. Here is a test as you said you were
add two records try this ---
SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Count([December 4].Items) AS CountOfItems, Sum([December
4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

--
KARL DEWEY
Build a little - Test a little

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

Post your SQL.

 

[Love Buzz]

Thanks for your response. Unfortunatley I got a conflict error with the sql
below.

I do not see anything that would do it. Here is a test as you said you were
add two records try this ---
SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Count([December 4].Items) AS CountOfItems, Sum([December
4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

--
KARL DEWEY
Build a little - Test a little

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

Post your SQL.
--
KARL DEWEY
Build a little - Test a little


:

Ok. I am working with a select queury and all I am trying to do is sum two
numbers in a column together that share the same 'queue'.

For some reason it's adding the two numbers and then multiplying by 17 for
every 'group'.

Any ideas? Thanks for your help.

 

[Love Buzz]

I tried 'avg' and it worked fine. Not sure why the sum function tries to
multiply 17 to the sum. Weird.

Thanks for your response. Unfortunatley I got a conflict error with the sql
below.

I do not see anything that would do it. Here is a test as you said you were
add two records try this ---
SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Count([December 4].Items) AS CountOfItems, Sum([December
4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

--
KARL DEWEY
Build a little - Test a little

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

:

Post your SQL.
--
KARL DEWEY
Build a little - Test a little


:

Ok. I am working with a select queury and all I am trying to do is sum two
numbers in a column together that share the same 'queue'.

For some reason it's adding the two numbers and then multiplying by 17 for
every 'group'.

Any ideas? Thanks for your help.

 

[fredg]

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

Post your SQL.

It would have been even more helpful had you given us an example of
what the actual values to be summed are, what result you expect, and
what you actually get.

I would suspect that you are not getting a value unexpectedly
multiplied by 17, but rather incorrect values to be summed. See #1
below.


1) Date and Function are both reserved keywords and should not be used
as field names. See:

http://www.allenbrowne.com/AppIssueBadWord.html

Try your query again after you change those field names and see if
that has fixed your problem.

Also, from the name of one of your fields, [December 4], I would
suspect that your database is not normalized.

 

[John Spencer]

My guess is that you are returning mutliple rows (17) for each record in the
group. Probably due to the joins you have set up.

You might try changing the query so you eliminate multiple row being
returned on the basis of the joins. One way to do that might be the
following.

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4]
WHERE UserID IN (SELECT UserID FROM [Returns Associates])
AND Function In (SELECT Function FROM [Queus and SLAs])
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I tried 'avg' and it worked fine. Not sure why the sum function tries to
multiply 17 to the sum. Weird.

Thanks for your response. Unfortunatley I got a conflict error with the
sql
below.

I do not see anything that would do it. Here is a test as you said you
were
add two records try this ---
SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Count([December 4].Items) AS CountOfItems,
Sum([December
4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December
4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON
[December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

--
KARL DEWEY
Build a little - Test a little


:

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December
4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON
[December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December
4].Function,
[December 4].Queu;

:

Post your SQL.
--
KARL DEWEY
Build a little - Test a little


:

Ok. I am working with a select queury and all I am trying to do
is sum two
numbers in a column together that share the same 'queue'.

For some reason it's adding the two numbers and then multiplying
by 17 for
every 'group'.

Any ideas? Thanks for your help.

 

[John W. Vinson]

Ok. I am working with a select queury and all I am trying to do is sum two
numbers in a column together that share the same 'queue'.

For some reason it's adding the two numbers and then multiplying by 17 for
every 'group'.

Any ideas? Thanks for your help.

Almost certainly because you're retrieving each record 17 times, because one
of your joined tables in the query has 17 records:

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

If there are multiple records in [Returns Associates] or [Queus and SLAs] for
each value of UserID or of Function respectively, you'll see each record in
the (horribly badly named) [December 4] table repeated that many times. That's
how queries are designed to work.

 

[Love Buzz]

Marshall you are correct. That table listes the 'user IDs' of the Associates
that I want to analyze data from the 'December 4' table. I linked to the two
tables. Is there anyway to correct this so that it does not multiply the sum
by the total records?

Thank you for your help.

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

:

Ok. I am working with a select queury and all I am trying to do is sum two
numbers in a column together that share the same 'queue'.

For some reason it's adding the two numbers and then multiplying by 17 for
every 'group'.

I suspect that each record in the december table links to 17
records in the returns table.

 

[Love Buzz]

I think I got it. Why is December 4 a bad name for a table?

Ok. I am working with a select queury and all I am trying to do is sum two
numbers in a column together that share the same 'queue'.

For some reason it's adding the two numbers and then multiplying by 17 for
every 'group'.

Any ideas? Thanks for your help.

Almost certainly because you're retrieving each record 17 times, because one
of your joined tables in the query has 17 records:

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

If there are multiple records in [Returns Associates] or [Queus and SLAs] for
each value of UserID or of Function respectively, you'll see each record in
the (horribly badly named) [December 4] table repeated that many times. That's
how queries are designed to work.

 

[Marshall Barton]

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

I suspect that each record in the december table links to 17
records in the returns table.

 

[Marshall Barton]

Just remove the returns table from the query.

If this is for a form, then try using a subform for the
returns data.

Otherwise, create another query that joins your query to the
returns table.
--
Marsh
MVP [MS Access]

Marshall you are correct. That table listes the 'user IDs' of the Associates
that I want to analyze data from the 'December 4' table. I linked to the two
tables. Is there anyway to correct this so that it does not multiply the sum
by the total records?

SELECT [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu, Sum([December 4].Items) AS SumOfItems
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON [December
4].Function = [Queus and SLAs].Function
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Function,
[December 4].Queu;

:

Ok. I am working with a select queury and all I am trying to do is sum two
numbers in a column together that share the same 'queue'.

For some reason it's adding the two numbers and then multiplying by 17 for
every 'group'.

I suspect that each record in the december table links to 17
records in the returns table.

 

[John W. Vinson]

I think I got it. Why is December 4 a bad name for a table?

Minor issue: it's got a blank in the name. Access lets you use blanks in table
or fieldnames, many other programs don't; and doing so requires that you
always use [square brackets] in referring to the name.

Major issue: it implies that you have tables [December 4] and [December 11]
and [December 18] - i.e. storing data in tablenames. This is one variant on
the venial sin of Committing Spreadsheet. Data should be stored *in a field in
your table*; date data in a Date/Time field - NOT in a tablename. You should
NOT have a different table for each date!

If this table is named [December 4] for some other reason, then I withdraw my
objection... but it sure makes me suspicious.

 

[Love Buzz]

December 4 was the first date that I used to extract data from a report.
That was the only reason. I am still lost as to what else December 4 is a
bad name....but that's OK.

I think I got it. Why is December 4 a bad name for a table?

Minor issue: it's got a blank in the name. Access lets you use blanks in table
or fieldnames, many other programs don't; and doing so requires that you
always use [square brackets] in referring to the name.

Major issue: it implies that you have tables [December 4] and [December 11]
and [December 18] - i.e. storing data in tablenames. This is one variant on
the venial sin of Committing Spreadsheet. Data should be stored *in a field in
your table*; date data in a Date/Time field - NOT in a tablename. You should
NOT have a different table for each date!

If this table is named [December 4] for some other reason, then I withdraw my
objection... but it sure makes me suspicious.