Report Sorts (Access 2002)

I have 6 columns in a table. The first column can contain duplicates and the remaining columns will not contain duplicates for each value of the first column. The way the data is entered in the table each value in the first column will not repeat more times then the maximum number of values of the other 5 columns for every value of the first column. See attachment for example.

The first column value may appear elsewhere in the table as well. When I run a query to bring in data from another table, the values stay grouped the way they appear in the table. If I try to sort on column one, in a query or in a report, It will re-arrange the data out of this format. What i want is a report to sort on Column 1 and then perform as follows: If there is a value for column 2 then those records appear first, if not proceed to column 3. Of the remaining records, if there is a value in column 3 those records appear next. And so on. I'm not concerned with the sort of the records in columns 2-6.

This might seem very confusing because it is a little tough to explain. Any suggestions?

Re: Report Sorts (Access 2002)

Yes I have.
I have a Group Header for Asset ID. I didn't see a need for a footer.

If I sort by inputs it won't necessarily capture it in the correct order. For example. The first input may be X and the second input A. The first input has an output associated with it but the second one doesn't. I need the X to appear first. That could vary for each Asset ID.

Re: Report Sorts (Access 2002)

The way you want to organize the report, goes against the grain of a database, since you want to sort each column individually, so fields from different records will end up together. One way to do this is to populate a temporary table with the data the way you want them in the report, and use this table as record source.
I have attached a modified version of Jezza's database (thanks, Jerry - stealing is easier than doing the work myself <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
It uses DAO code to clear, then refill the temporary table. (There's a reference to the DAO 3.6 Object Library in Tools | References...)
This code is called in the On Open event of the report, so that you look at the most recent situation each time the report is opened.