Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Queries / April 2006

Tip: Looking for answers? Try searching our database.

Create report with 2 queries

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
sumana - 27 Mar 2006 22:07 GMT
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
Reply to this Message
Duane Hookom - 27 Mar 2006 23:18 GMT
Use a subreport or two. Each report or subreport can have its own record
source.

Signature

Duane Hookom
MS Access MVP
--

> HI,
>
[quoted text clipped - 8 lines]
> Thanks
> Sumana
Reply to this Message
sumana - 28 Mar 2006 00:04 GMT
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
Reply to this Message
Duane Hookom - 28 Mar 2006 04:00 GMT
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.

Signature

Duane Hookom
MS Access MVP
--

> Thanks for the info.
>
[quoted text clipped - 4 lines]
>
> Sumana
Reply to this Message
sumana - 28 Mar 2006 21:49 GMT
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
Reply to this Message
Duane Hookom - 28 Mar 2006 23:24 GMT
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.

Signature

Duane Hookom
MS Access MVP
--

> Sorry, I think I confused you. I will explain in detail my requirement.
>
[quoted text clipped - 50 lines]
>
> Sumana
Reply to this Message
sumana - 29 Mar 2006 23:23 GMT
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
Reply to this Message
Douglas J. Steele - 29 Mar 2006 23:44 GMT
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")

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>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
[quoted text clipped - 11 lines]
> Thanks
> Sumana
Reply to this Message
sumana - 30 Mar 2006 21:37 GMT
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
Reply to this Message
sumana - 30 Mar 2006 22:39 GMT
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?
Reply to this Message
Duane Hookom - 31 Mar 2006 04:45 GMT
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?

Signature

Duane Hookom
MS Access MVP
--

>I put my query in the report. And when I calculate the Total of
> RewEffort field of the report using the below expression
[quoted text clipped - 6 lines]
>
> I do not understand what the problem is. Could u pl help?
Reply to this Message
sumana - 04 Apr 2006 21:49 GMT
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
Reply to this Message
Duane Hookom - 04 Apr 2006 22:55 GMT
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.

Signature

Duane Hookom
MS Access MVP
--

> Sorry for replying late, was not able to work all these days.
>
[quoted text clipped - 24 lines]
>
> Sumana
Reply to this Message
sumana - 04 Apr 2006 23:35 GMT
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
Reply to this Message
Duane Hookom - 05 Apr 2006 01:17 GMT
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.

Signature

Duane Hookom
MS Access MVP
--

> Finally the queries you provided worked. Thanks!!
>
[quoted text clipped - 4 lines]
>
> Sumana
Reply to this Message
sumana - 05 Apr 2006 14:28 GMT
DCINo is text since it has to be alphanumeric. So won't this solution
work? What should be the possible alternative?

Sumana
Reply to this Message
Duane Hookom - 05 Apr 2006 14:54 GMT
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.

Signature

Duane Hookom
MS Access MVP
--

> DCINo is text since it has to be alphanumeric. So won't this solution
> work? What should be the possible alternative?
>
> Sumana
Reply to this Message
sumana - 05 Apr 2006 20:37 GMT
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?
Reply to this Message
Duane Hookom - 06 Apr 2006 03:57 GMT
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));

Signature

Duane Hookom
MS Access MVP
--

> Oh! I overlooked this problem. I need "1000" in between "200" and
> "5000".
[quoted text clipped - 17 lines]
> against all the text boxes, like txtDCIStart.Value, in all places where
> I have used text boxes, would it suffice?
Reply to this Message
sumana - 07 Apr 2006 22:26 GMT
It doesn't work!!
Reply to this Message
Duane Hookom - 08 Apr 2006 03:50 GMT
Oh yes it does! From what I can see there is absolutely no wrong results, no
error messages, and no issues.

Signature

Duane Hookom
MS Access MVP
--

