Results 1 to 7 of 7

Thread: Report Grouping

  1. #1
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    120

    Unanswered: Report Grouping

    I have been working on this for some time now, and I am hoping someone can look and it and say:
    "All you have to do is this......."

    The attached shows the SQL to the report. I am trying to get the report to summarize by StatusType. Unfortunately, when the report is run, it shows (eg):
    Status 1 5
    Status 1 45
    Status 1 17
    Status 2 34
    Status 2 31
    etc

    It should read:
    Status 1 67
    Status 2 65

    The total at the bottom of the page is correct.

    What I think is happening, is that the query includes a field called REQUESTER, which is not included in the report. I think the report is showing the statuses for each requester, when I need a total for each status. (This query is used for another report, which is why I need REQUESTER)

    I have tried different variations of where to put the text box (InRange).
    It just doesn't work.

    Would anyone please be kind enough to take a look?

    Thank you very much,
    Michael
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "all you have to do is..."

    ... copy the text of the sql query out of sql view and paste it in here as text

    that stupid word doc used up all my machine's memory (something about a font) and i had to invoke Task manager to kill it


    rudy

  3. #3
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    120

    SORRY 'BOUT THAT !!

    rudy: So sorry !!

    Here's the SQL:

    QUERY IN SQL VIEW:
    PARAMETERS [Forms]![frmABC]![cboGROUP] Text, [Forms]![frmABC]![txtBeginDate] DateTime, [Forms]![frmABC]![txtEndDate] DateTime;
    SELECT tbl_ABC.GROUP, tbl_GROUP.GROUPNAME, Sum(IIf([DATE_IN] Between [Forms]![frmABC]![txtBeginDate] And [Forms]![frmABC]![txtEndDate],1,0)) AS InRange, tbl_TypeOfRequest.REQUESTTYPE, tbl_Requester.REQUESTER
    FROM tbl_Requester INNER JOIN (tbl_TypeOfRequest INNER JOIN (tbl_ABC INNER JOIN tbl_GROUP ON tbl_ABC.GROUP = tbl_GROUP.GROUP) ON tbl_TypeOfRequest.REQTYPE = tbl_ABC.[Type of Request]) ON tbl_Requester.REQSTR = tbl_ABC.Requester
    GROUP BY tbl_ABC.GROUP, tbl_GROUP.GROUPNAME, tbl_TypeOfRequest.REQUESTTYPE, tbl_Requester.REQUESTER
    HAVING (((tbl_ABC.GROUP)=[Forms]![frmABC]![cboGROUP]));

    Thanks for checking it out!!

    Michael

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's your query, formatted --
    Code:
    SELECT tbl_ABC.GROUP
         , tbl_GROUP.GROUPNAME
         , Sum(IIf([DATE_IN] 
            Between [Forms]![frmABC]![txtBeginDate] 
                And [Forms]![frmABC]![txtEndDate]
              ,1 ,0)) AS InRange
         , tbl_TypeOfRequest.REQUESTTYPE
         , tbl_Requester.REQUESTER
    ...
    GROUP 
        BY tbl_ABC.GROUP
         , tbl_GROUP.GROUPNAME
         , tbl_TypeOfRequest.REQUESTTYPE
         , tbl_Requester.REQUESTER
    HAVING tbl_ABC.GROUP=[Forms]![frmABC]![cboGROUP];
    remove , tbl_Requester.REQUESTER from both the SELECT list and the GROUP BY and see if that fixes things

    also, your HAVING could probably be a WHERE

    rudy

  5. #5
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    120
    Thanks again rudy - forgot that I can use HTML codes.

    Maybe I cannot do what I would like to do.

    I am trying to run a "Summary" report and a "Detail" report using the one query (as posted). Therefore, the tbl.requester would need to remain as part of the query.

    I was hoping to utilize the "grouping" feature of the Summary report.
    (The "Detail" report works fine.)

    What it is starting to sound like, is that I will need a "Summary" query AND a "Detail" query.

    Oh well, back to the drawing board.

    Thank you for your help, rudy.

    Michael

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, you can do it with one query, i'm fairly certain, as long as that query is the detail query

    but i haven't done access reports in a lo-o-o-o-o-ong time so perhaps someone else will step in


    rudy

  7. #7
    Join Date
    Mar 2002
    Location
    Sacramento, CA
    Posts
    120
    I hear ya !!

    Yeah, the query posted is the Detail query. I have tried many different combinations using the report's grouping, headers etc.

    Again, thanks for looking and trying !!
    (and sorry again 'bout Mr. Task Manager !)

    Michael

Posting Permissions

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