any idea on calculating average?

[sk88]

I have two queries. Both the queries comes from the same table.


Therefore, both queries have similar columns.

qry3
Fiscal Year
Quarter
Call
Visit
TotalOfMetricsID


qry4
Fiscal Year
Quarter
Yes
TotalOfMetricsID


the quarter consists of Q1, Q2, Q3, and Q4 of the fiscal year

Now I want to create a query where I can get the %/average of qry3 and qry4 with respect to the quarters and Fiscal year..

like
2008 Q1 = AVERAGE
2008 Q2 =AVERAGE
2009 Q1 =AVERAGE



The average/% should have formula like
(qry4totalofmetricsID/qry3totalofmetricsID)*100


Now how should I go about doing this? any ideas anyone?

[June7]

So both queries have a record for each quarter for each year? If so, join one query to the other on the year and quarter fields.

[sk88]

So both queries have a record for each quarter for each year? If so, join one query to the other on the year and quarter fields.

Thanks June! Yes, both the queries have a record for each quarter for each year. So you mean I join them together and then get the % ? But the % is calculated from the 'totalmetricsID'... How do I make it happen then?

And will my new query have fields like

Fiscal Year
Quarter
qry3TotalMetricsID
qry4TotalMetricsID
Average/%

[June7]

I don't understand your confusion. Haven't you done queries that do calculations with fields? Using the query designer, join queries, drag fields to the design grid, create calculation with the two TotalMetricsID fields. Calc expression goes in the Field row of the grid.