Rx_
Nothing In Moderation
- Local time
- Today, 14:58
- Joined
- Oct 22, 2009
- Messages
- 2,803
One table - need to filter by and reutrn a min ordinal value (Requested, Moved, Final) for each ID_Wells where SHLBHL = 'SHL'
This part of the totals query (The first 3 fields) works perfectlly and returns the right count.
The problem is obtaining the 4th column that is an autocounter.
The Requested, Moved, Final are rarely entered in an actual order.
So, First/Last Max/Min won't work.
I need the unique row's ID_SHLBHL that was filterd to ID_Wells, REQ_FIN, SHLBHL
The 4th column ID_SHLBHL will be used to relink to the unique row for the rest of the data.
Rule: There may only be zero or one FINAL (enforced by form). There may be zero or many MOVED, there may be zero or many Requested
If - Final - pick that. If no Final an 1 or more Moved - pick that. If no Final and no Moved and 1 or more Requested - pick that.
I can write code to create this, but it would take a while to run on a few hundred thousand records.
This part of the totals query (The first 3 fields) works perfectlly and returns the right count.
The problem is obtaining the 4th column that is an autocounter.
The Requested, Moved, Final are rarely entered in an actual order.
So, First/Last Max/Min won't work.
I need the unique row's ID_SHLBHL that was filterd to ID_Wells, REQ_FIN, SHLBHL
The 4th column ID_SHLBHL will be used to relink to the unique row for the rest of the data.
Rule: There may only be zero or one FINAL (enforced by form). There may be zero or many MOVED, there may be zero or many Requested
If - Final - pick that. If no Final an 1 or more Moved - pick that. If no Final and no Moved and 1 or more Requested - pick that.
I can write code to create this, but it would take a while to run on a few hundred thousand records.