Table in a database - Make a telephone number appear once for an office location.instead of multiple

[Casebusy]

Hello



I am trying to solve a question on a past exam paper, for practice.

This involved importing an excel file into the database. This file contains information about different office locations with phone numbers attached to them. It also had the names of the workers which are in each office. So for example the London office might have 10 employees and the Shanghai office may have 20 employees. In the table (in access), the london office along with the telephone will be shown many times and so will the other locations. I want to reduce it, in order for it to be shown once. So each office/location with its address is shown once. But I do not know how do it. i have attached 2 documents , the question paper and the mark scheme. The mark scheme shows a screen shot of what it looks like (the answers). i have noted a "+" on the left of the table in access, i am unsure if that is grouping or how to do it .

the screen shot is on page 4 of the mark scheme which will show you exactly what is looks like. I just do not know how to do it :-( I have also included the question paper just in case, is question 6 & 7

any help will be much appreciated

thanks in advance

[orange]

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

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

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

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.

[Casebusy]

thank you so much for your detailed reply, it is very much appreciated. now I see that i made simple mistakes by not creating the office table, and assigning the primary key, before importing.

[orange]

You are welcome.
I thought your base issue was the repeating phone numbers if/when you reported multiple employees per office location.

[Casebusy]

i deleted the employees column from the original spreadsheet to avoid redundant data, as i already had a separate employees table in the database. the one thing that confused me, was on the mark scheme. You see the plus sign to the left of the office table in the database, the + appears on each record, never seen that before

[orange]

I suggest you Google/Bing ms access subdatasheet