Please help!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AccessHelp - 15 Nov 2005 16:49 GMT

Hello,

I posted the same tread yesterday. Please help. I need to know what is
causing to happen.

I have a report to run a list of clients that we have not billed invoices
for. The Control Source of the report is a SQL query, and the SQL query is
pulling the info from a Select query that I created.

The problem is when I do the "Totals" on the SQL query and run it in the
Datasheet View, the sum of the "Total Accum Time" (the $ amount for the time
that we spend on a client) is not the same as without doing the "Totals" on
the SQL query. Does anyone know what causes to happen? Shouldn't the sum be
the same with or without the "Totals" the SQL query? It appears to me that
they are not pulling the same transactions.

Below is the SQL statement. In my SQL query, I have it set up so that the
report will prompt the user for a Client ID or an Affiliate Group.

Thanks.

SELECT CLIENTS.AFFGROUP AS [Aff Group], qryBilling_Summary.[Client ID],
CLIENTS.[LEGAL NAME] AS [Client Legal Name], Sum(qryBilling_Summary.[Total
Accum Time]) AS [SumOfTotal Accum Time], Sum(qryBilling_Summary.[Interim
Amount Billed]) AS [SumOfInterim Amount Billed], Sum([Interim Amount
Billed]-[Total Accum Time]) AS [Diff of Interim & Actual]
FROM qryBilling_Summary LEFT JOIN CLIENTS ON qryBilling_Summary.[Client ID]
= CLIENTS.CLNTOID
GROUP BY CLIENTS.AFFGROUP, qryBilling_Summary.[Client ID], CLIENTS.[LEGAL
NAME]
HAVING (((CLIENTS.AFFGROUP) Like nz([Please Enter Aff Group],"*")) AND
((qryBilling_Summary.[Client ID]) Like nz([Please Enter Client ID],"*")) AND
((Sum(qryBilling_Summary.[Interim Amount Billed]))=0));

Reply to this Message

Chris2 - 16 Nov 2005 03:39 GMT

> Hello,
>
[quoted text clipped - 11 lines]
> the same with or without the "Totals" the SQL query? It appears to me that
> they are not pulling the same transactions.

AccessHelp,

1) "the sum of the "Total Accum Time" (the $ amount for the time
that we spend on a client)"

2) "is not the same as without doing the "Totals" on the SQL query."

Please forgive my obtuseness, but I'm curious how you get a "sum"
without doing a "totals" on the SQL query.

I mean, SUM([Total Accum Time]) is going to be one number.

If SUM([Total Accum Time]) is not done, then why would you expect
the same number, and why would it matter if it was different (since
it wouldn't be the SUM)?

Did you independently add up the [Total Accum Time] column to
determine its sum, and then find that the SUM([Total Accum Time])
was different than the independently calculated sum? Am I just mad
because I don't understand (anyone feel free to hop in here and tell
me)?

Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.

1) Post your Visual Basic DIMs and SETs for the ADO objects, the
connection string (with fake UID and PWD, of course!), the
instantiation of any recordsets, and key processing code.

2) Read the above link, and post a tables description. If you can
write the DDL, that would be great, because then I won't have to.

3) Post sample data (including parameter values). Some INSERT INTO
<table> SELECT col1, col2 UNION scripts would be great, too.

4) Post the expected results.

5) Post the wrong results you are getting so I can see how it
differs from the expected results (and so I can execute your query
to get the wrong results myself).

Barring that, my best advice is simplification. Strip the query
down until it only operates on the column in question. Create a
test table with a very limited number of rows where you can see, by
Mark I eyeball alone, what the SUM of the column is. Point the
simplified query at the test table, and run it, and see if the
results match or don't match your expectations.

Sincerely,

Chris O.

Reply to this Message

AccessHelp - 16 Nov 2005 17:40 GMT

Hi Chris2,

Thanks for your response. These are what I did. I ran the SQL query in the
Database view with or without "Totals" (In a query, if you do the "Totals",
you group all the text fields and sum all the numeric fields together). Then
I copy all the numbers from "Total Accum Time" for both with or without
"Totals" into Excel and added them.

The sum of numbers for both with or without "Totals" didn't come up the
same. I thought the sum of those numbers would be the same with or without
the "Totals" in a query. The "Totals" is pretty much grouped all the similar
text fields together and sum all the numeric fields of the similar text
fields?

Thanks again.

> > Hello,
> >
[quoted text clipped - 75 lines]
>
> Chris O.

Reply to this Message

Chris2 - 17 Nov 2005 06:50 GMT

> > > Hello,

<snip>

> > AccessHelp,

<snip>

> > Although meant for an sqlserver newsgroup, the
> > following link is still applicable for MS Access:
> > http://www.aspfaq.com/etiquette.asp?id=5006, and
> > is excellent when it comes to detailing how to
> > provide the information that will best enable
> > others to answer your questions.

<snip>

> > Sincerely,
> >
[quoted text clipped - 15 lines]
>
> Thanks again.

AccessHelp,

Your query has three conditions on the HAVING clause.

How did you copy a precise set of the post-HAVING clause data into
Excel? You did apply *exactly* the same conditions to that data
before copying it into Excel and =SUM()ing it, right?

From your description ("Then I copy all the numbers from "Total
Accum Time" for both with or without "Totals" into Excel and added
them."), it is as if you copied *all* the source data into Excel and
did =SUM() on it.

Once the query's HAVING clause slices out some data, the output
totals will not add up the original combined SUM of the source
column.

CREATE TABLE GroupSumTest
(col1 integer
,col2 integer
)

(Yes, there is no primary key, shoot me.)

col1, col2
1, 10
1, 20
2, 30
3, 40
3, 50
4, 60

An Excel =SUM() of col2 values gives 210.

SELECT SUM(col2) SumOfCol2
FROM GroupSumTest

SumOfCol2
210

-----

Query: Find_GrpSumTest_LE_30:

SELECT col1
,SUM(col2) as grpsumcol2
FROM GroupSumTest
GROUP BY col1
HAVING SUM(col2) <= 30

col1, grpsumcol2
1, 30
2, 30

-----
SELECT SUM(grpsumcol2) AS SumOfGroups
FROM Find_GrpSumTest_LE_30

SumOfGroups
60

210 <> 60

Sincerely,

Chris O.

Reply to this Message