How to calculate the number of records in my query

[awu]

Hi, All:
I am really struggling about this problem.

I have a query that is generated from two tables. My form has to
display all the field in this query. The query has 4 field:
ProductName
DeviceName
NumberOfComponents
Status

Field "status" has 4 kinds, 1, 2, 3, 4. The example of query is as
follows:
p1 d1 23 1
p1 d1 2 2
p1 d1 3 3
p1 d1 10 4
p2 d2 3 1
p2 d2 5 3
.....

In my form, I need display the percentage of NumberOfComponent Under
each status for one ProductName and one DeviceName. For example,

ProductName DeviceName TotalNumberOfStatus % of status 1
========== ========== ================== =============
p1 d1 (23+2+3)=28 23/28


I tried many ways, none of them are successful. Please help.

Thank you so much
awu

 

[Randy Harris]

This is a little different from what you asked for the other day in another
ng. This will do what you are asking for here:

SELECT
ProductName,
DeviceName,
Sum(Counts) AS TotalStatus,
Sum(IIf([Status]=1,[Counts],0)) / Sum(Counts) AS Status1,
Sum(IIf([Status]=2,[Counts],0)) / Sum(Counts) AS Status2,
Sum(IIf([Status]=3,[Counts],0)) / Sum(Counts) AS Status3,
Sum(IIf([Status]=4,[Counts],0)) / Sum(Counts) AS Status4,
FROM
YourQuery
GROUP BY
ProductName,
DeviceName

Note: I used Counts as the field name from the query, rather than
NumberOfComponents.