By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,677 Members | 1,877 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,677 IT Pros & Developers. It's quick & easy.

Sum() randomly doubles in query

P: n/a
I am running into a really odd problem with Access and looking for some
insight as to what could be causing my issue. Basically this SQL
creates a listing of Total Sales (Sum(InvLine.Retail)) by Category
(InvLine.Cat) which seems strait forward. But today I run the report
and a category is doubled, so I went through the basic steps of
verifying the data's intergrety wih other categories etc and nothing is
diffent about the line but instead of 4.75 its 9.50. Also even tried
changing to InvLine.Price and it doubled that as well?!?!

The problem is a single line in Table InvLine which is the only line
for this category for the report that I ran.

LId IId InvDayId IType D TS TE Unit Description Contact Quantity Retail Price TGId Total UGId Exported Cat RevAccountId
65576 10999 40088 2 3/21/2006 0 0 0 Umpqua 300
#4 1 $4.75 $4.75 3 $5.13 1 $0.00 04 Fly Tying 3

Here is my SQL

SELECT InvLine.Cat, Sum(InvLine.Retail) AS TotalCat
FROM Payments INNER JOIN ((InvLine INNER JOIN Invoice ON InvLine.IId =
Invoice.IId) INNER JOIN PaySplit ON Invoice.IId = PaySplit.IId) ON
Payments.PAYId = PaySplit.PayId
WHERE (((Invoice.Total)=[Invoice].[Paid]) AND ((Payments.PayDate)
Between #3/20/2006# And #3/22/2006#))
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat;

Any Ideas?

Mar 21 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
sb*********@gmail.com wrote:
I am running into a really odd problem with Access and looking for some
insight as to what could be causing my issue. Basically this SQL
creates a listing of Total Sales (Sum(InvLine.Retail)) by Category
(InvLine.Cat) which seems strait forward. But today I run the report
and a category is doubled, so I went through the basic steps of
verifying the data's intergrety wih other categories etc and nothing is
diffent about the line but instead of 4.75 its 9.50. Also even tried
changing to InvLine.Price and it doubled that as well?!?!

The problem is a single line in Table InvLine which is the only line
for this category for the report that I ran.

LId IId InvDayId IType D TS TE Unit Description Contact Quantity Retail Price TGId Total UGId Exported Cat RevAccountId
65576 10999 40088 2 3/21/2006 0 0 0 Umpqua 300
#4 1 $4.75 $4.75 3 $5.13 1 $0.00 04 Fly Tying 3

Here is my SQL

SELECT InvLine.Cat, Sum(InvLine.Retail) AS TotalCat
FROM Payments INNER JOIN ((InvLine INNER JOIN Invoice ON InvLine.IId =
Invoice.IId) INNER JOIN PaySplit ON Invoice.IId = PaySplit.IId) ON
Payments.PAYId = PaySplit.PayId
WHERE (((Invoice.Total)=[Invoice].[Paid]) AND ((Payments.PayDate)
Between #3/20/2006# And #3/22/2006#))
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat;

Any Ideas?

Using a dumb example, let's say I have an Order and 2 customers
associated with the order. If I did a sum on the order amount
(contained in the order table) and I was linked also to the
CustomerOrder table, I woulld get a doubling value.

Any easy way to view this is to make a copy of your query. Remove the
Totals query so it becomes a simple SQL select. Now filter on the
category that it is messing up on. I think you'll find that somehow you
have 2 records of something that makes it double.
Mar 22 '06 #2

P: n/a
Son of a gun your right I was just thinking about this the wrong way
perfect thank you!! Just to tell you what happened, the cash
transactions have 2 lines total cash paid and change given that was it.

Mar 22 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.