three tables data

Emp table.......Approvals table........Assignments table
empno pk).......(rec_id pk)............rec_id pk)
name............contractno.............acontract
----........... ----------------.......-------------
Ayad............221.....................221
Martin..........221.....................221
Mustafa.........221.....................SW3-23
Gangadharan.....221.....................229
Mathew..........229.....................221
Sujin...........2016....................221
Santra..........982.....................221
Suraphan........222.....................221
.....
and so on.....


how can retrieve the records from the three tables that Result will display like following .
how can use group by or cross join or full join condition in the following query that it give following result.
I use following query in ACCESS report but it not give all records ie.
table save in SQL Server which is link with the ACCESS report.
(empno field also in the approvals and assignments table.)


SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees inner JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments ON employees.empno = assignments.empno



Result.
--------
Contract 221
Name...........approvals/assignment contracts
--------.......------------------------------
Ayad............221
Martin..........221
Mustafa.........SW3-23
Gangadharn......229
Mathew..........229
Sujin...........2016
Santra..........982
Suraphan........222
.....
and so on....


ie. Contract 221 how many employees are approvals and assignments
same like it show all other contract, 229,2016,282......

Please help.

Mateen

a suggestions you may consider. Check your statement for accuracy

SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate, count(empployees.empno) as Approved
FROM (employees inner JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments ON employees.empno = assignments.empno,count(employees.empno) as Assignments
group by employees.name

thanks for your response.

I try you query but it give erro on . (dot)
I check it but I could not found the problem.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.



problem in contractno and acontract.
how can retrieve data from two column ie contractno and acontract
it crose joing boths columns and retrieve the data.
ie. contract 221 present in the contractno field and also in acontract field.

samply if data store like this in three tables.

empno...name......contractno...acontract
-----------------------------------------
101.....Ayad........221.......2016
102.....Martin......2016......221
103.....Mustafa.....221.......221
104.....Gangadharm..984.......2002
105.....Jones.......2002......984
106.....Mathew......229.......221
107.....Sujin.......221.......981
108.....Santra......982.......2016
109.....Suraphan....222.......221
110.....Sumi........981.......2002


by the query Result should be display like this.

contract 221
--------------------------------
empno..name.....approvals/assign
101....Ayad.......2016
102....Martin.....221
103....Mustaf.....221
106....Methew.....221
107....Sujin......981
109....Suraphan...221

ie. contract 221 six employee are working(including some are approaved and some are assign)
contractno means approvals's contracts
acontract mean assignment's contract.



regards.

Mateen

Looks like you have not corrected the mis-spelt word in that query.

remove one "P" from --- count(empployees.empno)

-Vijay G

thanks for response.
I remove one p
and use it but error is same ie.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '.'.

tables name and fields name are correct.



SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate, count(employees.empno) as Approved
FROM (employees inner JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments ON employees.empno = assignments.empno,count(employees.empno) as Assignments
group by employees.name


regards.

Mateen

The section in blue doesn't look like it belongs...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks for response.

query giving desire records.
I use having cluase for the separate the records (each contract)


SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate, count(employees.empno) as approved
FROM (employees inner JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments ON employees.empno = assignments.empno
group by employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
having assignments.acontract='221'


without having cluase it retrieve all records (all contracts) (including approved and assignments)

and when I use having cluase it retrieve only ie.
having approvals.contractno='221' 3 records
having assignmets.acontractno='221' 4 records

our company want to classified further records. ie

how can retrieve records of each contracts
including approvals and assignments ?

data store like this in three tables.

empno...name......contractno...acontract
-----------------------------------------
101.....Ayad........221.......2016
102.....Martin......2016......221
103.....Mustafa.....221.......221
104.....Gangadharm..984.......2002
105.....Jones.......2002......984
106.....Mathew......229.......221
107.....Sujin.......221.......981
108.....Santra......982.......2016
109.....Suraphan....222.......221
110.....Sumi........981.......2002


Result should be like this.

contract 221
--------------------------------
empno..name.....approvals/assign
101....Ayad.......2016
102....Martin.....221
103....Mustaf.....221
106....Methew.....221
107....Sujin......981
109....Suraphan...221


each contract show records include approvals and assignments
records.

Half problem is solve, only need further separation of
each contract.

Regards.

Mateen