Help please!

Hi
My query does not work correctly but I really can't see the problem. What I
want to do is to count the number of times T_Details_EnFormation.NIP is
either in field EnFormation1 OR in field EnFormation2. Sometimes I get
expected result for maybe 1/4 of the lines. The remaining is wrong. Here's
what I got :

SELECT T_Details_EnFormation.NIP, Count(T_Details_EnFormation.NIP) AS
CompteNIP, T_Details_EnFormation.NbFormationRequise
FROM Horaire_FormateursBenevoles, T_Details_EnFormation
WHERE Horaire_FormateursBenevoles.EnFormation1=T_Details _EnFormation.NIP OR
Horaire_FormateursBenevoles.EnFormation2=T_Details _EnFormation.NIP
GROUP BY T_Details_EnFormation.NIP, T_Details_EnFormation.NbFormationRequise

Any help would be greatly appreciated!! Thanks in advance!

Dear Jo:

So I can study it, I'm rewriting your query:

SELECT D.NIP, Count(D.NIP) AS CompteNIP,
D.NbFormationRequise
FROM Horaire_FormateursBenevoles F, T_Details_EnFormation D
WHERE F.EnFormation1 = D.NIP
OR F.EnFormation2 = D.NIP
GROUP BY D.NIP, D.NbFormationRequise

All I have done is to change whitespace and use aliases. If I did that
correctly, it would not affect the query functionally.

For the cases where the count is not correct, is it too high or too low, or
missing, or what?

I recommend you post the data for some result that is wrong. Tell us what
the query is giving and what you expect. Please only post the columns that
are referenced in your query: NIP and NbFormationRequise from
T_Details_EnFormation and EnFormation1 and EnFormation2 from
Horaire_FormateursBenevoles. With any luck you can pust just those rows
from the two tables for one value of NIP.

The structure is that of an INNER JOIN but on one of two possible columns in
the T_Details_EnFormation table. That's an unusual construction. When you
use a cross-join and then filter on some "key" column(s) that's an older way
of specifying an INNER JOIN. I don't know yet if that's the possible source
of any difficulty.

Tom Ellison


"jo152" wrote in message
...
Hi
My query does not work correctly but I really can't see the problem. What
I
want to do is to count the number of times T_Details_EnFormation.NIP is
either in field EnFormation1 OR in field EnFormation2. Sometimes I get
expected result for maybe 1/4 of the lines. The remaining is wrong. Here's
what I got :

SELECT T_Details_EnFormation.NIP, Count(T_Details_EnFormation.NIP) AS
CompteNIP, T_Details_EnFormation.NbFormationRequise
FROM Horaire_FormateursBenevoles, T_Details_EnFormation
WHERE Horaire_FormateursBenevoles.EnFormation1=T_Details _EnFormation.NIP
OR
Horaire_FormateursBenevoles.EnFormation2=T_Details _EnFormation.NIP
GROUP BY T_Details_EnFormation.NIP,
T_Details_EnFormation.NbFormationRequise

Any help would be greatly appreciated!! Thanks in advance!

Dear Jo:

Oh, also the column NbFormationRequise.

Also, you could run this:

SELECT D.NIP, D.NbFormationRequise
FROM Horaire_FormateursBenevoles F, T_Details_EnFormation D
WHERE F.EnFormation1 = D.NIP
OR F.EnFormation2 = D.NIP
ORDER BY D.NIP, D.NbFormationRequise

You could then count manually the number of rows for each
NIP/NbFormationRequise combination. This should match what the query
reports, but in more deatail. Does that help explain it?

Tom Ellison


"Tom Ellison" wrote in message
...
Dear Jo:

So I can study it, I'm rewriting your query:

SELECT D.NIP, Count(D.NIP) AS CompteNIP,
D.NbFormationRequise
FROM Horaire_FormateursBenevoles F, T_Details_EnFormation D
WHERE F.EnFormation1 = D.NIP
OR F.EnFormation2 = D.NIP
GROUP BY D.NIP, D.NbFormationRequise

All I have done is to change whitespace and use aliases. If I did that
correctly, it would not affect the query functionally.

For the cases where the count is not correct, is it too high or too low,
or missing, or what?

I recommend you post the data for some result that is wrong. Tell us what
the query is giving and what you expect. Please only post the columns
that are referenced in your query: NIP and NbFormationRequise from
T_Details_EnFormation and EnFormation1 and EnFormation2 from
Horaire_FormateursBenevoles. With any luck you can pust just those rows
from the two tables for one value of NIP.

The structure is that of an INNER JOIN but on one of two possible columns
in the T_Details_EnFormation table. That's an unusual construction. When
you use a cross-join and then filter on some "key" column(s) that's an
older way of specifying an INNER JOIN. I don't know yet if that's the
possible source of any difficulty.

Tom Ellison


"jo152" wrote in message
...
Hi
My query does not work correctly but I really can't see the problem. What
I
want to do is to count the number of times T_Details_EnFormation.NIP is
either in field EnFormation1 OR in field EnFormation2. Sometimes I get
expected result for maybe 1/4 of the lines. The remaining is wrong.
Here's
what I got :

SELECT T_Details_EnFormation.NIP, Count(T_Details_EnFormation.NIP) AS
CompteNIP, T_Details_EnFormation.NbFormationRequise
FROM Horaire_FormateursBenevoles, T_Details_EnFormation
WHERE Horaire_FormateursBenevoles.EnFormation1=T_Details _EnFormation.NIP
OR
Horaire_FormateursBenevoles.EnFormation2=T_Details _EnFormation.NIP
GROUP BY T_Details_EnFormation.NIP,
T_Details_EnFormation.NbFormationRequise

Any help would be greatly appreciated!! Thanks in advance!