Very interesting query

[JB]

I have 2 tables: tblPeople and tblEmployment (one-to-many relationship).

John Smith has worked at Boeing but not necessarily on Wings
But he has Wing experience at another company.

I want to write a query that picks out all people who have BOTH Boeing AND
Wing experience but NOT necessarily at the same company/i.e. not in the same
Employment record! Can this be done??

I have a man shouting at me saying it ought to be possible but I can't see
how to do it - if it isn't possible then I'll need to give him a pretty good
explanation why!

Help!
J

 

[Peter van der Goes]

I have 2 tables: tblPeople and tblEmployment (one-to-many relationship).

John Smith has worked at Boeing but not necessarily on Wings
But he has Wing experience at another company.

I want to write a query that picks out all people who have BOTH Boeing AND
Wing experience but NOT necessarily at the same company/i.e. not in the same
Employment record! Can this be done??

I have a man shouting at me saying it ought to be possible but I can't see
how to do it - if it isn't possible then I'll need to give him a pretty good
explanation why!

Help!
J

Check out the OR keyword in SQL, or if you're using Access QBE, the "or" row
in the QBE grid.
Here's an example from a different database:

SELECT Donor.DonorID, Donor.Title, Donor.FirstName, Donor.LastName,
Pledge.PledgeID, Pledge.FundCode, Pledge.PledgeDate, Pledge.TotalPledged
FROM Donor INNER JOIN Pledge ON Donor.DonorID = Pledge.DonorID
WHERE (((Pledge.TotalPledged)>100)) OR (((Donor.Title) Like "Mr*"));

This selects donor and pledge information and returns rows WHERE either the
TotalPledged is > 100 OR the DonorTitle begins with "Mr". You can use the
same technique to retrieve information on people who either work at Boeing
OR who have experience with wings (however that experience is identified in
your database).

 

[doco]

Without knowing anything else about what you are doing, it seems at least
plausible to me that the relationship is many to many; in which case you
would need a transition table (tblEmploymentDetails). What is the field
structure of tblEmployment?

doco

 

[JB]

BUT

I want all those Donors who have, for example, made pledges where:
TotalPledged > £100
AND PledgeDate = 10/10/04
BUT the pledges that meet these criteria don't need to be in the same
record!
i.e. If Mr Jones made a pledge of £150 on 20/09/04
and he also made a pledge of £80 on 10/10/04
then I want the query to find him.
If he meets only one of these criteria then i don't want him to come up.

Clear?!

 

[JB]

tblEmployment:
ItemID, PeopleID, Company, Product, Programme

Each member of tblPeopleID can have many related records in tblEmployment

Does that help?

 

[John Vinson]

BUT

I want all those Donors who have, for example, made pledges where:
TotalPledged > £100
AND PledgeDate = 10/10/04
BUT the pledges that meet these criteria don't need to be in the same
record!
i.e. If Mr Jones made a pledge of £150 on 20/09/04
and he also made a pledge of £80 on 10/10/04
then I want the query to find him.
If he meets only one of these criteria then i don't want him to come up.

Clear?!

An IN clause works here:

SELECT <whatever>
FROM Donors
WHERE DonorID IN(Select DonorID FROM Pledges
WHERE TotablPledged > £100)
OR DonorID IN(Select DonorID FROM Pledges
WHERE PledgeDate = #10-Oct-2004#)

Note that literal dates in criteria must either be unambiguous, or use
the American month/day/year sequence.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Tom Wickerath]

Hi James,

I believe the following will work, although I haven't taken the time to set up the tables and
test it. It uses a sub-query similar to what John Vinson suggested. The subquery, when run by
itself, should return only one field. This becomes the criteria for your main query. Something
like this (untested):

Subquery: SELECT PeopleID FROM tblEmployment WHERE [Programme] = 'Wings'

Combining the above with a query that selects people, you would have something like this:

SELECT LastName, FirstName, Company
FROM tblPeople
INNER JOIN
tblEmployment ON tblPeople.PeopleID = tblEmployment.PeopleID
WHERE tblEmployment.Company="Boeing" AND tblPeople.PeopleID
In (SELECT PeopleID FROM tblEmployment WHERE [Programme] = 'Wings');


Tom

PS. I work for The Boeing Company, so I do find your query interesting!

__________________________________________

tblEmployment:
ItemID, PeopleID, Company, Product, Programme

Each member of tblPeopleID can have many related records in tblEmployment

Does that help?
_________________________________________

 

[JB]

Thanks, John, I did get your answer immediately but have been working on it
all day to get it to work in my context - fantastic, it's perfect.

Oh, I wish I was clever and/or knew SQL!

J

 

[Mike Painter]

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Two totally different questions from the same person.
Hmmm. I wonder if John gets credit from the teacher.