Your tables structures need to contain common fields that will allow you to create joins and relationships among them like (roughly):
tblEmployees
EmployeeID (Primary Key)
- EmpLName
- EmpFName
- EmpExt
- DeptID (Foriegn Key)
- StoreID (Foreign Key)
tblDepartments
- DepID (Primary Key)
- DepName
tblStores
- StoreID (PK)
- Address
- Manager (actually redundant because managers are employees too
which you could indicate with a 'Reports To' field in your
employee table and use the employee ID to identify the
manager (called a 'self-join' in you employees table))
Once the common fields are in place, establish referential integrity between them (there are two 1-to-many relationships suggested above), then write a query that joins the tables and base your report on it. The query would look like (very roughly):
SELECT
tblEmployees.EmployeeID,
tblEmployees.EmpLName,
tblEmployees.EmpFName,
tblDepartments.DepName,
tblStores.StoreID,
tblStores.Address,
tblStores.Manager
FROM
tblStores
INNER JOIN
(tblDepartments
INNER JOIN
tblEmployees
ON blDepartments.DepID = tblEmployees.DeptID)
ON tblStores.StoreID = tblEmployees.StoreID;
The basic idea is to establish common fields in your tables that you can join them on, then write queries that create those joins.
HTH,
Bob
|
