[AccessD] How to search two records in the same field of a table

Hi Kostas

> I have a table with: IDfilms and IDpeople ..

I guess it goes like this:

IDfilm   IDpeople
10       200
10       230
10       240
10       242
11       230
12       230
12       240
12       242

Thus, People 230 and 240 both worked on films 10 and 12

You can do like this:

SELECT 
    tblFilmPeople_0.IDfilm, 
    tblFilmPeople_1.IDfilm, 
    Count(tblFilmPeople_1.IDpeople) AS FilmPeople
FROM 
    tblFilmPeople As tblFilmPeople_0 
LEFT JOIN 
    tblFilmPeople AS tblFilmPeople_1 
    ON tblFilmPeople_0.IDpeople = tblFilmPeople_1.IDpeople
GROUP BY 
    tblFilmPeople_0.IDfilm, 
    tblFilmPeople_1.IDfilm
HAVING 
    tblFilmPeople_0.IDfilm <>  tblFilmPeople_1.IDfilm
    AND 
    Count(tblFilmPeople_1) Between 2 And 3;

Save this query and create a new to extract the people:

SELECT 
    qd1.tblFilmPeople_0.IDfilm, 
    qd1.tblFilmPeople_1.IDfilm, 
    tblFilmPeople_0.IDpeople
FROM 
    (qd1 
    INNER JOIN tblFilmPeople_0
    ON qd1.tblFilmPeople.IDfilm = tblFilmPeople_0.IDfilm) 
        INNER JOIN tblFilmPeople AS tblFilmPeople_1 
        ON (qd1.tblFilmPeople_1.IDfilm = tblFilmPeople_1.IDfilm) 
             AND (tblFilmPeople_0.IDpeople = tblFilmPeople_1.IDpeople)
ORDER BY 
    qd1.tblFilmPeople_0.IDfilm, 
    qd1.tblFilmPeople_1.IDfilm, 
    tblFilmPeople_0.IDpeople;

/gustav

________________________________________
Fra: accessd-bounces at databaseadvisors.com <accessd-bounces at databaseadvisors.com> på vegne af Kostas Konstantinidis <kost36 at otenet.gr>
Sendt: 18. januar 2015 11:22
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] How to search two records in the same field of a table

Steve,
What I really need is something like the follow... but it doesn't work.
I don't want to count records but to find the film titles depending on searching by T_people.person

SELECT MT_films.Title1, T_people.person
FROM T_people INNER JOIN (MT_films INNER JOIN ST_peoplefilms ON MT_films.ID_films = ST_peoplefilms.ID_films) ON T_people.ID_person = ST_peoplefilms.ID_person
GROUP BY MT_films.Title1, T_people.person
HAVING (((T_people.person)=[Enter 1st name] And (T_people.person)=[Enter 2nd name]));


Regards
/kostas


-----Αρχικό μήνυμα-----
From: Steve Schapel
Sent: Sunday, January 18, 2015 11:43 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] How to search two records in the same field of a table

Kostas

Try it like this?...

SELECT IDfilms, Count(IDpeople) AS PeoplePerFilm
FROM YourTable
GROUP BY IDfilms
HAVING Count(IDpeople)=2 Or Count(IDpeople)=3

Regards
Steve

-----Original Message-----
From: Kostas Konstantinidis
Sent: Sunday, January 18, 2015 10:22 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] How to search two records in the same field of a table

Hi all,
I have a table with: IDfilms and IDpeople
What I need to do is to find after searching all the IDfilms in which two or
three IDpeople worked together

I tried a union query but it doesn't work

thank's a lot
/kostas