Duplicate values in a runningsum query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DevourU - 08 Nov 2005 17:47 GMT

I am having a problem when I hit duplicate values. The running sum does not
change. Can I add 1 or .01 or anything to prevent duplicates from happening?

SELECT DISTINCT [ABC Cycle Preview].extcost AS ExtStdCost, Sum([ABC Cycle
Preview].extcost) AS SumOfExtCost, Format(DSum("ExtCost","ABC Cycle
Preview","[ExtCost]>=" & [ExtStdCost] & ""),"0,000.00") AS RS, [ABC Cycle
Preview].descript, [ABC Cycle Preview].onhand, [ABC Cycle Preview].stdcost

Help?

-JS

Reply to this Message

David S - 09 Nov 2005 00:34 GMT

Try removing the "DISTINCT" after "SELECT"

Reply to this Message

DevourU - 09 Nov 2005 17:00 GMT

Thankx for the reply. No difference without DISTINCT I am now creating the
table with a uniqueID if it will help. I am trying to have a query running
sum sorted by cost descending, and duplicates costs are getting in the way.

-JS

> Try removing the "DISTINCT" after "SELECT"

Reply to this Message

David S - 09 Nov 2005 22:51 GMT

OK - if you can you post the complete SQL of your query, the table fields,
and some sample data, we should be able to get to the bottom of it...

Reply to this Message

DevourU - 10 Nov 2005 18:36 GMT

Awesome and thankx. RS is runningsum. I need to sort ExtStdCost descending
to calc %'s. RSID is autonumber, partno is no duplicate text. Here is my
problem: You can see RS does not change on duplicate values.

ExtStdCost RS descript onhand stdcost partno RSID
$72.24 379,481.64 EM,FRONT CONNECTOR,40-POS 3 $24.08 685-11509-000
501
$72.00 379,697.64 CBLP,XFER CHMB LIN INTK SW 25KAX20K 2 $36.00
018-14233-001 503
$72.00 379,697.64 PS,DC/DC,9V OUT,5A 24V-IN,ADJ ISR 3 $24.00
660-08325-000 504
$72.00 379,697.64 TB,TWIN,GND,DMT,UK3_TW PE 24 $3.00 673-11721-000
502
$71.92 379,769.56 SM,COVER,WALKWAY,5500M/F TWR 2 $35.96 714-05405-000
505

Entire SQL:

SELECT [ABC Cycle Preview].extcost AS ExtStdCost, Format(DSum("ExtCost","ABC
Cycle Preview","[ExtCost]>=" & [ExtStdCost] & ""),"0,000.00") AS RS, [ABC
Cycle Preview].descript, [ABC Cycle Preview].onhand, [ABC Cycle
Preview].stdcost, [ABC Cycle Preview].partno, [ABC Cycle Preview].RSID
FROM [ABC Cycle Preview]
GROUP BY [ABC Cycle Preview].extcost, [ABC Cycle Preview].descript, [ABC
Cycle Preview].onhand, [ABC Cycle Preview].stdcost, [ABC Cycle
Preview].partno, [ABC Cycle Preview].RSID
HAVING ((([ABC Cycle Preview].onhand)>0))
ORDER BY [ABC Cycle Preview].extcost DESC;

-JS

> OK - if you can you post the complete SQL of your query, the table fields,
> and some sample data, we should be able to get to the bottom of it...

Reply to this Message

DevourU - 10 Nov 2005 18:48 GMT

Oooops, this should be easier to view.

ExtStdCost RS onhand stdcost partno RSID
$72.24 379,481.64 3 $24.08 685-11509-000 501
$72.00 379,697.64 2 $36.00 018-14233-001 503
$72.00 379,697.64 3 $24.00 660-08325-000 504
$72.00 379,697.64 24 $3.00 673-11721-000 502
$71.92 379,769.56 2 $35.96 714-05405-000 505

Awesome and thankx. RS is runningsum. I need to sort ExtStdCost descending
to calc %'s. RSID is autonumber, partno is no duplicate text. Here is my
problem: You can see RS does not change on duplicate values.

Entire SQL:
SELECT [ABC Cycle Preview].extcost AS ExtStdCost, Format(DSum("ExtCost","ABC
Cycle Preview","[ExtCost]>=" & [ExtStdCost] & ""),"0,000.00") AS RS, [ABC
Cycle Preview].descript, [ABC Cycle Preview].onhand, [ABC Cycle
Preview].stdcost, [ABC Cycle Preview].partno, [ABC Cycle Preview].RSID
FROM [ABC Cycle Preview]
GROUP BY [ABC Cycle Preview].extcost, [ABC Cycle Preview].descript, [ABC
Cycle Preview].onhand, [ABC Cycle Preview].stdcost, [ABC Cycle
Preview].partno, [ABC Cycle Preview].RSID
HAVING ((([ABC Cycle Preview].onhand)>0))
ORDER BY [ABC Cycle Preview].extcost DESC;

> OK - if you can you post the complete SQL of your query, the table fields,
> and some sample data, we should be able to get to the bottom of it...

Reply to this Message

David S - 10 Nov 2005 23:58 GMT

Um, I think that's made it worse. Is your original table is [ABC Cycle
Preview] with fields extcost, RS, descript, onhand, stdcost, partno, RSID?
Your sample data says ExtStdCost, but that seems to be a field from your
query rather than in the table yourself. What's actually in these fields?
Some are pretty obvious, like:
descript - product description
partno - product part number
RSID - autonumber for uniqueness
stdcost - standard cost for the part in question
onhand - stock on hand of the part in question

The others are less obvious:
extcost - external cost? Is this the cost for someone else to buy this part?
RS - running sum. A running sum of what?

Because I don't understand what the running sum is, I have no idea what
you're trying to achieve with this part of your query:
Format(DSum("ExtCost","ABC Cycle Preview","[ExtCost]>=" & [ExtStdCost] & ""),
"0,000.00") AS RS

I don't understand your use of the GROUP BY clause either, because you seem
to have included pretty much every field from the table in there, include the
quantity type fields that you would normally sum or average as part of a
group by (like onhand).

Why do you need it sorted by descending cost, and why would this help you
calculate a percentage? What is it a percentage of?

Sorry, that's a lot of questions, but I've gotten very confused about what
you're trying to achieve - think of me as dumb and you're trying to eplain it
to me as simply as possible :) I might be on line for a few more hours, but
then it's the weekend here in Australia and I won't be checking again until
Monday 14/11.

Reply to this Message