Accessing specific rows in a table

[PBS2]

In my Crystal V.10 report I need to access 6 consecutive rows in a table. There is a field in the table called firstrow which when set to 1 identifies the first row I need to access. I need pick up the value of a field called mydata from each of the 6 rows to use in formulas on the report.

I have inserted an SQL expression field into my report called firstdata which contains the following expression:-

SELECT mydata
FROM mytable
WHERE (firstrow = 1)

I can then reference this data by quoting {%firstdata} in the formulas I need to.

My question is how can now access the next 5 rows after the first row? Can I use an an SQL expression? The records after the first row are ordered chronologically with a date field if this helps.

 

[Madawc]

You could use an alias, add the same table again and it will be treated as distinct. Link from the 'first row' to the other records, assuming that they have something in common.

Use a running-total count to limit it to the first five, suppressing the rest.

Madawc Williams (East Anglia, UK)

 

[lbass]

You could use Madawc's suggestion and link on date with a <= join, sorting on the date field and using suppression, or you could use "Add command" as your datasource, with a command something like:

SELECT top 6 table.`mydata`, table.`date`
FROM `table` table
WHERE table.`date` >= (select table.`date` from `table` table where table.`firstrow` = 1)
ORDER BY table.`date`

The syntax varies depending upon your datasource, and the above should work with an Access-based datasource.

-LB

 

[abaldwin]

Non-normalized data.

If you have a field that is being populated with "first record" then it should be easy enough to populate the 5 remaining fields with a record number. I am assuming there are many more records that you do not want.

then select where the field is IN (1,2,3,4,5,6) or use the same logic that is identifying the records in the first plase as the selection criteria for the report. D

Databases do not always present the data in the same order that you "see" them. For instance, if the view you have is based on sorting by last name and another user has since changed the last name from Brown to Lee then the record is going to be "lower" than it previously was.

I would look into the database design before attempting to write a crystal that is not normal.

Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!