reset query value

[Guest]

Hey there

I have a query that does this:
TXID TransactionDate PRID PrevPRID UnitPrice UnitsOrdere RunTot
54762 18-Jul-05 1 1 $1.95 -1 34
54763 03-Aug-05 1 1 $1.95 -1 33
54764 03-Aug-05 1 1 $1.95 -2 31
54765 30-Nov-05 1 1 $1.95 -2 29
54766 31-Mar-05 2 1 $4.85 81 81
54767 15-Apr-05 2 2 $4.85 -1 109
54768 03-May-05 2 2 $4.85 -1 108

What I want is to reset the running total when the PRID changes so that it
should look like this:
TXID TransactionDate PRID PrevPRID UnitPrice UnitsOrdere RunTot
54762 18-Jul-05 1 1 $1.95 -1 34
54763 03-Aug-05 1 1 $1.95 -1 33
54764 03-Aug-05 1 1 $1.95 -2 31
54765 30-Nov-05 1 1 $1.95 -2 29
54766 31-Mar-05 2 1 $4.85 81 81
54767 15-Apr-05 2 2 $4.85 -1 80
54768 03-May-05 2 2 $4.85 -1 79

Here's the SQL for the query:
SELECT TXTemp.TransactionID AS TXID, TXTemp.TransactionDate,
TXTemp.ProductID AS PRID, DLookUp("[ProductID]","TXTemp","[TransactionID] = "
& [TransactionID]-1) AS PrevPRID, TXTemp.UnitPrice, TXTemp.UnitsOrdered,
IIf([PRID]<>[PrevPRID],[UnitsOrdered],DSum("UnitsOrdered","TXTemp","[TransactionID]<=" & [TXID])) AS RunTot
FROM TXTemp
ORDER BY TXTemp.TransactionID;

Thanx
IAJG

 

[John Spencer]

I would think the following would work

SELECT TXTemp.TransactionID AS TXID, TXTemp.TransactionDate,
TXTemp.ProductID AS PRID, DLookUp("[ProductID]","TXTemp","[TransactionID] = "
& [TransactionID]-1) AS PrevPRID, TXTemp.UnitPrice, TXTemp.UnitsOrdered,
DSum("UnitsOrdered","TXTemp","[TransactionID]<=" & [TXID] & " and PRID =" &
PRID) AS RunTot
FROM TXTemp
ORDER BY TXTemp.TransactionID;

You might consider using the transaction date instead of TXID if you can't
guarantee that TXID is going to be sequential.

 

[Guest]

Yeah, I thought it should be that easy just adding in that PRID=PRID on the
DSum, tried it before and it just turns the running total into the total
total.

Another idea?

Thanx

I would think the following would work

SELECT TXTemp.TransactionID AS TXID, TXTemp.TransactionDate,
TXTemp.ProductID AS PRID, DLookUp("[ProductID]","TXTemp","[TransactionID] = "
& [TransactionID]-1) AS PrevPRID, TXTemp.UnitPrice, TXTemp.UnitsOrdered,
DSum("UnitsOrdered","TXTemp","[TransactionID]<=" & [TXID] & " and PRID =" &
PRID) AS RunTot
FROM TXTemp
ORDER BY TXTemp.TransactionID;

You might consider using the transaction date instead of TXID if you can't
guarantee that TXID is going to be sequential.

Hey there

I have a query that does this:
TXID TransactionDate PRID PrevPRID UnitPrice UnitsOrdere RunTot
54762 18-Jul-05 1 1 $1.95 -1 34
54763 03-Aug-05 1 1 $1.95 -1 33
54764 03-Aug-05 1 1 $1.95 -2 31
54765 30-Nov-05 1 1 $1.95 -2 29
54766 31-Mar-05 2 1 $4.85 81 81
54767 15-Apr-05 2 2 $4.85 -1 109
54768 03-May-05 2 2 $4.85 -1 108

What I want is to reset the running total when the PRID changes so that it
should look like this:
TXID TransactionDate PRID PrevPRID UnitPrice UnitsOrdere RunTot
54762 18-Jul-05 1 1 $1.95 -1 34
54763 03-Aug-05 1 1 $1.95 -1 33
54764 03-Aug-05 1 1 $1.95 -2 31
54765 30-Nov-05 1 1 $1.95 -2 29
54766 31-Mar-05 2 1 $4.85 81 81
54767 15-Apr-05 2 2 $4.85 -1 80
54768 03-May-05 2 2 $4.85 -1 79

Here's the SQL for the query:
SELECT TXTemp.TransactionID AS TXID, TXTemp.TransactionDate,
TXTemp.ProductID AS PRID, DLookUp("[ProductID]","TXTemp","[TransactionID] = "
& [TransactionID]-1) AS PrevPRID, TXTemp.UnitPrice, TXTemp.UnitsOrdered,
IIf([PRID]<>[PrevPRID],[UnitsOrdered],DSum("UnitsOrdered","TXTemp","[TransactionID]<=" & [TXID])) AS RunTot
FROM TXTemp
ORDER BY TXTemp.TransactionID;

Thanx
IAJG

 

[John Spencer]

Are you saying that you did this?-
DSum("UnitsOrdered","TXTemp","[TransactionID]<=" & [TXID] & " and PRID =" &
TXTemp.PRID) AS RunTot
which should give you the totals you wanted

OR did you do this
DSum("UnitsOrdered","TXTemp","[TransactionID]<=" & [TXID] & " and PRID
=PRID") AS RunTot
which should give you a running sum over all?

Notice the difference in the two. The first uses the value in the current
row of the query for criteria. The second is basically saying if the value
in the row in the table is equal to itself then include it when doing the
sum. The only time that would screen out any rows at all would be if PRID
in the table was null.

If I misunderstood you and you are doing the first, I can only walk away
muttering "Why, why, why doesn't that work?"

Yeah, I thought it should be that easy just adding in that PRID=PRID on
the
DSum, tried it before and it just turns the running total into the total
total.

Another idea?

Thanx

I would think the following would work

SELECT TXTemp.TransactionID AS TXID, TXTemp.TransactionDate,
TXTemp.ProductID AS PRID, DLookUp("[ProductID]","TXTemp","[TransactionID]
= "
& [TransactionID]-1) AS PrevPRID, TXTemp.UnitPrice, TXTemp.UnitsOrdered,
DSum("UnitsOrdered","TXTemp","[TransactionID]<=" & [TXID] & " and PRID ="
&
PRID) AS RunTot
FROM TXTemp
ORDER BY TXTemp.TransactionID;

You might consider using the transaction date instead of TXID if you
can't
guarantee that TXID is going to be sequential.

Hey there

I have a query that does this:
TXID TransactionDate PRID PrevPRID UnitPrice UnitsOrdere
RunTot
54762 18-Jul-05 1 1 $1.95 -1 34
54763 03-Aug-05 1 1 $1.95 -1 33
54764 03-Aug-05 1 1 $1.95 -2 31
54765 30-Nov-05 1 1 $1.95 -2 29
54766 31-Mar-05 2 1 $4.85 81 81
54767 15-Apr-05 2 2 $4.85 -1 109
54768 03-May-05 2 2 $4.85 -1 108

What I want is to reset the running total when the PRID changes so that
it
should look like this:
TXID TransactionDate PRID PrevPRID UnitPrice UnitsOrdere
RunTot
54762 18-Jul-05 1 1 $1.95 -1 34
54763 03-Aug-05 1 1 $1.95 -1 33
54764 03-Aug-05 1 1 $1.95 -2 31
54765 30-Nov-05 1 1 $1.95 -2 29
54766 31-Mar-05 2 1 $4.85 81 81
54767 15-Apr-05 2 2 $4.85 -1 80
54768 03-May-05 2 2 $4.85 -1 79

Here's the SQL for the query:
SELECT TXTemp.TransactionID AS TXID, TXTemp.TransactionDate,
TXTemp.ProductID AS PRID,
DLookUp("[ProductID]","TXTemp","[TransactionID] = "
& [TransactionID]-1) AS PrevPRID, TXTemp.UnitPrice,
TXTemp.UnitsOrdered,
IIf([PRID]<>[PrevPRID],[UnitsOrdered],DSum("UnitsOrdered","TXTemp","[TransactionID]<="
& [TXID])) AS RunTot
FROM TXTemp
ORDER BY TXTemp.TransactionID;

Thanx
IAJG