Skip to Content
0
Former Member
Jul 24, 2008 at 08:52 PM

Query running on Infoset-Wrong totals and calculations

16 Views

Hello everyone: Here is a following example. One query output is in the compact form and the other one is detialed form. It may not make much sense to look here but if you copy it in excel you can see it better. Unfortunately I did not have a better way of explaining it. The problem is with the adding (aggregating) of numbers. If you notice that the Service Sales ($667.00) amount for Nov 2007 has doubled. You can find this by comapring the Nov 2007 amount in compact query to Nov 2007 detailed query output. I don't know why this happens for Nov 2007 and not for Oct 2007. Another problem is that only some of the Service and Hardware sales amounts for Dec 2007 have doubled or halved. If you compare the totals and details of the two outputs you will see what's happening. I appolgize for such a detialed qs and example but this is all I could come up with.

I;m racing against time with this so I hope someone can come up with an answer as it is already late night in Rome. Please feel free to ask me any details. I will be up all night.

Ciao!

Romina

P.S.: I just realized that it does not look good by pasting it in excel so please let me know and I will send you the excel file. Thanks

This is a query output in compact form

Month Sales Cnt. Service Sales Hardware Sales T. Sales Average Sales

OCT 2007 7 $ 552.00 $ 3.92 $ 555.92 $ 79.42

NOV 2007 5 $ 667.80 $ 193.33 $ 861.13 $ 172.23

DEC 2007 59 $ 135,366.73 $ 43,729.46 $ 179,096.19 $ 3,035.53

This is the detailed form of the query.

Month Cust Hierarchy Sales Ordr Model Sales Item A/C Indicator Activity Type Workcenter Service Sales Labor Cost Material Cost Total (M+L) Sales Ord Count Average Cost

OCT 2007 Not Assigned 4000022 ABC 1000/510060 PW Rep WrkCtr1 1.00 EA $ 3.92 $ 3.92

1000/510085 PW Rep WrkCtr1

C Rep WrkCtr1

1000/921070 PW Rep WrkCtr1

C Rep WrkCtr1

1000/943070 C Rep WrkCtr1 8.000 H $ 160.00 $ 160.00

1000/943079 C Rep WrkCtr1 4.000 H $ 80.00 $ 80.00

4000024 # 1000/510065 W Rep WrkCtr1 1.00 EA $ 0.00 0.00

1000/510085 W Rep WrkCtr1

1000/943070 W Rep WrkCtr1 2.000 H $ 40.00 $ 40.00

1000/943079 W Rep WrkCtr1 1.000 H $ 20.00 $ 20.00

4000025 # 1000/510065 W Rep WrkCtr2 2.00 EA $ 0.00 0.00

1000/921070 W Rep WrkCtr2

1000/943070 W Rep WrkCtr2 0.300 H $ 6.00 $ 6.00

4000028 # 1000/921070 BP Rep WrkCtr1

1000/943070 BP Rep WrkCtr1 3.000 H $ 60.00 $ 60.00

1000/943079 BP Rep WrkCtr1 2.000 H $ 40.00 $ 40.00

4000029 # 1000/510065 C Rep WrkCtr1 1.00 EA $ 0.00 0.00

1000/921070 C Rep WrkCtr1

1000/943070 C Rep WrkCtr1 2.000 H $ 40.00 $ 40.00

1000/943079 C Rep WrkCtr1 2.000 H $ 40.00 $ 40.00

4000030 # 1000/510085 BP Rep WrkCtr1

1000/943070 BP Rep WrkCtr1 2.000 H $ 40.00 $ 40.00

1000/943079 BP Rep WrkCtr1 1.000 H $ 20.00 $ 20.00

4000032 # 1000/510065 C Rep WrkCtr2 1.00 EA $ 0.00 0.00

1000/921070 C Rep WrkCtr2

1000/943070 C Rep WrkCtr2 0.300 H $ 6.00 $ 6.00

Result 27.600 H 6.00 EA $ 552.00 $ 3.92 $ 555.92 7 $ 79.42

NOV 2007 Not Assigned 4000060 # 1000/510065 C Rep WrkCtr2 2.00 EA $ 106.77 $ 106.77

1000/921070 C Rep WrkCtr2

Not assigned Rep WrkCtr2

1000/941070 Not assigned Rep WrkCtr2 0.000 $ 3.00 $ 3.00

