Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Limiting returns (Access03)

    I am trying to build a report and limit the returns. A very basic concept which I have done before, but It isn't working today.

    I have a table that has the data for the sections and subsections and other information. With a query I limit to department and job title which is then reported on the report. Then I have a subform that reports out the tasks, subtask, and subsubtasks.

    My problem is that when I run the report I get mutiples from the task list on down.

    For example if there is a task, subtask, subsubtask, subsubtask, and subtask. The whole grouping is repeated five times.

    I have placed the fields in the group header and in details. No matter how I approach it I get repeats. I have tried Unique values and records. It is at the report level once the subreports are added. Any wisdom would be appreciated.

    Fay

  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: Limiting returns (Access03)

    Hi again Fay

    Well it appears you have the subtask, subtask problem you had , working.

    1)Are you running this report using a parameter query?
    2) Have you included Group footers in your report, they sometimes don't come through when creating reports with the wizard?
    Jerry

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

    Re: Limiting returns (Access03)

    Can you tell us more about the record source for the main report and for the subreport? Or attach a stripped down version of the database?

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting returns (Access03)

    Here is the stripped down version. I put it back to "normal" with the lower three levels in the group headers. Thank you for your thoughts and help. Fay

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

    Re: Limiting returns (Access03)

    The query that acts as record source for the main report contains several fields that are not needed, and that cause repetition within a subsection + task. In the attached version, I removed those fields, and set Unique Values to Yes. Is the report OK now?

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting returns (Access03)

    Bless you! Thank you. It works like a charm and as I need it to. Fay

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting returns (Access03)

    As I said the solution worked. But I found another problem. If I have a task with several subtask if I only pick two of the four subtasks for the task for a specific unit the report still gives me all four subtasks. I have been playing around with this and have not been successful. Any of your assistance will be appreciated. Thank you. Fay

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

    Re: Limiting returns (Access03)

    In the database you attached, qryCLAttachment (the record source of the main report) returns one record with SubSectionID = 1 and TaskID = 49. The query qryTrimTasksToSubSubTasks (the record source of the subreport) returns four records, all with SubSectionID = 1 and TaskID = 49, so they are all displayed in the subreport. Why would only two subtasks be selected? I don't see anything in the queries or report that would do that.

  9. #9
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting returns (Access03)

    qryCLAttachments is based on the tblAttachment table. The tblAttachment which stores the information that the unit has made. The unit only selected two of the possible four subtasks, 21 Features and 22 Phone etiquette. But the report returns 19 cell phones and 20 directories which it shouldn't. I see part of the problem now. The subform does not go back to the ftbAttachment to grab what is saved there. So I need to work from that point now. I was thinking that this level would be constrained by the qryCLAttachments. Obviously I am wrong.

    Is it safe to assume that I will need to have a separate query and report for the task, subtask, and subtask levels? If so should the subtask report be put in the group header or details section of the task form?

    Thank you for your patience I have dyslexia and sometimes I can't proof worth a darn. Fay

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

    Re: Limiting returns (Access03)

    Perhaps you need to add tblAtttachments to the query for the subreport too. See attached version. Test thoroughly with "real" data, for I'm not sure I have understood the setup corrrectly.

  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting returns (Access03)

    Getting closer. Trying to figure out what isn't working. I have two different reports experience and checklist. Based on exactly the same queires with different names. Now so items that should show up on both reports only show on one. I will get back to you. Thanks for the help. Fay

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting returns (Access03)

    I can't figure out the mix returns. When the main page comes up set Registered Nurse in the Job Title and Labor and Delivery in the Department. You need to only type the first letter. Then click the Checklist Listing button. (There are only entries for ICU and the Labor and Delivery departments.)

    There are only 8 returns at the Task level, telephone and paging. If you open the qryCLAttachment query you will see that there are 22 entries at the task level. If you go to the tblAttachment table there are 28 records for the L & D department.

    I think the main problem is at the of the subreport but I can't find the issue. Thanks for your help.

    Fay

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

    Re: Limiting returns (Access03)

    Does this do what you want? I changed the design of qryTrimTasksToSubSubTasks - all links are now from tblAttachment to the other tables.

  14. #14
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Limiting returns (Access03)

    Sorry I didn't get back to you sooner, I actually was working.

    It appears that you have nailed it. I need to play with it more, but what I see is what I wanted. Thanks you Hans. This project seemed so simple but it and you have taken me into some interesting places.

    Fay

Posting Permissions

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