Detail vs Summary (1 Viewer)

[mrabrams2]

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......."

I have a report "Detail Report of..." based on the below query which works wonderfully - the report is exactly what I need.
What I have tried to do is, based on the same query, to create a "Summary Report".

Where the Detail report looks like:
DEF Company Status1 12
FGH Company Status1 15
IJK Company Status1 34

DEF Company Status2 25
FGH Company Status2 51
IJK Company Status2 64

I want the Summary Report to be solely based on StatusType:
Status1 61
Status2 139


I have tried different variations of the Grouping in the Summary report, but just can't get it to work.

I'm even wondering if I will wind up needing 2 separate queries (which is actually the easy way out.!)

Here is the SQL that works fine for the detail report. (If I take out the tblrequester.REQUESTER, it works fine for the summary but not for the detail!)

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];

Is there a way to have both reports based on the same query?

Thank you for reading this all the way through !

Michael

 

[pdx_man]

In the Grouping and Sorting for the report, group by the field GROUPNAME and under its properties, select Yes for Group Footer. In this new section, put in a text box with the control source of:

=Sum([InRange])

(Assuming that the company is your field GROUP and the status is GROUPNAME)

 

[mrabrams2]

Whoops, I changed the names to protect the innocent.


tbl_TypeOfRequest.REQUESTTYPE is actually STATUS1, STATUS2...

tbl_Requester.REQUESTER is the department (DEF Company,etc)

tbl_GROUP.GROUPNAME is the Company Name (used solely for the Report header)

So based on the results below,

#1- The Report Header would have whatever is in =[Forms]![frmABC]![cboGROUP]

#2 - tbl_Requester.REQUESTER would generate:
DEFCompany
FGHCompany
IJKCompany

#3 - tbl_TypeOfRequest.REQUESTTYPE would generate
STATUS1
STATUS2

and
#4 - InRange would generate the counts

If I take out the tbl_Requester.REQUESTER from the SQL, the Summary report works fine. I would rather figure out the proper grouping in the report and base it on the same query.

What do you think - is this possible?

Thanks a lot for helping!
Michael

 

[mrabrams2]

Well, I think I finally have it. I need to try some more test data, but so far it looks right.

I added a Group Header AND a Group Footer for REQUESTTYPE.

I placed a text box for RequestType and a textbox for InRange in the RequestType Header as INVISIBLE.

Then, I placed a text box for RequestType and a textbox for InRange in the RequestType footer and used =Sum([InRange]) in the InRange footer textbox.

Results are as I needed.

Thanks to all who have assisted.

Michael

 

[pdx_man]

In the Grouping and Sorting for the report, group by the field REQUESTTYPE and under its properties, select Yes for Group Footer. In this new section, put in a text box with the control source of:

=Sum([InRange])

 

[mrabrams2]

I guess brilliant minds think alike (and 4 minutes apart!!)

I am sincerely happy that it is working, but I would love to know why different combinations DO work? Is it just a lucky coincidence?

This is what I mean.

#1 -I tried adding a group header AND footer for RequestType.
In the header I had the InRange in a textbox (invisible)
In the footer I had the InRange textbox visible, and its controlsource as =Sum([InRange])

#2- I set the header to No Header (as pdx_man suggested) and just has a Group footer with InRange textbox controlsource as =Sum([InRange])

#3 - I did the same as #2, but used as controlsource InRange (from the query)

All of the above produced the same result.

Why? Is any one "more correct" ??

Thanks for helping !!

Michael