Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    42

    Unanswered: Report - Sorting Records

    Hi! I have a sub report which lists detailed records for for house repair items. In the heading for each unique section (Hall, Bath,etc) I am trying to display the square foot calculation. The thing is the square foot number can be in any of the multiple detail records and I am having trouble forcing it to display in my text boxes which are located in the header section. Some will show and some won't show. I have no idea why this happens.

    For example.

    Claim No. Type Desc Len Wid Hght SqFt
    101 5 Hall 5 5 5 125
    101 5 Hall
    101 8 Bath
    101 8 Bath 3 3 3 27


    When the report runs only some of the SqFt calcs will show up. I have tried different types of sorts but nothing seems to work. The record source is set in the "On Open Event".

    Any help would be greatly appreciated!

    Thanks in advance.

  2. #2
    Join Date
    Aug 2004
    Posts
    40
    Is the sqrt function being performed in the query or at the report level?

    I've found a couple of time that I had to do calcs in the query not in the report.

    Howard

  3. #3
    Join Date
    Aug 2003
    Posts
    42
    Its being performed in the query. The example above are what would be my query results..

  4. #4
    Join Date
    Aug 2004
    Posts
    40
    Just re-reading through you problem and wondering where the blank rooms are coming from?

    In order for this to work you'll either need use a totals query the sorted so you can get the first of that room etc

    Group by claim
    Group by type
    Group by desc
    Max of Len
    Max of Wid
    Max of Height

    Then create another query with this as it's record source and perform the sqrt function from there.

    OR

    You need to normallize your data in the query so that all of the records calc to a meaning full value.

    let me know if this doesn't help and I'll think some more.

    Howard

  5. #5
    Join Date
    Aug 2003
    Posts
    42
    Thanks for the suggestion. I will try that tonight when I get home and let you know.

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    More than likely the easiest way to accomplish this is to have the Square Footage in all of the detail records. Then on the report only reference the Square Footage in the header of the report. So your data would look like this:

    Claim No. Type Desc Len Wid Hght SqFt
    101 5 Hall 5 5 5 125
    101 5 Hall 5 5 5 125
    101 8 Bath 3 3 3 27
    101 8 Bath 3 3 3 27

    And your report would look like:

    Hall Sq. Ft. 125
    ...Hall Detail 1
    ...Hall Detail 2

    Bath Sq. Ft. 27
    ...Bath Detail 1

  7. #7
    Join Date
    Aug 2003
    Posts
    42
    DKnuckle....any ideas on how to accomplish this? Update query or code? I was thinking I might have to do both to update each unique type with the same dimensions? Thoughts on this?

    Thanks!

Posting Permissions

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