I may have outsmarted myself

[AdventureBob]

Ok, I have developed a sturcture that is highly efficient, very normalized and I can do nearly anything I need to with the structure. Except... I need to be able to find all of the transactions that have made it to a given step and then sum the time from the start to the identified step.



ContractsTbl
ContractNum (Key)
Description
Cost
Owner

XactionsTbl
XactionID
ContractNum (links to ContractTbl)
Step
StepStart
StepStop
StepNote

So this works very well for a given set of steps, theres another table called ContractSteps but for simplicity assume that Step.XactionTbl is represented by a digit 1 through 7. Our process requires that we account for recursion, and this structure works very well for that as well as keeping notes at each step. So the psuedo qry is:

Find all the contracts at step X and sum the time it took to get there.
Thanks in advance for any help

[orange]

Based on the info and my own test data (very limited)
and just using your xactionsTbl

Define 2 queries.
query1: xactionsContractsThatHavereachedStep

PARAMETERS TopStep IEEEDouble;
SELECT xActionstbl.contractNum
FROM xActionstbl
WHERE step = TopStep;

query2: xactionsTotalTimeInElapsedDays

PARAMETERS TopStep IEEEDouble;
SELECT xactionstbl.contractNum AS ContactsInvovled
, xactionstbl.step AS StepsInvolved
, Sum(DateDiff("d",[xActionstbl]![stepstart],[xActionstbl]![stepStop])) AS sumofTime_days
FROM xactionstbl INNER JOIN xactionsContractsThatHaveReachedStep
ON xactionstbl.contractNum = xactionsContractsThatHaveReachedStep.contractNum
WHERE (((xactionstbl.step) Between 1 And [TopStep]))
GROUP BY xactionstbl.contractNum, xactionstbl.step;

Run only query2; at the prompt for TopStep enter the number of the step 1-7

These totals include the elapsed days for each step including the TopStep.

This is a quick response to your question. May not be practical and it assumes steps are completed in sequence and that StepStart and StepStop are completed and are valid Dates.

If nothing else, it may give some idea of 1 approach to solution.

My table design and data are in xactions0.jpg

[AdventureBob]

Thanks for the response.
Let me play with what you posted for a few.

general rules: For a step to be done the front end controls that there MUST be a start and a stop. If theres no stop thats the step its at. Or at least thats the logic Im using right now.

I think the problem will come due to the recursion.
Steps can go; 1,2,3,2,3,4,5,4,3,4,5,6,7 or in any amount of recursion.
Its rare that we have contract make it through in a single pass.

Thanks Ill let you know if I get there. I think Im standing to close to the trees to see forest.

[orange]

I just tried adding a second step 2 to contract 1 and it worked.

in the attached jpg you can see the new record at the bottom of lower datasheet, and the revised Total in top datasheet. I used 3 as my TopStep.