Query help needed (getting wrong results)

[jonathangr]

Hello
I am getting wrong results with my query so I hope you can help me check what the problem is. What I want to do is to count the number of times a field (NIP) is in field EF1 or in field EF2.

Here's my query:

SELECT table1.NIP, Count([table2]![EF1] And [table2]![EF2]) AS CountNIP 
FROM table1, table2 WHERE (((table2.EF1)=[table1].[NIP])) OR (((table2.EF2)=[table1].[NIP]))
GROUP BY table1.NIP;

Thanks in advance for your help

 

[Melagan]

A bit more information on how table1 and table2 are related would help. Could you post some example data from table1 and table2 and how you want the result to be?



~Melagan
______
"It's never too late to become what you might have been.

 

[jonathangr]

Sure, here's some example data:

table1:
Fct NIP Month Year FR FM PS
--------------------------------------------------
2 2266 5 2005 5 0 No

table2:
Form EF1 EF2 Day Month Year Period
----------------------------------------------------------
507 2264 2290 4 3 2005 08h to 12h
1960 2266 0 12 3 2005 16h to 20h
1993 2277 2266 10 4 2005 20h to 24h

query:
NIP CountNIP
------------
2266 2
...

My goal here is to count the number of times NIPs (from table1) are either in column EF1 or EF2 in table2. In this example, 2266 is 1 time in the first (EF1) and 1 time in the second (EF2), so that's 2.

Thanks for your help!

 

[Melagan]

How's this?

SELECT t1.NIP, Count((t2.[EF1]) + (t2.[EF2)) as NIPCount  
FROM table1 t1, table2 t2 
WHERE (((t2.EF1)=t1.[NIP])) OR (((t2.EF2)=t1.[NIP]))
GROUP BY t1.NIP;

~Melagan
______
"It's never too late to become what you might have been.

 

[jonathangr]

thanks for your answer
unfortunatly, the results are the same (wrong)

 

[jonathangr]

I don't know if this changes something but in table1, some NIPs come several times (The primary keys are Fct, NIP, Month and Year). I've already tried DISTINCT(NIP) in the SELECT but I get the same wrong results

 

[Melagan]

Can you post what the "wrong" results are?


~Melagan
______
"It's never too late to become what you might have been.

 

[jonathangr]

Hello again,
Here are a part of the results of my query and expected results by manually counting the NIPs in EF1 and EF2:
NIP CountNIP Expected
2062 9 3
2073 4 1
2264 3 3
2265 1 1
2266 8 2
2267 1 1
2268 5 5
2272 1 1
2274 20 5

 

[jonathangr]

I haven't found the problem but I found a way to bypass it. It seems like the times there are multiples entries for the same NIP, the CountNIP result is multiplied the number of times the NIP appears witch I find wierd since I use DISTINCT. Anyway, I made a function to divide CountNIP by the number of times it appears in table1 so now I'm ok. Thank again Melagan for your time and help

 

[lespaul]

it's because the query you are using:

SELECT t1.NIP, Count((t2.[EF1]) + (t2.[EF2)) as NIPCount
FROM table1 t1, table2 t2
WHERE (((t2.EF1)=t1.[NIP])) OR (((t2.EF2)=t1.[NIP]))
GROUP BY t1.NIP;

uses a cartesan product. Try this:

SELECT t1.NIP, Count((t2.[EF1]) + (t3.[EF2)) as NIPCount
FROM ((table1 t1
INNER JOIN table2 t2 ON t2.EF1=t1.[NIP])
INNER JOIN Table2 t3 ON t3.EF2=t1.[NIP]
GROUP BY t1.NIP;

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

 

[PHV]

You wanted something like this ?
SELECT A.NIP, Count(*) AS CountNIP
FROM table1 AS A INNER JOIN (
SELECT DISTINCT EF1 AS NIP FROM table2
UNION ALL SELECT DISTINCT EF2 FROM table2
) AS U ON A.NIP = U.NIP
GROUP BY A.NIP

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

 

[jonathangr]

Oh I see! It seems to work great. Thanks for your help. I'll know for next time