Please help Query IIFs

[nparrillo]

Folks,
I hope this makes sense.

I have these two tables, items within each table are linked by a registration number. Within table 1, I have a top stage value and a base stage value all of which is accompanied yes by a corresponding stage number.

The second table, contains a top point value and a base point value. I want to see if it possible to run a query to see if the top point and base point values fall within the span of the the top stage and base stage values, and if so, yield the respective corresponding stage number that that accompanies the stage values.

Let me know what you all think..

The end goal by generating the stage number value that is in the first table within the second table would be that I could then be able to build a relationship between the two tables using the stage numbers.

[kennejd]

You will create a query....join the tables by registration number. Use a between comparison in the criteria to find your ranges. Sorry my answer is a little vague...I'm having trouble following your question...

[nparrillo]

yes, the tables are joined by registration number. maybe the formula I have attemped to write will help you follow.

Stage Number Match:IIF([Points]![Depth Top]<=[Stages)]![Depth Top] And IFF([Points)]![Depth Base]>=[Stages]![Depth Base],[Stages)]![StageNumber]))

[nparrillo]

See file this might clear some things up

[kennejd]

Try this:
Stage Number Match:IIF([Points]![Depth Top]<=[Stages)]![Depth Top] And [Points)]![Depth Base]>=[Stages]![Depth Base],[Stages)]![StageNumber])

[nparrillo]

that didnt seem to work either. see the file i attached it might help you under stand what I am talking about. I really apprieciate your help.

[pbaldy]

Both TopPoint and BasePoint need to be included in the same range? See if this does what you want (if they aren't both between, it should return 0):

SELECT Points.RegNumber, Points.TopPoint, Points.BasePoint, Nz(STAGES.StageNumber,0) AS StageNum
FROM Points LEFT JOIN STAGES ON (Points.TopPoint >= STAGES.TopStage AND Points.TopPoint <= STAGES.BaseStage) AND (Points.BasePoint >= STAGES.TopStage AND Points.BasePoint <= STAGES.BaseStage) AND (Points.RegNumber = STAGES.RegNumber)

[kennejd]

The formula should look like:

StageMatch: IIf([TopPoint] Between [TopStage] And [BaseStage] AND [BasePoint] Between [TopStage] And [BaseStage],[StageNumber])