Query/report with several records on one line.

Is that record number a column in your table?

 If it is, will those numbers always be consecutive, starting with 1 (1, 2, 3, 4, 5, 6, etc.), or could the numbers start with something besides 1, or skip some values?

 Assuming the simplest case, where the record number is a column in the table, and the first 5 records are always numbered 1 - 5, you could use a record selection formula like the following to only include the first 5 rows:

{record number field} <= 5

 For the report output, you could use a cross-tab, with Inspection Number for the rows, record number for the columns, and Inspection Value for the summarized field.

 If the record number is a column in the table, but the first 5 values are not always 1 - 5, then a cross-tab may still work.  The problem becomes limiting the data to the first 5 records.

 James

Will you have a sinle inspection number or multiple.

If the cross tab idea doesn't work I can think of ways to use variables and formulas with groups to produce the output you want.

mlmcc

All,

Thanks for your time. I'm sorry I am slow to respond but I have been away from this project until today.

A cross tab seems like a way to go but I am having difficulty with it. The cross tab examples I have found all deal with summarizing the data. In this case I simply want the first 5 values (still trying to figure out the best method to get the first 5) for a given measurement. In the example I am working with there are 32 attributes (measurements) I need to report for 5 samples (parts). So, my cross tab would be 32 rows of 5 columns of data (plus a other informational columns which apply to all rows). I know that there are 160 rows of data (32*5) in my data source.

I don't know if this will help you. I am stumped.

The cross tab examples I have found all deal with summarizing the data.

If you only have 1 record for each sample (part) and attribute, then the summaries in the cross-tab will just be the values from each record.

 The main problem would seem to be limiting the data to the first 5 records in each set.  If there is no good way to do that, then a cross-tab may not work.

 Are those records numbered?  If so, what do those numbers look like?  I'm guessing that it's not simply 1, 2, 3, etc., because then you probably wouldn't be having any problem figuring out how to get the first 5 records.

 James

James,

You are correct about not finding a simple 1,2,3... There is what appears to be a phony date stamp that is getting used as the sort.

I forgot to add that these values are not numeric but text string and they can't be converted because some values will be numeric and other will be text, like pass/fail.

Thanks.

Brian

I forgot to add that these values are not numeric but text string and they can't be converted because some values will be numeric and other will be text, like pass/fail.

You mean the values that you're "summarizing"?  That shouldn't be a problem.  There is an option in the Cross-tab Expert to tell it not to summarize the value.  If that option is not available (I seem to recall that it's not in some cases), you can set the "summary" to be Minimum or Maximum.  That will work on string fields.

 As for limiting the data to the first 5 values, if you can't use some simple test, like Record_number <= 5, then the best solution would probably be to create your own db query (where you would have more control/options), assuming that you're not already using a query like that.  You could create a query in the db (eg. a view or stored procedure), or use a CR Command (where you write the query, but you create it in CR and it's stored in the report).  Then you could hopefully limit the results in the query.

 If you're going to try creating a query, you could get a start on it by going to Database > "Show SQL Query" and copying the query that CR created for the report.  If that query has the data that you need for the report, you would theoretically just need to modify it to limit the results to the first 5 in each set.

 If you can't create a query that limits the data, you may have to use suppression in the report (to suppress everything after the first 5 values), in which case a cross-tab probably won't work and you'll have to create the columns manually.  That should be possible, but a cross-tab would be so much simpler.

 James