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

Hi Kostas

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

so it would be ST_peoplefilms.

in the second query you would join T_people to look up the names.

/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 16:01
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] How to search two records in the same field of a table

Hi Gustav,
You guess right but can't make it works
for example what is tblFilmPeople_0.IDfilm and tblFilmPeople_1.IDfilm

The original tables are
MT_films
    ID_films (one to many --> ST_peoplefilms.ID_films)
    Title1
    Title2 etc etc

ST_peoplefilms
    ID_films
    ID_person  (one to many --> T_people.ID_person
    etc etc

T_people
    ID_person
    person *name of the person

Sorry for all the inconvenience but rather something I don't understand
properly

thank's
/kostas

-----Αρχικό μήνυμα-----
From: Gustav Brock
Sent: Sunday, January 18, 2015 1:34 PM
To: Access Developers discussion and problem solving
Subject: Re: [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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com