Sum() randomly doubles in query

[sbatschelet]

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?

[salad]

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.

[tranceport185]

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.