1000/943070 C Rep WrkCtr2 3.000 H $ 60.00 $ 60.00

4000061 # 1000/510065 W Rep WrkCtr2 2.00 EA $ 493.28 $ 493.28

1000/921070 W Rep WrkCtr2

Not assigned Rep WrkCtr2

1000/941070 Not assigned Rep WrkCtr2 0.000 $ 6.00 $ 6.00

1000/943070 W Rep WrkCtr2 6.000 H $ 120.00 $ 120.00

4000062 # 1000/510065 BP Rep WrkCtr2 1.00 EA $ 246.64 $ 246.64

1000/921070 BP Rep WrkCtr2

Not assigned Rep WrkCtr2

1000/941070 Not assigned Rep WrkCtr2 0.000 $ 1.30 $ 1.30

1000/943070 BP Rep WrkCtr2 1.300 H $ 26.00 $ 26.00

4000109 # 1000/510065 W Rep WrkCtr2 1.00 EA $ 246.64 $ 246.64

1000/941070 Not assigned Rep WrkCtr2 0.000 $ 1.10 $ 1.10

1000/943070 W Rep WrkCtr2 1.100 H $ 22.00 $ 22.00

4000115 # 1000/510065 W Rep WrkCtr2 -4.00 EA -$ 900.00 -$ 900.00

1000/941070 Not assigned Rep WrkCtr2 0.000 $ 4.50 $ 4.50

1000/943070 W Rep WrkCtr2 4.500 H $ 90.00 $ 90.00

Result 15.900 H 2.00 EA $ 333.90 $ 193.33 $ 527.23 5 $ 172.23

DEC 2007 Not Assigned 4000063 # 1000/510065 BP Rep WrkCtr2 2.00 EA $ 142.85 $ 142.85

1000/921070 BP Rep WrkCtr2

Not assigned Rep WrkCtr2

1000/941070 Not assigned Rep WrkCtr2 0.000 $ 4.50 $ 4.50

1000/943070 BP Rep WrkCtr2 2.250 H $ 45.00 $ 45.00

4000110 # 1000/943070 Not assigned Rep WrkCtr2 22.100 H $ 442.00 $ 442.00

4000142 # 1000/510060 W Rep WrkCtr3 1.00 EA $ 3.92 $ 3.92

1000/510065 W Rep WrkCtr3 1.00 EA $ 67.66 $ 67.66

1000/941070 Not assigned Rep WrkCtr3 0.000 $ 1.30 $ 1.30

1000/943070 W Rep WrkCtr3 1.300 H $ 26.00 $ 26.00

4000143 # 1000/510065 BP Rep WrkCtr3 2.00 EA $ 225.00 $ 225.00

1000/941070 Not assigned Rep WrkCtr3 0.000 $ 1.20 $ 1.20

1000/943070 BP Rep WrkCtr3 1.200 H $ 24.00 $ 24.00

4000146 # 1000/510065 C Rep WrkCtr2 1.00 EA $ 1.50 $ 1.50

1000/941070 Not assigned Rep WrkCtr2 0.000 $ 1.25 $ 1.25

1000/943070 C Rep WrkCtr2 1.250 H $ 25.00 $ 25.00

4000152 # 1000/510065 C Rep WrkCtr2 1.00 EA $ 5.75 $ 5.75

1000/941070 Not assigned Rep WrkCtr2 0.000 $ 1.25 $ 1.25

1000/943070 C Rep WrkCtr2 1.250 H $ 25.00 $ 25.00

4000157 # 1000/510065 W Rep WrkCtr4 2.00 EA $ 0.00 0.00

1000/941070 Not assigned Rep WrkCtr4 0.000 $ 3.00 $ 3.00

1000/943070 BP Rep WrkCtr4 2.000 H $ 40.00 $ 40.00

Free of Charge Rep WrkCtr4 1.000 H $ 20.00 $ 20.00

4000158 # 1000/941070 Not assigned Rep WrkCtr2 0.000 $ 1.30 $ 1.30

1000/943070 BP Rep WrkCtr2 1.300 H $ 26.00 $ 26.00

4000165 # 1000/941070 Not assigned Rep WrkCtr4 0.000 $ 1.10 $ 1.10

1000/943070 W Rep WrkCtr4 1.100 H $ 22.00 $ 22.00

4000178 # 1000/510065 C Rep WrkCtr4 1.00 EA $ 75.00 $ 75.00

