Report Based on 2 Tables

[AuditorCMM]

We are trying to create a database to track employee time. Table #1 contains
the names of the projects we are working on along with the budgeted hours for
that project and the staff responsible.

Table #2 is populated by the actual time entered by employees. The employee
selects the project they are working on (drop down menu based on Table #1)
and then enters the date and the number of hours spent.

We need a report that compares the budgeted and actual hours for each
project. It would contain the project name and budgeted hours from Table #1
and the staff name, date, and actual hours from Table #2. If possible, we
would also like to have a column that shows the difference between the
budgeted and actual hours.

I am assuming we need to develop some sort of query based on the two tables,
but I can't figure out a way to make it work.

Please help!! Thanks.

 

[Duane Hookom]

It isn't clear whether everyone who works on a project is budgeted to work
on it. Regardless, I would probably create a union query first like:

SELECT ProjectID, EmployeeID, BudgetHours as Hrs, "Budgeted" as Category
FROM tblOne
UNION ALL
SELECT ProjectID, EmployeeID, Sum(ActHours), "Actual"
FROM tblTwo
GROUP BY ProjectID, EmployeeID, "Actual";

Then create a crosstab from the above that uses ProjectID and EmployeeID as
Row Headings, Category as the Column Heading, and Sum(Hrs) as the Value.

 

[AuditorCMM]

Is there any way to do this without having to work with the SQL based
queries? I really have trouble understanding those. If not, is there a way
to set up a report based on these 2 tables and then somehow format it to get
what I need?

 

[Duane Hookom]

There is no method that I am willing to explore since the query methods are
the most efficient and easiest. If you provided some table and field names
as well as relationships, someone could provide all the SQL for you.