Create report with 2 queries

[sumana]

HI,

I have a singe table and 2 queries based on the same table. One query
is a Select query with some criteria. The second query is one which
uses the aggregate function Count() in the select statement. I need to
display the results of both of these queries in a report.

Access doesn't allow me to derive a report based on both these queries.
Can anyone help me how I can go about doing this?

Thanks
Sumana

 

[Duane Hookom]

Use a subreport or two. Each report or subreport can have its own record
source.

 

[sumana]

Thanks for the info.

I did try the answer you mentioned and I could display the results but
I am not able to hide the subreport in the main report since I do not
see any "visible" property for the subreport in the property sheet.
Could you also help me do this?

Sumana

 

[Duane Hookom]

Why would you want to hide a subreport? There was no mention made of hiding
anything for any reason in your first question.

Can you start from the beginning with what you have and what you need.

 

[sumana]

Sorry, I think I confused you. I will explain in detail my requirement.

I have a table called DCI. It has many fields, few of them are:

DCINo, DCIIssueDate, DCICommitDate, DCIDeliveredDate, ActualEffort,
ReworkEffort......

Now, I have to create a report for which the user will input the DCI
Range (i.e. max and min DCI nos.)
I have to list the DCI Nos and Rework effort of all DCI within the
Input range which have ReworkEffort > Value(this value is again an
input from the user)

Ex I created the query like this: Select DCINo, ReworkEffort from DCI
where DCINo between 200 and 5000 and ReworkEffort > 0

This was a simple task and I could do it.

The second part of it is to display a % calculation as

(Total Rework Effort obtained from the above query / Total Actual
Effort for selection range) * 100

I can obtain the numerator by summing up "ReworkEffort" field in the
report. But I will not be able to calculate the denominator from the
above query.

So I split the above query into 2 queries.

Query1: Same as above query

Query2: SELECT Sum(DCI.ActEffort) AS SumOfActEffort
FROM DCI
HAVING (((DCI.DCINo) Between
[Forms]![frmReportInput]![txtDCIStart] And
[Forms]![frmReportInput]![txtDCIEnd]));

(Note: I created a form to input the parameters for the query)

The 2nd query will give me the denominator

Access 2003 doesn't allow me to create a report from both these
queries.
So, I created the main report from query1 and a subreport from query2.
I can do the % calculation from these two reports and show in the main
report. But I do not want to show the subreport to the user since it
has only one field resulting from query 2.
This is why i wanted to hide the subreport.

Can this be done? or is there a way to optimise my query.?

Thanks for advising me on this issue

Sumana

 

[Duane Hookom]

Since one of your queries returns only a single row, you can include your
query2 in your first query. Just add [SumOfActEffort] to your query grid to
make it available in your report.

 

[sumana]

I incorporated the above change in the query and I was able to get the
Sum of ActualEffort field but this value repeats in every row of the
result instead being displayed as one value just in the first row. Is
this possible?

Below is the modified query to obtain the result