1000/941070 Not assigned Rep WrkCtr4 0.000 $ 1.30 $ 1.30

1000/943070 C Rep WrkCtr4 1.300 H $ 26.00 $ 26.00

4000201 PX4IB 1000/510065 BP #/REPRFL WrkCtr5 2.00 EA $ 40.15 $ 40.15

1000/941070 Not assigned #/REPRFL WrkCtr5 0.000 $ 2.00 $ 2.00

1000/943070 BP #/REPRFL WrkCtr5 3.000 H $ 120.00 $ 120.00

4000202 # 1000/510065 W Rep WrkCtr2 1.00 EA $ 75.00 $ 75.00

1000/941070 Not assigned Rep WrkCtr2 0.000 $ 5.50 $ 5.50

1000/943070 W Rep WrkCtr2 5.500 H $ 110.00 $ 110.00

4000205 # 1000/941070 Not assigned Rep WrkCtr6 0.000 $ 162.00 $ 162.00

1000/943070 Not assigned Rep WrkCtr6 423.000 H $ 8,460.00 $ 8,460.00

4000236 # 1000/943070 Not assigned Rep WrkCtr7 2.000 H $ 40.00 $ 40.00

4000237 # 1000/510060 BP Rep WrkCtr2 1.00 EA $ 86.86 $ 86.86

1000/941070 Not assigned Rep WrkCtr2 0.000 $ 1.30 $ 1.30

1000/943070 BP Rep WrkCtr2 1.300 H $ 26.00 $ 26.00

4000239 # 1000/510060 BP Rep WrkCtr8 1.00 EA $ 86.86 $ 86.86

1000/510065 BP Rep WrkCtr8 1.00 EA $ 261.64 $ 261.64

1000/941070 Not assigned Rep WrkCtr8 0.000 $ 1.30 $ 1.30

1000/943070 BP Rep WrkCtr8 1.300 H $ 26.00 $ 26.00

4000243 # 1000/941070 Not assigned Rep WrkCtr2 0.000 $ 3.00 $ 3.00

1000/943070 BP Rep WrkCtr2 3.000 H $ 60.00 $ 60.00

4000255 # 1000/941070 Not assigned Rep WrkCtr2 0.000 $ 1.30 $ 1.30

1000/943070 W Rep WrkCtr2 1.300 H $ 26.00 $ 26.00

4000259 # 1000/510060 BP Rep WrkCtr2 1.00 EA $ 976.81 $ 976.81

1000/941070 Not assigned Rep WrkCtr2 0.000 $ 1.30 $ 1.30

1000/943070 BP Rep WrkCtr2 1.300 H $ 26.00 $ 26.00

4000260 # 1000/510065 Free of Charge Rep WrkCtr4 1.00 EA $ 0.00 0.00

1000/941070 Not assigned Rep WrkCtr4 0.000 $ 11.00 $ 11.00

1000/943070 W Rep WrkCtr4 11.000 H $ 220.00 $ 220.00

4000261 # 1000/510060 BP Rep WrkCtr8 2.00 EA $ 173.72 $ 173.72

1000/510065 BP Rep WrkCtr8 2.00 EA $ 508.28 $ 508.28

1000/941070 Not assigned Rep WrkCtr8 0.000 $ 2.60 $ 2.60

1000/943070 BP Rep WrkCtr8 3.900 H $ 78.00 $ 78.00

4000263 # 1000/941070 Not assigned Rep WrkCtr2 0.000 $ 3.00 $ 3.00

1000/943070 BP Rep WrkCtr2 3.000 H $ 60.00 $ 60.00

4000280 # 1000/510065 BP Rep WrkCtr2 2.00 EA $ 106.77 $ 106.77

1000/943070 BP Rep WrkCtr2 3.000 H $ 60.00 $ 60.00

4000284 # 1000/921070 BP Rep WrkCtr2

W Rep WrkCtr2

1000/943070 BP Rep WrkCtr2 406.600 H $ 13,751.21 $ 13,751.21

W Rep WrkCtr2 506.550 H $ 17,131.52 $ 17,131.52

4000311 # 1000/943070 Not assigned Rep WrkCtr4 11.000 H $ 220.00 $ 220.00

4000314 # 1000/943070 Not assigned Rep WrkCtr4 10.000 H $ 200.00 $ 200.00

