counting values

[sara]

I have a table like this

ID f1 f2 f3 f4 ....
1 2 5 3 1
2 2 1 1 3
3 5 4 2 1
....
n 1 1 2 1

ID is a counter, the other fields have values going from 1 to 5, what should
I do to get a query that counts all the results for each "f", something like:

f1 f2 f3 f4 ....
1 1 2 1 3
2 2 0 2 0
3 0 0 1 1
4 0 1 0 0
5 1 1 0 0


Thank you

 

[Gary Walter]

I have a table like this

ID f1 f2 f3 f4 ....
1 2 5 3 1
2 2 1 1 3
3 5 4 2 1
...
n 1 1 2 1

ID is a counter, the other fields have values going from 1 to 5, what
should
I do to get a query that counts all the results for each "f", something
like:

f1 f2 f3 f4 ....
1 1 2 1 3
2 2 0 2 0
3 0 0 1 1
4 0 1 0 0
5 1 1 0 0

Hi Sara,

Your situation may be more complicated
than your simple example, but we might start
with a simple possible solution...

Create a table "tblNum" with one field "Num"
and enter 5 records: 1,2,3,4,5

SELECT
tbl.Num,
(SELECT Abs(Sum(t.f1=Num)) FROM tblSara As t) AS f1Cnt,
(SELECT Abs(Sum(t.f2=Num)) FROM tblSara As t) AS f2Cnt,
(SELECT Abs(Sum(t.f3=Num)) FROM tblSara As t) AS f3Cnt,
(SELECT Abs(Sum(t.f4=Num)) FROM tblSara As t) AS f4Cnt
FROM tblNum;

will give following result using your sample data:

qrySara1 Num f1Cnt f2Cnt f3Cnt f4Cnt
1 1 2 1 3
2 2 0 2 0
3 0 0 1 1
4 0 1 0 0
5 1 1 0 0


I imagine it is probably more complicated, yes?

good luck,

gary

 

[Gary Walter]

I have a table like this

ID f1 f2 f3 f4 ....
1 2 5 3 1
2 2 1 1 3
3 5 4 2 1
...
n 1 1 2 1

ID is a counter, the other fields have values going from 1 to 5, what
should
I do to get a query that counts all the results for each "f", something
like:

f1 f2 f3 f4 ....
1 1 2 1 3
2 2 0 2 0
3 0 0 1 1
4 0 1 0 0
5 1 1 0 0

Hi Sara,

Your situation may be more complicated
than your simple example, but we might start
with a simple possible solution...

Create a table "tblNum" with one field "Num"
and enter 5 records: 1,2,3,4,5

SELECT
tblNum.Num,
(SELECT Abs(Sum(t.f1=Num)) FROM tblSara As t) AS f1Cnt,
(SELECT Abs(Sum(t.f2=Num)) FROM tblSara As t) AS f2Cnt,
(SELECT Abs(Sum(t.f3=Num)) FROM tblSara As t) AS f3Cnt,
(SELECT Abs(Sum(t.f4=Num)) FROM tblSara As t) AS f4Cnt
FROM tblNum;

will give following result using your sample data
(if your table name was "tblSara"):

Num f1Cnt f2Cnt f3Cnt f4Cnt
1 1 2 1 3
2 2 0 2 0
3 0 0 1 1
4 0 1 0 0
5 1 1 0 0

 

[sara]

Thank you Gary, I'll try with your solution.
My situation doesn't look much more complicated, the only problem is that I
have up to 12 f's to anlayse, but I don't think it should create any problem.

Thanks again and I'll write if the solution did work.

Sara