asked on

DataReport simple question

Hello,

I have a table called Inst_kinds (Kind_code, Kind_name) and a table called Instruments (Inst_num, Kind_code, Status). The value of "Status" field (Integer) can be 1, 2, 3, 4 or 5. I want to get a report which will look like this:

| Sta=1 | Sta=2 | Sta=3 | Sta=4 | Sta=5
------| ----- | ----- | ----- | ----- | -----
Kind1 | 6 | 5 | 3 | 2 | 3
Kind2 | 3 | 2 | 3 | 1 | 0
Kind3 1 | 1 | 1 3 | 4 | 0
Kind4 | 4 | 3 | 1 | 3 | 1
Kind5 | 6 | 2 | 1 | 2 | 4
..... | ... | ... | ... | ... | ...
KindN | X | Y | Z | P | Q

The data in the cells is:
Count(Inst_num) Where (Kind_code = 1) And (Stutus = 1)
Count(Inst_num) Where (Kind_code = 1) And (Stutus = 2)
Count(Inst_num) Where (Kind_code = 1) And (Stutus = 3)
Count(Inst_num) Where (Kind_code = 1) And (Stutus = 4)
Count(Inst_num) Where (Kind_code = 1) And (Stutus = 5)
Count(Inst_num) Where (Kind_code = 2) And (Stutus = 1)
Count(Inst_num) Where (Kind_code = 2) And (Stutus = 2)
etc, etc, etc, etc, etc, etc, etc, etc......

HOW CAN I DO THIS ???

slavikn

ASKER

P.S. The table was written using a Fixed font.

gencross

You can use GROUP BY. Here is an example of the SQL you will want to use the return the records.

I'm writing this off of the top of my head so you may need to modify the SQL some, but you get the general idea.

sSQL = "select count(Inst_num) AS RecCount,Status,Kind_name,kind_code from Instruments INNER JOIN tbl on Instruments.Kind_Code = Inst_kinds.Kind_code GROUP BY Kind_name,kind_code,Status"

Hope this helps.

ASKER CERTIFIED SOLUTION

gencross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slavikn

ASKER

The answer was almost correct. Anyway, I understood the idea.