4000315 PX4IB 1000/510065 BP #/MILES WrkCtr9 2.00 EA $ 259.22 $ 259.22

#/REPRFL WrkCtr9 2.00 EA $ 259.22 $ 259.22

#/TRAVEL WrkCtr9 2.00 EA $ 259.22 $ 259.22

1000/943070 BP #/MILES WrkCtr9 2.000 H $ 80.00 $ 80.00

#/REPRFL WrkCtr9 2.000 H $ 80.00 $ 80.00

#/TRAVEL WrkCtr9 2.000 H $ 80.00 $ 80.00

1000/943079 BP #/MILES WrkCtr9 1.000 H $ 20.00 $ 20.00

#/REPRFL WrkCtr9 1.000 H $ 20.00 $ 20.00

#/TRAVEL WrkCtr9 1.000 H $ 20.00 $ 20.00

1000/943085 BP #/MILES WrkCtr9

#/REPRFL WrkCtr9

#/TRAVEL WrkCtr9

4000317 CN3B 1000/510065 BP #/REPRFL WrkCtr9 3.00 EA $ 249.45 $ 249.45

1000/943070 C #/REPRFL WrkCtr9 2.000 H $ 80.00 $ 80.00

4000321 # 1000/943070 Not assigned Rep WrkCtr4 10.000 H $ 200.00 $ 200.00

4000323 # 1000/510065 Free of Charge Rep WrkCtr4 4.00 EA $ 0.00 0.00

1000/943070 Free of Charge Rep WrkCtr4 10.000 H $ 200.00 $ 200.00

4000325 # 1000/943070 Not assigned Rep WrkCtr4 10.000 H $ 200.00 $ 200.00

4000326 # 1000/943070 Not assigned Rep WrkCtr4 11.100 H $ 222.00 $ 222.00

4000354 # 1000/510060 BP Rep WrkCtr10 1.00 EA $ 86.86 $ 86.86

1000/510065 BP Rep WrkCtr10 1.00 EA $ 261.64 $ 261.64

1000/943070 BP Rep WrkCtr10 1.500 H $ 30.00 $ 30.00

C Rep WrkCtr10 0.600 H $ 12.00 $ 12.00

4000356 # 1000/510065 BP Rep WrkCtr2 2.00 EA $ 78.22 $ 78.22

1000/943070 BP Rep WrkCtr2 9.000 H $ 180.00 $ 180.00

4000357 # 1000/510065 BP Rep WrkCtr2 1.00 EA $ 39.11 $ 39.11

1000/943070 BP Rep WrkCtr2 3.000 H $ 60.00 $ 60.00

4000358 # 1000/943070 BP Rep WrkCtr2 9.000 H $ 180.00 $ 180.00

4000359 # 1000/943070 Not assigned Rep WrkCtr4 22.200 H $ 444.00 $ 444.00

4000360 # 1000/943070 Not assigned Rep WrkCtr4 10.000 H $ 200.00 $ 200.00

4000362 # 1000/943070 Not assigned Rep WrkCtr4 10.000 H $ 200.00 $ 200.00

4000363 # 1000/943070 Not assigned Rep WrkCtr4 10.000 H $ 200.00 $ 200.00

4000364 # 1000/510065 Free of Charge Rep WrkCtr4 1.00 EA $ 0.00 0.00

1000/943070 Free of Charge Rep WrkCtr4 13.400 H $ 268.00 $ 268.00

Not assigned Rep WrkCtr4 1.200 H $ 24.00 $ 24.00

4000365 # 1000/943070 Not assigned Rep WrkCtr4 11.100 H $ 222.00 $ 222.00

4000381 # 1000/943070 Free of Charge Rep WrkCtr4 13.200 H $ 264.00 $ 264.00

4000382 # 1000/510065 Free of Charge Rep WrkCtr4 1.00 EA $ 0.00 0.00

1000/943070 Free of Charge Rep WrkCtr4 1.100 H $ 22.00 $ 22.00

Not assigned Rep WrkCtr4 2.000 H $ 40.00 $ 40.00

4000384 # 1000/943070 BP Rep WrkCtr4 12.200 H $ 244.00 $ 244.00

Free of Charge Rep WrkCtr4 24.300 H $ 486.00 $ 486.00

Not assigned Rep WrkCtr4 25.500 H $ 510.00 $ 510.00

4000385 # 1000/943070 Free of Charge Rep WrkCtr4 23.300 H $ 466.00 $ 466.00

