Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Consecutive Reads

Consecutive Reads

Consecutive Reads

Hi All,


I have a report that is required to capture 4 consecutive read types. For example;

There are three read types, there could be more but we only care about estimate reads in this case.
Actual Read

Each reading date will have one read type assigned. I need to find all accounts with 4 consecutive estimate reads. Only 4 consecutive estimate reads, no others are required.

Assuming I need a redim preserve and a loop.


RE: Consecutive Reads

Is it possible to have repeating values or out of sequence types ? For example is it possible to have :
Actual Read
Actual Read

www.R-Tag.com Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

RE: Consecutive Reads


So do you have more than Read Type and Reading Date in this table?

Exactly what do you mean by 4 consecutive 'Estimate' Read Type: simply ordered by Reading Date?

Do you have SQL code?


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Consecutive Reads

correct Peter,

I would only need to see


for the last four read dates.

we could disregard as soon as one of the last four consecutive reads was not an estimate.

Skip: the balance of the fields are negligible as I can pull every column as long as I have the last four consecutive reads being estimates.

I would assume doing a loop + redim preserve on the read type where readtype = 'estimate' and readtype[1] = 'estimate' and exit on the 4th redim would do it. I could then filter on the loop = [estimate, estimate, estimate, estimate]

I'm just not experienced enough with redim preserve and loops.


RE: Consecutive Reads

Assuming you are only interested in the last four readings for each AccountNo.
And only interested in AccountNos where the last four readings are Estimates.
One approach would be to limit the records to the last four readings for each AccountNo.
I would do that using a Command.
The command would return the last 4 readings per AccounNo.

So imagine that you have a table called Readings with the following fields:
A record might look like this

The following command would be added to the Database Expert and the report would be based on this command (instead of using the Readings Table):
FROM Readings R
WHERE ReadingId In (
SELECT TOP 4 ReadingId
FROM Readings
WHERE AccountNo = R.AccountNo
NOTE: The syntax may differ based on the flavour of SQL you are using

The result should be that you now have the last 4 records per AccountNo.
Create a Group based on AccountNo
Create a formula called @Estimates:
If {Readings.ReadType} = "Estimate" then 1 else 0
Create a Sum of @Estimates per group.
Create a Group Record Selection formula to filter the Groups:
Sum ({@Estimates}, {Readings.AccountNo}) = 4

You will now only have Customers show in the report where the last 4 Readings are Estimate Readings.

If this works this may be a less complicated approach than using a looping formula.

Gordon BOCP

RE: Consecutive Reads

Thanks Gordon!

I will try that.

RE: Consecutive Reads

Hi Gordon,

The query worked like a charm. I had to rewrite it for SQLLITE which uses LIMIT N after the order by instead of TOP N in the select, but worked the same none the less. It's not my favorite syntax.

I now have a different environment to apply this concept to. It's a Visual FoxPro dbf file. The software environment is using Crystal reports viewer to open the .rpt and the rpt's are using file connections and not ODBC driver connections, this is a limitation to the software. Unfortunately this means I can't use a SQL command (sigh).

Now thinking a while x do y loop with an exit while on the fourth reading id can be used in the report selection where the loop result is <= 4. I can then use the summary and group by concept from your original suggestion.


I appreciate the help,

RE: Consecutive Reads

Hi Gordon,

I figured it out. I sorted the data by read date descending and created a redim preserve on the read type. Then I created a formula with the redim preserve startswith 'ES, ES, ES, ES,' and used it to suppress the undesirables.


RE: Consecutive Reads

Nice work Paul ! that sounds like a very efficient solution


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close