Hi,
In a table, not a recordset, but in a table, the records are not in a fixed
position. Else, inserting a record would eventually move a million of
records in a table of a million of records. So, the only "order" we can
really relay on is an order defined through the VALUES of the fields.
The first query consider for each record ( AS a ) how many records in table
aliased AS b, how many records, COUNT(*), in b, have a date before or equal
to the one in table aliased as a. Sure, since a and b are, the same table,
that COUNT(*) is then the rank, as in first, second, third, .... All that,
based on the value of the fields (the field date, in fact).
The second query keep only the ranks (as previously computed) where bal <0.
Next, among all those so kept, again, keep only those that correspond to a
date less or equal to the one supplied by the alias "a", and keep the max
value of these records from b.
Example:
a.date a.bal a.rank b.rank
xxx + 1 ---
xxx + 2 ---
xxx - 3 3
xxx + 4 ---
xxx + 5 ---
xxx - 6 6
xxx + 7 ---
where --- indicates the record in b is not kept because b.bal>0
Now consider the record with rank =4. The max(b.rank) with a rank <=4 is 3
(ie, the max(b.rank) from all records that precede it. SO, if we do the
same for all records, we got:
a.rank b.rank Nz(Max(b.rank), 0)
1 --- 0
2 --- 0
3 3 3
4 --- 3
5 --- 3
6 6 6
7 --- 6
where the MAX occur only on the records having b.rank less or equal to
a.rank.
Subtract column 3 from column 1 to get what you want.
Hoping it may help,
Vanderghast, Access MVP
bettyboopbh said:
YES! it works. thanks for not giving up on me.
one last thing if you wouldn't mind.
could you explain the logic.
in excel i would use i.e. in cell b2 i would have the formula
"if(a2=0,0,a2+b1).
simple and logical (to me anyway).
but i don't have a clue how your sql got the results i needed.
could you explain the what/why of q1 and q2.
thanks again
Michel Walsh said:
hi,
The first error is related to the fact I missed the GROUP BY clause.
SELECT a.date, a.bal, a.rank - Nz( Max(b.rank), 0)
FROM q1 as a LEFT JOIN (SELECT rank FROM q1 WHERE bal<1) As b
ON a.rank >= b.rank
GROUP BY a.date, a.bal, a.rank
I also add the optional argument of Nz.
The second error seems related to the fact that Access does not find the
query q1. Have you saved the first query under the name q1? if not,
replace
q1, in this second query, by the exact name you used for the first query
you already saved.
Hoping it may help,
Vanderghast, Access MVP
Hi Michel
thanks again for trying to help.
when i copy and past your SQL into q2 and run by selecting datasheet
view(before saving) i get an error msg,"you tried to execute a query
that
does not include the specified expression 'date' as part of an
aggregate
function."
if i save q2 and then run by selecting datasheet view i get a different
error msg, "the microsoft jet database engine cannot find the input
table
or
query 'SELECT rank from q1 WHERE bal <1'."
also, if i then close q2 and then reopen the "(" in front and back of
"SELECT rank.....WHERE bal <1) change to "[". from this point if i
run
q2 i
get the same error msg, "the microsoft jet database engine cannot find
........SELECT rank from q1 WHERE bal <1'."
i copyied the sql from my q2 query for you to look over. (although the
q2
SQL came from copying and pasting what you posted for me.)
did you/could you actually try your sql for q2 to see if it works for
you??
again, thank you so very much for your help.