SELECT DCI.DCINo, DCI.RewEffort, (SELECT Sum(DCI.ActEffort) FROM DCI
WHERE ((DCI.DCINo) Between
"200" And "5000") AS SumOfActEffort
FROM DCI
WHERE (((DCI.DCINo) Between "200" And "5000") AND ((DCI.RewEffort)>0));


Thanks
Sumana

 

[Douglas J. Steele]

No, it's not possible.

See whether this works for you:

SELECT DCI.DCINo, DCI.RewEffort, Null AS SumOfActEffort
FROM DCI
WHERE (((DCI.DCINo) Between "200" And "5000") AND ((DCI.RewEffort)>0))
UNION
SELECT Null, Null, Sum(DCI.ActEffort)
FROM DCI
WHERE ((DCI.DCINo) Between "200" And "5000")

 

[sumana]

Sorry the solution which you gave did not work.

May be I will use the query which I previously posted and will try to
hide the column in the report...it may work...

Thanks for all your help

Sumana

 

[sumana]

I put my query in the report. And when I calculate the Total of
RewEffort field of the report using the below expression
=Sum ( [RewEffort] )

in a text box field placed in the report footer, it gives an error
saying

" Multi-Level GROUP BY clause is not allowed in a sub-query "

I do not understand what the problem is. Could u pl help?

 

[Duane Hookom]

I don't quite understand what you have and what you need. Does one of your
queries really only return the single value? Where/how do you want to use
the single value?

 

[sumana]

Sorry for replying late, was not able to work all these days.

My Query is as follows:

SELECT DCI.DCINo, DCI.RewEffort, (SELECT Sum(DCI.ActEffort) FROM DCI
WHERE ((DCI.DCINo) Between [Forms]![frmReportInput]![txtDCIStart] And
[Forms]![frmReportInput]![txtDCIEnd]) AS SumOfActEffort
FROM DCI
WHERE (((DCI.DCINo) Between [Forms]![frmReportInput]![txtDCIStart] And
[Forms]![frmReportInput]![txtDCIEnd]) AND
((DCI.RewEffort)>[Forms]![frmReportInput]![txtRwkVal]));


I make a report of this query. Here SumOfActEffort is a single value. I
want to use this to calculate a %age value in the report as

Sum(RewEffort)
---------------------- * 100
SumOfActEffort

I am not able to calculate the numerator value in the report (i.e
adding one of the field values of the report) and it gives an error
which i mentioned earlier.
But I was able to do the same when the query did not include the
SumOfActEffort part.

Hope this is clear

Sumana

 

[Duane Hookom]

How about these queries:

== qtotActEffort =======
SELECT Sum(ActEffort) AS SumOfActEffort
FROM DCI
WHERE DCINo Between "200" And "5000";


==Query2=======
SELECT DCINo, RewEffort, SumOfActEffort
FROM DCI, qtotActEffort
WHERE DCINo Between "200" And "5000" AND RewEffort>0;

BTW: according to your sql, "1000" is not between "200" and "5000" although
"300000" is.

 

[sumana]

Finally the queries you provided worked. Thanks!!

But I didn't quite get what you wanted to convey in the 2nd part of ur
msg i.e "BTW: according to your sql, "1000" is not between "200" and
"5000" although
"300000" is. "


Sumana

 

[Duane Hookom]

The solution matches what I thought I suggested in my 3rd reply in this
thread. I probably wasn't clear enough.

Is DCINo text or numeric? If it is text then "3" is between "20" and "400".
Text is treated like text and not numbers.

 

[sumana]

DCINo is text since it has to be alphanumeric. So won't this solution
work? What should be the possible alternative?

Sumana

 

[Duane Hookom]

Your current syntax will work as long as you realize
"1000" is not between "200" and "5000" although "300000" is
and "3" is between "20" and "400"

How do we know if the solution will work? You have never stated if this is
acceptable to meet your needs. If you think "1000" should be between "200"
and "5000" then you will need to convert the text to values.

 

[sumana]

Oh! I overlooked this problem. I need "1000" in between "200" and
"5000".

Since I am using a report input form, my current queries will be

== qtotActEffort =======
SELECT Sum(DCI.ActEffort) AS SumOfActEffort
FROM DCI
WHERE (((DCI.DCINo) Between Forms!frmReportInput!txtDCIStart And
Forms!frmReportInput!txtDCIEnd));

== Query2 =======
SELECT DCI.DCINo, DCI.RewEffort, qtotActEffort.SumOfActEffort
FROM DCI, qtotActEffort
WHERE (((DCI.DCINo) Between Forms!frmReportInput!txtDCIStart And
Forms!frmReportInput!txtDCIEnd) And
((DCI.RewEffort)>Forms!frmReportInput!txtRwkVal));



So, how do we convert text to values. If I just use the .Value property
against all the text boxes, like txtDCIStart.Value, in all places where
I have used text boxes, would it suffice?

 

[Duane Hookom]

You might be able to get by with:
== qtotActEffort =======
SELECT Sum(DCI.ActEffort) AS SumOfActEffort
FROM DCI
WHERE ((Val(DCI.DCINo) Between Forms!frmReportInput!txtDCIStart And
Forms!frmReportInput!txtDCIEnd));

 

[sumana]

It doesn't work!!