An unanswered count question

[monsjic]

I posted the question below a while ago, but never got a response. So I thought I'd repost it and see if anyone has any ideas?



I have data like below:

Table1 Table2
Fld1: Fld2: Fld3: FldA: FldB: FldC:
1 A 1 1 A 1
2 B 2 2 A 1
3 B 3 3 A 4
4 C 5 4 B 3
5 A 1
6 C 5
7 C 5

I run the SQL below and get the output below it.

SELECT Fld2, Fld3, Count('x') AS COUNT
FROM Table1, Table2
WHERE Fld3=FldC AND Fld2=FldB
GROUP BY Fld2, Fld3;

RESULTS FOR COUNT
DESIRED
Fld2: Fld3: Count: COUNT:
A 1 3 1
B 3 1 1
C 5 2 1


Does anyone know how to get my desired counts?

Thanks

 

[smedvid]

details are a little confuzing... but here is a quick shot - psuedo code...

Create a Union query to combine table1/table2, then create a second query to sum the results...

Select Fld1 as F1, Fld2 as F2,Fld3 as F3 from table1
UNION
Select FldA as F1, FldB as F2, FldC as F3 from table2;

Select F1,F2,Count(F3)
From new_query
Group by F1,F2

Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/