Relationship based on less than the shown record

[keshalyi]

Hi all! Sorry I've not been posting much, I'm in the middle of ifnishing up a project.Hopefully you guys and gals can help me, and I can get back to beeing an active newbie here.

Here's the problem.

I have a number field in my database that quantifies the sequence that the records are to be output in. This sequence is arbitrary, and set by the user, and so this field is the only place that the sequence can be gleaned from.

I have another number field that measures how many inches tall each record will be when output (its complicated, and not worth going into. Sufficeth to say, I have a field thats a measurement for each record).

What I need is a field that is the sum of the measurements of every ad that comes before the record in sequence. Now, this field has to remain stable, no matter what the database is sorted by. I thought, well, that should be easy - what I need is a relationship that displays every record whose field sequence is less than the current record.

Only, how the heck do I do that?!?!

It seems there must be an easy way, but I can't figure it. I know I need to just set up a test field, but how do I make a test that's value changes for every record? In other words how do I make the test "If sequence < sequence in whatever record we're determining this for, 1, 0"

Thanks, all!

[falkaholic]

What i'd do is to make (if i got the problem right) your standard relation based on a static field and a calculation.

Make 2 new relations. 1 to the master ID to the slave calculation.

Then make one from the slave to the master.

In the slave db have the ID be a calculation something like this:

if( sequence < masterdb::currentsequence, ID, "")

then make a calculation (call it sumMessurement) in the slave that does:

Sum(mesurement)

then in your master database maker another calculation

Sum(slaveDynamicID::sumMessurement)

that should give you the sum of the messurement left in the sequence. (note the 'should')

[keshalyi]

Thanks for the quick answer. I suppose that could work, couldn't it? I hate to build a whole new table just for that, but, if I have to, so be it...

[falkaholic]

Wait, the way i see it you don't need a new table. It should fit into what your doning -- though am assuming that your using the master/lineItem design for it.

Make sure you plan it out real good because I'm not sure I understood what you were doing exactly.