I am using this query (report) in access.

SELECT contracts.projtype,contracts.rec_id AS contracts_rec_id, contracts.contractno, contracts.contname, contracts.contamt, contracts.jobno, contracts.strdate, contracts.comdate, contracts.extdate, contracts.contr, contracts.pstatus, employees.rec_id AS employees_rec_id, employees.empno AS employees_empno, employees.name, employees.mname, employees.lname, employees.posit, employees.dept, employees.bstatus, approvals.rec_id AS approvals_rec_id, approvals.empno AS approvals_empno, approvals.appsta, approvals.appstrdate, approvals.munref, approvals.munletdate, approvals.dcilref, approvals.dcilletdate, approvals.apppos, approvals.appcont, assignments.rec_id AS assignments_rec_id, assignments.empno AS assignments_empno, assignments.aposit, assignments.alocat, assignments.astrdate, assignments.aenddate, assignments.cshift
FROM contracts,employees,approvals,assignments where contracts.rec_id = employees.rec_id and employees.empno = approvals.empno and approvals.empno = assignments.empno


Above access query create report like this.


Addition work

06367 Martin Manager Inspector ……

Road Widening

12121 Jones Salesman RE ….
12121 Jones Salesman RE ….

Tunnels and Intersections

10100 Peter Accountant Inspector ….
10100 Peter Accountant Re…..


Problem is that above query retrieve only one employee’s information in each projtype field.
why above query does not retrieve other employees information which are also working same projtype (field)
ie Addition work, Road Widening and Tunnels and Intersections ?
(Group by base on projtype)


table fields

CONTRACTS TABLE
rec_id int 4
contractno varchar 10 (PK)
contname varchar 50
projtype varchar 50
..
EMPLOYEES TABLE
rec_id int 4
empno varchar 10 (PK)
name varchar 50
bstatus varchar 50
.
APPROVALS TABLE
rec_id int 4 (PK)
empno varchar 50
appsta varchar 50
appstrdate datetime
..
ASSIGNMENTS TABLE
rec_id int 4 (PK)
empno varchar 10
aposit varchar 50
….


Please help.

Mateen