Sum a sum of field? Sum of a count field?

[bsnapool]

Hi,

When I am doing this, it is coming up with silly figures...

I have got a query which consists of 4 costs which have been summed in a previous query. The result of this query is a rows of datas per criteria E.g. Day 4, Day 4, Day5. When I sum this, I would expect a sum of 13, but i am getting really high numbers?

Anyone care to enlighten me on this??

Thanks

 

[bsnapool]

Is there any reason why some are duplicating and others are not? I have tried another sequence where initial query not sum or count and sum and count at the end but this is not coming out with correct figures either?

Why would some be duplicated and not others? I tried dividing by 2 but this does not help either.

Anyone lend a hand???

Any help would be appreciated.

 

[bsnapool]

Anyone lend a hand???

 

[Brianwarnock]

I think that you may have to attach your db to get help here as it should work ok from the info given.

Brian

 

[bsnapool]

Thew DB is huge... I can put some screen shots up to make what im saying more clear... 2 ticks...

 

[bsnapool]

Here you go....

More information.

Any ideas???

http://www.box.net/shared/xscx8o6ck4

 

[Brianwarnock]

I have a hunch, no more than that, that it is to do with the complexity or rather no of joins, of the original query, turn that into a make table and run your second query against the table.
If that solves the problem ie gives the correct answer, repost asking the gurus why?

Brian

 

[bsnapool]

I have tried your suggestion, although i was a bit sceptical. This still gave the same outcome. I am struggling to see why this would happen?

 

[Mike375]

Perhaps you could create a bank data base and import the tables/queries in question. Delete the table records except leave a couple of dummy records. Compact, Zip and attach

 

[bsnapool]

Shall try this tomorrow. Thanks for your reply

 

[Brianwarnock]

I have tried your suggestion, although i was a bit sceptical. This still gave the same outcome. I am struggling to see why this would happen?

I understand your scepticism, but i remember that several years ago a inherited a non normalised DB (not saying that's your case) and had to run queries on queries on queries to get the info out, but it occasionally failed , but creating temp tables and then running queries on that worked, but cannot remember details, just thought it worth a shot.

Brian

 

[bsnapool]

Brian,

After making tables from queries.. (3 Times) it has given me the results I wanted. Thanks for your info. appreciated. I can now take the thorn out of my side.

Cheers

 

[Brianwarnock]

Glad I could help, just to add a bit if as this is the first time you've had to do this I created macros to run the sequence of events required to simplify the execution.

Brian

 

[bsnapool]

Sound good to me. Could you supply any samples of this code?? Could be very useful in the future

 

[Brianwarnock]

Now that I'm retired I don't have any samples to hand, but if you go to create a new macro you will find all the info you need to get started in the drop down lists. In your case Echo turned off and a series of Openquery followed by Close then Echo back on Commands should do the trick, even the parameters to these commands, the on off for echo or query names etc, are from lists.

Brian

 

[bsnapool]

Thanks Brian....