x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 153

# 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 ???
0
slavikn
• 2
• 2
1 Solution

Author Commented:
P.S.  The table was written using a Fixed font.
0

Commented:
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.
0

Commented:
Oops!  Try this SQL...

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

Author Commented:
The answer was almost correct. Anyway, I understood the idea.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.