Relation division

  • Thread starter Thread starter Lukas via AccessMonster.com
  • Start date Start date
L

Lukas via AccessMonster.com

Hi all.
I realy trouble with

PILOTS PLANE

Celko Piper Cub
Higgins B-52 Bomber
Higgins F-14 Fighter
Higgins Piper Cub
Jones B-52 Bomber
Jones F-14 Fighter
Jones Boing
Lukas F-16
Lukas Fantom
Smith B-1 Bomber
Smith B-52 Bomber
Smith F-14 Fighter
Smith Boing
Smith F-16
Wilson B-1 Bomber
Wilson B-52 Bomber
Wilson F-17 Fighter
Wilson F-14 Fighter
Wilson Gripen
Wilson F-16

PLACE HANGAR
H1 B-1 Bomber
H1 B-52 Bomber
H1 F-14 Fighter
H2 F-16
H2 Fantom
H3 Boing

What I need?
I need this reports

PILOTS PLACE
Jones H3
Lukas H2
Smith H1
Smith H3
Wilson H1

Any idea..??
Thanks
 
Or give me some subselect :o)
Please......
Hi all.
I realy trouble with

PILOTS PLANE

Celko Piper Cub
Higgins B-52 Bomber
Higgins F-14 Fighter
Higgins Piper Cub
Jones B-52 Bomber
Jones F-14 Fighter
Jones Boing
Lukas F-16
Lukas Fantom
Smith B-1 Bomber
Smith B-52 Bomber
Smith F-14 Fighter
Smith Boing
Smith F-16
Wilson B-1 Bomber
Wilson B-52 Bomber
Wilson F-17 Fighter
Wilson F-14 Fighter
Wilson Gripen
Wilson F-16

PLACE HANGAR
H1 B-1 Bomber
H1 B-52 Bomber
H1 F-14 Fighter
H2 F-16
H2 Fantom
H3 Boing

What I need?
I need this reports

PILOTS PLACE
Jones H3
Lukas H2
Smith H1
Smith H3
Wilson H1

Any idea..??
Thanks
Click to expand...
 
SELECT Distinct P.Pilots, PL.Hanger
FROM PilotsPlaneTable as P
INNER JOIN PlaceHangerTable as PL
ON P.Plane = PL.Hanger
 
Hi,


If I understand, Plane/Hangar can be seen as describing a skill, Place
describing a Job, (first table, skill of pilots, second table, skills
required for a job), and you want all pilots having ALL the skills to
perform given job?

SELECT a.pilots, b.place
FROM skills as a INNER JOIN jobs as b ON a.plane = b.Hangar
GROUP BY a.pilots, b.place
HAVING COUNT(*)=(SELECT COUNT(*) FROM jobs as c WHERE b.place=c.place)



should do.

Hoping it may help,
Vanderghast, Access MVP
 
Back
Top