Add another query to an existing report

[funloving]

Can you add another query to a report which has already been designed?

I have already created a group by query and created a report on it. But now I need to add a field to it which is based on another query how do i go about doing it.

Thanks in advance.

 

[lespaul]

just modify the existing query that is the report source to have the new field from the other query:

original query:

SELECT Field1, Field2, SUM(Field3) From SomeTable
GROUP BY Field1, Field2

new query:

SELECT Field1, Field2, Sum(Field3), NewField FROM SomeTable
INNER JOIN TheOtherQuery on SomeTable.FieldName = TheOtherQuery.SomeField
GROUP Field1, Field2, NewField

Leslie

Have you met Hardy Heron?

 

[funloving]

Thanks for the answer will try it.

 

[funloving]

Well my prob is like this ,

I have the following data :
Month Dept MaxSalary
April HR 100
April Payroll 60
April Admin 80


So I have this query which I created where for every month for every dept what was the max salary given. Now the thing is I have to find is which dept has taken the max salary in the month in a report,which is in above case is HR 100.

My query is as follows
select monthPay,dept,max(salary)
from sal
group by monthpay,dept

So my report has the above data and I have a group footer Month and for every month I want to have a textbox which says that for the month of April the HR dept had given the maximum salary.

How do I go about doing it.

thanks in advance.

 

[dhookom]

Create a query:

SELECT monthPay, max(salary) As MthMax
FROM sal
GROUP BY MonthPay;

Add this query to your existing query and join it so all records from your existing query are displayed. Then in your month footer, add a text box with a control source of:

=IIf(IsNull(MthMax),Null,Dept)

If you have two dept with the same max Salary, you will have issues. You would need to decide how you might want to handle those situations.


Duane
Hook'D on Access
MS Access MVP

 

[funloving]

will try it..thanks