> It doesn't work!!
Reply to this Message
sumana - 10 Apr 2006 20:59 GMT
Hi,
If we use the operator Val(DCI.DCINo) in the query qtotActEffort, the
result is fine, infact there is no change from the previous results
when we didn't have the Val() prefix.

If we use the same operator in the query Query2, it lists the DCI No
30000 as between 200 and 5000. This is not working fine

Sumana

> Oh yes it does! From what I can see there is absolutely no wrong results, no
> error messages, and no issues.
[quoted text clipped - 5 lines]
>
> > It doesn't work!!
Reply to this Message
Duane Hookom - 10 Apr 2006 21:08 GMT
Could you share some SQL views?

Signature

Duane Hookom
MS Access MVP
--

> Hi,
> If we use the operator Val(DCI.DCINo) in the query qtotActEffort, the
[quoted text clipped - 16 lines]
>>
>> > It doesn't work!!
Reply to this Message
sumana - 12 Apr 2006 19:54 GMT
What kind of views would you like to see? Are you indicating any SQLs?
I didn't exactly get what you meant, sorry!

> Could you share some SQL views?
>
[quoted text clipped - 23 lines]
> >>
> >> > It doesn't work!!
Reply to this Message
Duane Hookom - 13 Apr 2006 04:20 GMT
You can view a query in SQL view. Then copy and paste the SQL into a reply.

Duane Hookom
MS Access MVP

> What kind of views would you like to see? Are you indicating any SQLs?
> I didn't exactly get what you meant, sorry!
[quoted text clipped - 27 lines]
>> >>
>> >> > It doesn't work!!
Reply to this Message
sumana - 19 Apr 2006 21:02 GMT
the following are the queries in the sql view

qtotActEffort:

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

Query2:

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

thanks
sumana

> You can view a query in SQL view. Then copy and paste the SQL into a reply.
>
[quoted text clipped - 32 lines]
> >> >>
> >> >> > It doesn't work!!
Reply to this Message
Duane Hookom - 20 Apr 2006 04:02 GMT
Are you suggesting that records are displaying in the query where the
numeric value of DCINo is not between the numeric value in txtDCIStart and
txtDCIEnd? How about using the Query->Parameters to define the data type of
your text boxes. Also, is RewEffort text or numeric?
Signature

Duane Hookom
MS Access MVP

> the following are the queries in the sql view
>
[quoted text clipped - 54 lines]
>> >> >>
>> >> >> > It doesn't work!!
Reply to this Message
sumana - 21 Apr 2006 20:06 GMT
Example: txtDCIStart = 200 txtDCIEnd = 5000 RewEffort = 0, then it
lists a record which has DCINo = 30000 which is incorrect.(as you
suggested as a possible bug before in this discussion thread)

I cannot use query parameters coz I have to generate a lot of reports
based on same input. So, it would be best to input all the parameters
once, and allow the user to choose which report to be generated.

RewEffort is numeric(decimal)

Thanks
Sumana

> Are you suggesting that records are displaying in the query where the
> numeric value of DCINo is not between the numeric value in txtDCIStart and
[quoted text clipped - 62 lines]
> >> >> >>
> >> >> >> > It doesn't work!!
Reply to this Message
Duane Hookom - 21 Apr 2006 21:34 GMT
I have heard of issues with Decimal type fields. Try:
qtotActEffort:

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

Query2:

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

Duane Hookom
MS Access MVP

> Example: txtDCIStart = 200 txtDCIEnd = 5000 RewEffort = 0, then it
> lists a record which has DCINo = 30000 which is incorrect.(as you
[quoted text clipped - 80 lines]
>> >> >> >>
>> >> >> >> > It doesn't work!!
Reply to this Message
sumana - 27 Apr 2006 20:04 GMT
Sorry I replied late, was not working on this for some time.
These solutions what you mentioned are working.
Thanks for everything

Sumana

> I have heard of issues with Decimal type fields. Try:
> qtotActEffort:
[quoted text clipped - 99 lines]
> >> >> >> >>
> >> >> >> >> > It doesn't work!!
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.