Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jul 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Sorts (Access 2002)

    Hi there, welcome to the Lounge

    Small question, have you set Group Headers and Footers for Asset ID in your reports and then sort by Inputs?
    Jerry

  3. #3
    New Lounger
    Join Date
    Jul 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Sorts (Access 2002)

    Hi there

    I have just created a database with your data and created a report using your data through the wizard. You will have to Group by User ID and then sort the data by Inputs (descending)

    I have attached my database for you to look at. The report looks very similar to what you want I hope
    Jerry

  5. #5
    New Lounger
    Join Date
    Jul 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Sorts (Access 2002)

    I just realized why I hadn't tried that. Inputs and Outputs are Memo fields. I can't get sort/grouping on that in the report. I need those columns to be larger than 255 characters.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Sorts (Access 2002)

    OK so I am in a "dirty fix" mood tonight.

    Create a query from the table
    Add a new field say Sort...Put Sort:Left([input],1)

    Save query

    Run Report wizard from the new query and group by UserID and the sort descending for Sort. Complete wizard

    Open report in design view and delete Sort label and field and hey presto it works
    Jerry

  7. #7
    New Lounger
    Join Date
    Jul 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Sorts (Access 2002)

    Okay... That looks like it worked for the most part...there are still some cases where in the 4th column where it fails. I think it might need a little bit more tweaking.

    THANKS!!!!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •