Testing several records and having to remember a setting from the previous record is a prime candidate for a recordset loop in vba.
dim PrevDate as date, prevsupplierofservice as string, rs as recordset
set rs = currentdb().OpenRecordset("Select * from Transactions order by supplierofservice, servicedate")
while not rs.eof
if prevsupplierofservice <> rs!supplierofservice then
prevsupplierofservice = rs!supplierofservice
if rs!servicedate = dateadd("d",1,prevdate) then
debug.print rs!supplierofservice & " " & rs!servicedate
prevdate = rs!servicedate
I have a table called transactions, which contains a membership no, servicedate, product, supplierofservice.
I'm trying to identify where the Membership No has consecutive servicedates from a supplierofservice.
You can't do this with a single query. You can do it by scanning the result of a single query with Visual Basic code.
First, you query the table ORDER BY member#, supplier, servicedate. This puts all the records so that the record for each member, and for each supplier and member, and for each supplier and member and date, will be adjacent.
So now you scan the table from beginning to end, remembering what the previous member#, supplier, and service-date were. The first record is a special case: that's where you pick up initial values but do nothing else. For the second and subsequent records you compare the values, process them if they've changed from the previous record in the way that you want, and then (in any case) remember them in preparation for the next record.
Observe that this algorithm needs only a "one-record memory." Because of the sorting, it need only compare a record "to the previous one."
The specific case you're looking for is: "the member# and supplier are the same as before, and the date is one day greater ("DateAdd...") than the one before."
When all those magnetic tapes were whirling about in all those science-fiction movies, "that's what they were doing." They were either sorting data to put onto a tape, or they were processing sorted tapes.
yes, you can do it in one query, just join the table to itself
it won't be very fast, but it will be one query
furthermore, you won't have to write any application code! (a consummation, as the saying goes, devoutly to be wished)
yes, i remember mag tapes, i started on an IBM 360 model 40
from transactions A
join transactions B
this query returns the 2nd (and 3rd and 4th, etc., if there are runs) transacation for the same supplier on consecutive days