You are here:

Using MS Access/Access query from multiple tables/queries


Good morning. I am using Access 2007 and I need a simple way to run a query from a form that will pull information from a table and a query.  

the table is in the format of hierarchy such as Supervisor name and who they report to.

The query already runs which has multiple tables and it works great however I need to combine them.  

Here is the query:
SELECT [Earned Hours].Date, [Earned Hours].[Employee Name], [Earned Hours].[Employee ID], [Earned Hours].Department, [Earned Hours].Process, [Earned Hours].LOB, [Earned Hours].[Regular Hours], [Earned Hours].[Overtime Hours], [Earned Hours].[Core Production # of Hours], [Earned Hours].Volume, [Earned Hours].[AR&R Description], [Earned Hours].[AR&R Hours], [Earned Hours].[PTO # of Hours], [Earned Hours].[Office Close/Holiday], [Earned Hours].[Meetings/Calls # of Hours], [Earned Hours].[System Issues # of Hours], [Earned Hours].[BAU Training # of Hours], [Earned Hours].[Exception Processing Hours], [Earned Hours].[AIG Related Activites], [Earned Hours].[Admin Work Hours], [Earned Hours].[Migrations Hours], [Earned Hours].[Migrations PID], [Earned Hours].[Projects Hours], [Earned Hours].[Total Exception Time], [Earned Hours].[Total Hours], [Earned Hours].[Comments/Explanations], [Earned Hours].[Earned Hours]
FROM [Earned Hours]
WHERE ((([Earned Hours].Date) Between [Start Date] And [End Date]) AND (([Earned Hours].[Employee Name]) Like [Enter Employee Name] & "*"));

I'm not sure what you mean by combine them. If what you are asking is to include the supervisor in the result, I would need to know the structure of your tables. I don't see anything in your query that indicates a SupervisorID to be able to pull the name.

I do have some comments about your structure as shown. I see several problems:

1) it is not good practice to use spaces or special characters in object names. This can come back to haunt you later. You can use either underscores (Earned_Hours) or Camel Notation (EarnedHours) instead of spaces.

2) Your data is not normalized properly. You should not have both EmployeeID and Employee Name in your Earned Hours table. You should have a separate Employees table with info about the employee and just use the EmployeeID as a Foreign key to link to the hours

3) People names should be broken out into their components. I generally use 5 fields for people names (Salutation (Mr, Ms, etc.), First, Middle, Last, Suffix (Jr, Sr, etc.).

4) Date is a reserved word in Access and shouldn't be used for object names

5) You appear to be storing calculationsTotal Hours, Total Exception Time Whihc is not good design. Calculations should be done in queries.

6) You are encoding Data using field names which is not good design. You reall should have a table like so:

EarnedHoursID (PK Autonumber)
EmployeeID (FK)
HoursTypeID (FK)

And a lookup table for the HoursTypes (i.e. AIG Related Activities, Migration, Projects, etc.)

You want one record per hours type, not a field for each type.

I would strongly advise you to revise your design before going any further.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.