access, queries for report display

[akika]

Hi,



I have a table "Customers" with fields as short_text data type

ID	CustNum	CustName	Status	Received_flag	Category_Flag
1	1234	Akira	Active	Yes
2	5456	Kate	Active	No
3	3555	Kim	Inactive	No	Yes
4	9823	Jane	Active	Yes	No
5	4563	Jim	Active	Yes	Yes

Pls help to build / change a query to get below data:

SELECT DISTINCTROW Count(*) AS [Count Of Customers], Count(Customers.Status) AS CountOfStatus, Count(Customers.Received_flag) AS CountOfReceived_flag, Count(Customers.Category_Flag) AS CountOfCategory_Flag
FROM Customers
WHERE (((Customers.Received_flag)="Yes")) OR (((Customers.Category_Flag)="Yes")) OR (((Customers.Status)="Active"));



Representations:

Total Count Of records | Count Status Active | Count Category Flag Yes | Count Received Flag Yes | Percentage Category
5 | 4 | 2 | 3 | %

[ranman256]

make 2 queries,Q1 to make value of the field result,
Q2: count them

Q1:
select IIF([Active],1,0) as ActiveCt, IIF([Category_Flag]="Yes",1,0) as CataCt ... from table

then Q2, sum the coumns:
select sum(ActiveCt),Sum(CataCt) from Q1

[kd2017]

Since all fields are short_text data type this won't be very efficient:

SELECT Count(Customers.ID)                         AS CountOfCustomers, 
       Sum(IIf([Status] = "Active", 1, 0))         AS CountOfStatus, 
       Sum(IIf([Category_Flag] = "Yes", 1, 0))     AS CountOfCategory_Flag, 
       Sum(IIf([Received_flag] = "Yes", 1, 0))     AS CountOfReceived_flag, 
       [CountOfCategory_Flag] / [CountOfCustomers] AS PercentageCategory 
FROM   Customers; 

It would be better to use a foreign key to reference a customer, and to use a boolean for the received, category, and status flags. Even better still you might use integer datatypes instead of access' yes/no booleans: http://allenbrowne.com/NoYesNo.html

[akika]

thanks its working..
I would like to add one more field Order_status containing values (Complete With change, Complete no change, Complete, Incomplete, Work in Progress, Pending)

How can i amend this query to bring all completed values only (Complete With change, Complete no change, Complete).

[akika]

ive used like in the conditions .. looks ok thanks a lot