Calculating from two different reports

[JanesC]

I've created 2 reports from queries,
1 calculates the NoOfItems processed, per person, per month.
2 calculates the NoOfHours available to work, per person, per month.

I want to divide the NoOfItems by the NoOfHours to find out the number of items processed per hour.

I can't find a way to link the 2 querys, so i can't get both the totals on the same report.

Can the totals calculated in the queries be recorded in the tables?
Can I create a calculation in a new report using the totals in the other reports?
Any other ideas on how to get the items per hour?

 

[LonnieJohnson]

Have you tried creating a third query and joining the first two queries in there? prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[JanesC]

Yes, i get this message 'You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that . If so, try chosing fields from only the table or only the query.'

I think it's because some of the tables are linked via ODBC, so i can't create relationships between them and the 'normal' tables.

 

[MichaelRed]

Why is gizzard involved? He is limited in his capability. Just use the query grid and drag the two table onto the tables area. You selecte te join field(s) and type(s). the fileds are joined by drag/drop of field named between tables. Click /righ click on the join line to set the properties (if not the default inner join). Assuming the "per person" uniquely selects the person in each table, you shouls be able to do the join on this single field. To Calculate the new value, get the make a calc field:

PerRate: [NoOfItems]/[NoOfHours]

And group by "Month"

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JanesC]

Thanks everyone, i've realised why i can't join those 2 queries:

1 (NoOfItems)has several records per person per day.
2 (NoOfHours)has one record per month per person on the 1st of every month.

Can i get Access to sum all the records for 1 month in the one query so i can relate it to the one record in the other?

 

[MichaelRed]

Aggregate query. LOok at the Capital Sigme in the Query Grid. Use Help.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over