If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Help please!
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! |
#3
|
|||
|
|||
Help please!
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! |
Thread Tools | |
Display Modes | |
|
|