4000400 # 1000/510060 Free of Charge Rep WrkCtr4 6.00 EA $ 3,570.44 $ 3,570.44

1000/510065 Not assigned Rep WrkCtr4 6.00 EA $ 0.00 0.00

1000/943070 BP Rep WrkCtr4 20.000 H $ 400.00 $ 400.00

Free of Charge Rep WrkCtr4 110.800 H $ 2,216.00 $ 2,216.00

4000401 # 1000/510060 BP Rep WrkCtr4 7.00 EA $ 4,176.32 $ 4,176.32

1000/510065 Not assigned Rep WrkCtr4 3.00 EA $ 0.00 0.00

1000/943070 Free of Charge Rep WrkCtr4 47.600 H $ 952.00 $ 952.00

Not assigned Rep WrkCtr4 26.600 H $ 532.00 $ 532.00

4000402 # 1000/510060 BP Rep WrkCtr4 3.00 EA $ 1,817.64 $ 1,817.64

Free of Charge Rep WrkCtr4 4.00 EA $ 2,358.68 $ 2,358.68

1000/510065 Not assigned Rep WrkCtr4 3.00 EA $ 0.00 0.00

1000/943070 BP Rep WrkCtr4 22.200 H $ 444.00 $ 444.00

Free of Charge Rep WrkCtr4 13.300 H $ 266.00 $ 266.00

4000403 # 1000/510060 Free of Charge Rep WrkCtr4 3.00 EA $ 1,817.64 $ 1,817.64

Not assigned Rep WrkCtr4 4.00 EA $ 2,358.68 $ 2,358.68

1000/510065 Not assigned Rep WrkCtr4 3.00 EA $ 0.00 0.00

1000/943070 BP Rep WrkCtr4 12.200 H $ 244.00 $ 244.00

Free of Charge Rep WrkCtr4 23.300 H $ 466.00 $ 466.00

Not assigned Rep WrkCtr4 25.500 H $ 510.00 $ 510.00

4000404 # 1000/510060 BP Rep WrkCtr4 12.00 EA $ 7,270.56 $ 7,270.56

Free of Charge Rep WrkCtr4 20.00 EA $ 11,793.40 $ 11,793.40

1000/510065 Not assigned Rep WrkCtr4 6.00 EA $ 0.00 0.00

1000/943070 Not assigned Rep WrkCtr4 1,928.800 H $ 38,576.00 $ 38,576.00

4000406 # 1000/510065 BP Rep WrkCtr2 2.00 EA $ 106.77 $ 106.77

1000/943070 BP Rep WrkCtr2 6.700 H $ 134.00 $ 134.00

4000407 # 1000/510060 BP Rep WrkCtr4 7.00 EA $ 4,176.32 $ 4,176.32

1000/510065 Not assigned Rep WrkCtr4 3.00 EA $ 0.00 0.00

1000/943070 BP Rep WrkCtr4 1,029.900 H $ 20,598.00 $ 20,598.00

Not assigned Rep WrkCtr4 937.700 H $ 18,754.00 $ 18,754.00

4000409 # 1000/510065 BP Rep WrkCtr2 2.00 EA $ 106.77 $ 106.77

1000/943070 BP Rep WrkCtr2 3.000 H $ 60.00 $ 60.00

4000412 # 1000/510065 Free of Charge Rep WrkCtr2 2.00 EA $ 78.22 $ 78.22

1000/943070 Free of Charge Rep WrkCtr2 6.000 H $ 120.00 $ 120.00

4000420 # 1000/943070 Not assigned Rep WrkCtr4 11.000 H $ 220.00 $ 220.00

4000426 # 1000/510065 BP Rep WrkCtr2 1.00 EA $ 39.11 $ 39.11

1000/943070 BP Rep WrkCtr2 6.000 H $ 120.00 $ 120.00

4000431 # 1000/510065 BP Rep WrkCtr2 1.00 EA $ 246.64 $ 246.64

1000/943070 BP Rep WrkCtr2 3.000 H $ 60.00 $ 60.00

4000462 # 1000/943070 Free of Charge Rep WrkCtr4 1.100 H $ 22.00 $ 22.00

Result 5,922.200 H 143.00 EA $ 131,494.23 $ 44,247.90 $ 175,742.13 59 $ 3,035.53

Edited by: Romina on Jul 24, 2008 10:53 PM