Big challenge, Field that appear only once when linked to another one 1

[haerion]

Hi Guys, I have a query that is as follow:

SELECT [Jour de visite].Sem, [Jour de visite].NoES, [Jour de visite].Nom, [Jour de visite].Prénom, [Étudiant de Rosemont TAD].NoER
FROM [Unique étudiant], [Jour de visite] INNER JOIN [Étudiant de Rosemont TAD] ON [Jour de visite].Sem = [Étudiant de Rosemont TAD].Jour
GROUP BY [Jour de visite].Sem, [Jour de visite].NoES, [Jour de visite].Nom, [Jour de visite].Prénom, [Étudiant de Rosemont TAD].NoER;

The problem is that where for the NoES and the NoER the day of the week is equal, then for each NoES, I got all the NoER that are on the same day, but I only want one NoER for each NoES since NoER AND NoES are people and i need to match 1 to another 1.

Been 2 week im stuck on this, any help would be greatly appreciated!

 

[lespaul]

If there are multiple records in Etudiant de Rosemont TAD for a matching NoES, which NoER record do you want to return?

Can you provide some sample records from [Jour de visite] and [Unique etudiant] and [Etudiant de Rosemont TAD] (are these all tables or are there queries involved?)

Do you realize that by using a cartesian join (FROM [Unique étudiant], [Jour de visite]) you are creating a recordset that has been "multiplied"? See understanding SQL joins link below for more information on joins.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[haerion]

Here is an example of the table I have:

Etudiant de Rosemont Tad:
NoER Lastname Firstname Day
0526326 bell cedric 1
0526326 bell cedric 2
0538495 bell nathaniel 1
0538495 bell nathaniel 2

Jour de visite:
NoES Lastname Firstname Day
865749 Greenwood Robert 1
865985 Bienno Yannick 1
864897 Tanaka Chen 2

On the Jour de visite table, I have all the high school student and the day they can come to the college for their matching with a college student, and on the other table, the TAD one, I have all the college student with the day they have college, so i want to be able to match 1 high school student with 1 college student on each day, there will be more college student than high school one.

Thanks for your time leslie!

 

[lespaul]

I think by adding FIRST to the query you will only get one result, and maybe what you are looking for:

SELECT [Jour de visite].Sem, [Jour de visite].NoES, [Jour de visite].Nom, [Jour de visite].Prénom, [b]FIRST([/b][Étudiant de Rosemont TAD].NoER[b])[/b]
FROM [Unique étudiant], [Jour de visite] INNER JOIN [Étudiant de Rosemont TAD] ON [Jour de visite].Sem = [Étudiant de Rosemont TAD].Jour
GROUP BY [Jour de visite].Sem, [Jour de visite].NoES, [Jour de visite].Nom, [Jour de visite].Prénom;

Leslie

 

[haerion]

Unfortunately, I tried this one, when I do first it give me the first college student for each school student but the first is always the same since on the same day there is more than one high school student so i get the same colleges students for each HS, I would need something like First but with a fonction(if it exist) that make the college student appear only one time on the query.

 

[PHV]

Typed, untested.

Create a query named qryRankES:
SELECT A.Sem, A.NoES, A.Nom, A.Prénom, Count(*) AS Rank
FROM [Jour de visite] AS A INNER JOIN [Jour de visite] AS B ON A.Sem = B.Sem AND A.NoES >= B.NoES
GROUP BY A.Sem, A.NoES, A.Nom, A.Prénom

Create a query named qryRankER:
SELECT A.Day, A.NoER, Count(*) AS Rank
FROM [Étudiant de Rosemont TAD] AS A INNER JOIN [Étudiant de Rosemont TAD] AS B ON A.Day = B.Day AND A.NoER >= B.NoER
GROUP BY A.Day, A.NoER

And now your query:
SELECT S.Sem, S.NoES, S.Nom, S.Prénom, R.NoER
FROM qryRankES AS S INNER JOIN qryRankER AS R ON S.Sem = R.Day AND S.Rank = R.Rank

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[haerion]

PHV, you are a genius!!! It work really great so far, thanks a lot for your help!