Dcount problem

[niki]

Hey y'all,
I have a table with three fields
|KEY|KEY_Tbl1|KEY_Tbl2|
The fields KEY_Tbl1 and KEY_Tbl2 each have numbers in them (1,2,3 for field KEY_Tbl1 and 10,11,12 for field KEY_Tbl2).
I can count how many 1,2,3,10,11,12 there are in each tables but my real problem is:
How can I count, using a query or a module for each possible case (9 possible), how many entries have:
- KEY_Tbl1=1 and KEY_Tbl2=10 or
- KEY_Tbl1=1 and KEY_Tbl2=11 or
- KEY_Tbl1=1 and KEY_Tbl2=12 or
- KEY_Tbl1=2 and KEY_Tbl2=10 or
(...)
- KEY_Tbl1=3 and KEY_Tbl2=12

????? Can some one help me?
As no one stops raising the level of difficulty (for my knowledge I am at a limit close to infinite on this one!!!), if that person knows how I can get rid of the field value (1,2,3,10,11,12 ) and have this query count for me the number of times
KEY_Tbl1=" any value" and KEY_Tbl2="another value"
Giving as an output the values of KEY_Tbl1 and KEY_Tbl2 and the number of times they are found on the same line in my table...

Thx for your help
nico

 

[dcx693]

My first inclination would be a crosstab query with the values of KEY_Tbl1 on the side and the KEY_Tbl2 values across the top. The calculation in the fields would be a count.

Graphically, it would look like this:
   10   11   12
1
2 counts for each combination in here
3

I'm not sure what your other question is. Can you elaborate, s'il vous plait?

 

[dcx693]

In fact, try this for your query. Substitute the name of your data table for tblCross below:


TRANSFORM Count(tblCross.KEY) AS CountOfKEY
SELECT tblCross.KEY_Tbl1
FROM tblCross
GROUP BY tblCross.KEY_Tbl1
PIVOT tblCross.KEY_Tbl2;

 

[niki]

Hey!
thanks to you all! it was easier than I thought!!!
c ya!
nico