difficult? maybe not..... (1 Viewer)

[dkirk02]

Ok, here we go....
Have a tbl that has multiple line items for one acct.
Acct No. 30 day bal 60 day 90 day
457 $2.57 $5.93 $0.00
457 $0.00 $6.23 $10.01
457 $15.17 $5.93 $11.25
457 $0.00 $0.00 $0.00

And here's the question....
I need to pull the record that has the most entries. ie...the third line is the one I would want to display in a query/report/tbl.
Does anyone know of any way to do this? I have been in some pretty deep thought, and I can't come up with anything. Any and all suggestions would be more than welcome.

Thanks in advance,
Deeta

 

[tzimak]

You could make 3 fields in your query, like this:

Val1: iif ([30 Day] > 0, 1, 0)
Val2: iif ([60 Day] > 0, 1, 0)
Val3: iif ([90 Day] > 0, 1, 0)

Then a fourth like this:

Sum: [Val1] + [Val2] + [Val3]


Sort it by [Sum] and set the Top Values propery to 1.

That should do it. I don't know if I had all the syntax exactly right, just wrote it without checking.

Tasmin