Show us your working database and any attempts/work you have done.
The office table has 1 telephone number per office. So if you list office info only, there is only 1 telephone number per office.
If you create a query to link Employees to Office, then any office with more than 1 employee will repeat the office Telephone number for each Employee in that Office.
You could create a report to select all employees and the office they work in, and produce output that
shows the Office and location and telephone and Employee info.You Group the output by Office location and telephone, and have each employee details beneath the group heading.
Note: I mocked up a sample to show an approach. I created my own Employee table with a few fields to show the concept. I'm sure
there are other ways to do this.
My starting tables are:
Officephones
Office |
ID |
Telephone_No |
Office_Code |
London |
1 |
4420878787 |
LON |
Shanghai |
2 |
86440044001 |
SHA |
Denver |
3 |
15550111 |
DEN |
Tawara |
4 |
614614614614 |
TAW |
Cairo |
5 |
202345678 |
CAI |
Bangkok |
6 |
6666444422 |
BAN |
New York |
7 |
15550101 |
NEW |
Male |
8 |
92345678 |
MAL |
Rio de Janeiro |
9 |
555555555 |
RIO |
Employee_Temp
Emplid |
Firstname |
Lastname |
Office_Code |
FavouriteSport |
CambridgeGrad |
140 |
Jane |
Doe |
BAN |
Cricket |
False |
219 |
Jim |
Jones |
NEW |
Swimming |
False |
2340 |
Porky |
Pig |
NEW |
Baseball |
False |
2341 |
Heeza |
Payne |
DEN |
Skiing |
False |
2345 |
Sam |
Jones |
BAN |
Football |
True |
2430 |
Phil |
Erupp |
LON |
Football |
True |
2431 |
Cecil |
Roads |
LON |
Football |
True |
2436 |
Jeremy |
Smith |
CAI |
Canoeing |
False |
I created the following queries
qOfficeEmployees
Code:
SELECT Employee_Temp.Firstname, Employee_Temp.Lastname, Employee_Temp.FavouriteSport
, Employee_Temp.CambridgeGrad, Employee_Temp.Office_Code, OfficePhones.Office
, OfficePhones.Telephone_No
FROM Employee_Temp INNER JOIN OfficePhones
ON Employee_Temp.Office_Code = OfficePhones.Office_Code
ORDER BY Employee_Temp.Office_Code DESC;
qOfficeLocsGroup
Code:
SELECT office_code
, OfficePhones.Office_Code & " " & OfficePhones.Office & " " & OfficePhones.Telephone_No AS Groupname
FROM officephones;
Then using the above queries, create a third query,
qGroupEmployeeByOffice
Code:
SELECT qOfficeLocsGroup.Groupname, qOfficeEmployees.Firstname
, qOfficeEmployees.Lastname, qOfficeEmployees.FavouriteSport
, qOfficeEmployees.CambridgeGrad
FROM qOfficeEmployees INNER JOIN qOfficeLocsGroup
ON qOfficeEmployees.Office_Code = qOfficeLocsGroup.office_code
ORDER BY qOfficeLocsGroup.Groupname;
which was used as Recordsource for the report.