Totals keep changing

[kmclane]

I have a bunch of data tables that I pull data from for a report. I am trying to get a grand total version set up, but I find that adding certain tables in changes totals on sum I already had set up. This makes no sense to me at all. If I had all the data in one table it wouldn't be a problem, but trying to tie them into one query is driving me nuts. I have three Auth income fields summing in my query individually, if I add them together they total to the exact amount. Then I add another table so I can sum a field in it, and when I run the query, two of my three totals have increased! How is this possible? It shouldn't be possible. My main problem is that these tables are not always really related. This isn't what you would even call a real database, it's just a collection of data that nobody put any thought into before they set it up. I am about to give up on this. I can match pieces, but when I try to combine the pieces I get garbage. Anybody have any useful suggestions? Other than redesigning the database, which I cannot do. Thanks, Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[amorous]

May be the field types are different. I mean you may be trying to add a Int Field to a Char field and so on...and that may be reason for the garbage values...

Why dont you do a CINT(yourvalue) and then sum them all...

-VJ

 

[KenReay]

Hi

Is adding tables to your query causing rows from the table with the values you are summing to appear more than once, eg say you had a POHeader Table (PK PO Number) with a column DiscountAmount, and a PO Item Table with PK PO Number / ItemNo. If you join on PO Number and sum Discount amount, you will get wrong answer, since it will appear once per item not once per header

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[kmclane]

The two tables I am currently pulling my hair out over have one field in common, MID(Merchant ID). My first table is the main activity table for the month. I am summing [VMCAuthInc], [TEAuthInc] and [NCAuthInc]. When I add these sums (manually or on a report)I get the correct total. Now an unrelated total I need is [DiscAdj]. This is in a table CBLAdj. It has the MID field. It only has 15-20 records, as opposed to the other which has 7200+. So I add the [DiscAdj] field. Now, depending on what kind of join I do I get different results. Regardless of which way I do it my Auth income totals for two of the three fields increase by a combined total of $493.25. My [NCAuthInc] total is unaffected. Makes no sense to me. There are no Authinc fields in the DiscAdj table. I'm sooo Confused!
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[JonFer]

Can you post the tables with their primary keys and then one example of the SQL you are running that gives incorrect totals?

 

[PHV]

Why not creating two different summing queries and then, if possible, create a third query with probably an outer join between the two summing queries ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmclane]

I found that if I keep the disparate tables in separate queries, I can add all my "total" queries in one without any joins as I am not doing any calculation. I had to take two steps back to go one step forward. Thanks. By the way, a big part of my problem is that nobody seems to know what a primary key is. These people have no clue how to design a database. I'm just picking up the pieces.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg