Test against different rows 1

[ooch1]

Hello,

I was wondering whether it is possible to test against different rows within a table or query.

For example, if my data is set out as follows:

MPR Contract P/unit Status
100 2500 1.99
100 2501 1.99 Contract Update
200 1000 1.99
200 1000 2.01 Price Update

What i would like to do is go down each row and where there is a change in either price or contract then update the status as suggested.

I am not to sure whether this can be acheived, as i cannot find any row or column functions?

All suggestions welcomed!!

OOch

 

[SJMcAbney]

Why not keep the same item but give it a DateExpired field?

Stewart

 

[ooch1]

SJMcAbney,
Thanks for the suggestion, but the way it is flagged, i have to look at the IDs and the price to see what changes have occured.

The statuses do not currently exist, i want to create these to reflect the changes in the data.

OOch

 

[PHV]

What is the criteria for the newer row ?

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

 

[ooch1]

PHV,

The creteria would be something along the lines of:

If (MPR of row -1 = MPR of current row) AND (P/unit or contract of current row <> P/unit or contract of current row) then update Status to Price update or contract Update respectively, else test the next row.

I hope this makes sense.

OOch

 

[PHV]

How are the row sorted (ordered) ?
For example, for the following datas:
200 1000 1.99
200 1000 2.01
how do you know the new price is 2.01 ?
I.E. what is the ORDER BY clause that garantee the chronological order ?

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

 

[ooch1]

PHV - The ORDER BY, is ascending on the contract number and then the price and everthing is grouped by the MPR.

OOch

 

[PHV]

First create a ranking query named, say, qryMPRrank :
SELECT A.MPR, A.Contract, A.[P/unit], Count(*) AS Rank
FROM tblMPR AS A INNER JOIN tblMPR AS B ON (B.Contract<A.Contract OR (B.Contract=A.Contract AND B.[P/unit]<=A.[P/unit])) AND (B.MPR=A.MPR)
GROUP BY A.MPR, A.Contract, A.[P/unit];

Then create a make table query named, say, qryMPRnewStatus :
SELECT A.MPR, A.Contract AS ContractA, A.[P/unit] AS PunitA, B.Contract AS ContractB, B.[P/unit] AS PunitB, IIf(A.Contract<>B.Contract,'Contract Updated',IIf(A.[P/unit]<B.[P/unit],'Price updated','?')) AS NewStatus
INTO tblMPRnewStatus
FROM qryMPRrank AS A INNER JOIN qryMPRrank AS B ON (A.MPR=B.MPR) AND (A.Rank=B.Rank-1);

And now the final update query:
UPDATE tblMPR AS M INNER JOIN tblMPRnewStatus AS B
ON (M.[P/unit]=B.PunitB) AND (M.Contract=B.ContractB) AND (M.MPR=B.MPR)
SET M.Status = B.NewStatus;

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

 

[ooch1]

PHV - That is an excellent post and i'm just trying to check my results to ensure they are correct, but the initial outlook looks good!!

One thing i'm not to sure on is the first ranking query and what would happen if there are more than one change, because i'm unsure what is actually being counted? plus i think that null values may be impacting the info.

On a more general note, in the first query if the meters are the same why will it automitacally check against all the values to check if one is lower?

Thanks for this help!

OOch