Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUB values mixed in Main (Access2003)

    Having another tricky (for me - that is!)
    Report use to be the one and only.
    Header NAME
    Header Jobs JobStatuses

    When I was asked to display Statuses Open and Hold forst and status Cancelled as last.
    So I had created 2 queries insted of 1.
    First is = Open and Hold
    Second = Cancelled

    I had created SUB Report and inserted another Heather Job.
    I had places SUB into second Header Job.
    Linked by Name.

    When I ran it result appeared as follows:

    Job JobStatus
    123____Hold
    124____Cancelled
    125____Open
    126____Cancelled

    as suppose to
    Job JobStatus
    123____Hold
    125____Open
    126____Cancelled
    124____Cancelled
    __________________________________________________ ___

    I had played with placing the SUB into different headers and no luck!
    I see it very odd that values from SUB Report getting mixed in with Main Report values.
    I thought SUB Report is like a shell that holds it's own data and displaying it AFTER Main Report data because of the fact that is was placed AFTER Main data.

    Please, help.
    Thanks

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

    Re: SUB values mixed in Main (Access2003)

    A subreport doesn't necessarily display its records after those of the main report.
    Instead of using a subreport, use a main report only, based on the original table or query (with all records).
    Open the report in design view.
    Activate the Sorting and Grouping window.
    Enter the following formula in an empty row:
    <code>
    =IIf([JobStatus]="Cancelled",2,1)
    </code>
    Select this row and drag it to the top of the Sorting and Grouping window.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUB values mixed in Main (Access2003)

    It seems awesome solution but it does not displaying 'Cancelled' at all now.

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

    Re: SUB values mixed in Main (Access2003)

    Are you sure that the record source of the report includes the records whose status is "Cancelled"?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUB values mixed in Main (Access2003)

    Record Source is the JobStatus
    JobStatus column is included 'Cancelled' - this is where I had copied it from to paste into your formula.
    I suppose when I dragged it to the top of the sorting grouping window - there is no header nor footer should be selected, right?
    I am must be missing something.
    Can you please, 'tell' me what is the formula says
    If JobStatus='Cancelled' then what is 2 and 1 representing? I might going to be able to 'get' it somehow. Thanks

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

    Re: SUB values mixed in Main (Access2003)

    You can set the Group Header property for the expression =IIf([JobStatus]="Cancelled",2,1) to Yes if you want a header to separate the Open / Hold records from the Cancelled records. If you don't want such a separator, set the Group Header property to No. Similar for Group Footer - it's up to you.

    The expression =IIf([JobStatus]="Cancelled",2,1) returns 1 if Job Status is not "Cancelled", i.e. if it is "Open" or "Hold", and it returns 2 if the Job Status is "Cancelled".
    The report is sorted on this expression, and since 1 comes before 2, the records with "Open" or "Hold" will be displayed before the records with "Cancelled".

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

    Re: SUB values mixed in Main (Access2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUB values mixed in Main (Access2003)

    I am knocking myself out trying to get this right. Not happening!
    Here is look of the Report.
    Name Header where name of the Recruiter whos Job we are going to display. It is #1 in sorting/grouping.
    Than there is a Manager #2.
    Than JobNumber #3 and it has Group Header set to yes and this is where all data is located.

    We see it as :

    Recruiter Name

    Manager JobID JobStatus
    _________________________________________

    Wherever I am placing the formula - few of the 'Open' or 'Holds' are showing after the 'Cancelled'
    For some Recruiters it is how it use to be - scattered all ovet the page. Randomly.

    I assume it is sorting problem. I must had done something with the sorting. Thanks

    Edited for more info.

    If I want to have an output as following:

    Recruiter Name

    Manager JobID JobStatus
    AAA______123____Hold
    AAA______125____Open
    ANA______126____Cancelled
    ANA______124____Cancelled

    How am I suppose to have it sorted?
    Client wants by Manager and JobStatus. Where is the data should be placed if now it is in JobID Header?

    Thanks

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUB values mixed in Main (Access2003)

    Post deleted by MS_fan

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

    Re: SUB values mixed in Main (Access2003)

    There are only 3 records in the table so it's hard to say whether there is a problem or not. Does the report display the records in the order that you want? If not, what should the order be?

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: SUB values mixed in Main (Access2003)

    You don't have a Job Status field anywhere. You do have a Status field, and another T9_REPL_EXTENSION , that sometimes includes the value Cancelled.

    It is this field T9_REPL_EXTENSION that you include in the sorting and grouping expression, and it seems to work OK. Open and Hold appear on page 1, Cancelled on p2.
    Attached Images Attached Images
    Regards
    John



  12. #12
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUB values mixed in Main (Access2003)

    Post deleted by MS_fan

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

    Re: SUB values mixed in Main (Access2003)

    The report in your new sample database shows all records with status Open and Hold on the first page, and those with Cancelled on the second page. Isn't that what you wanted?

  14. #14
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUB values mixed in Main (Access2003)

    Almost but not on a different page. I wanted it on the same page.

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

    Re: SUB values mixed in Main (Access2003)

    You have set the Force New Page property of the first group header section (GroupHeader0) to Before Section. This causes the page break. If you set the Force New Page property to None, the page break will disappear.

Page 1 of 2 12 LastLast

Posting Permissions

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