Access Query Loop Update

Question

I have a table with purchase orders, and all POs could have several entries with different items associated.

PO	PN	Status
1	A	Open
1	B	progress
1	C	Delivered
2	A	Open
3	A	Delivered
3	A	Delivered

From this master data Table, I would like to create a overview table, with just one entry per PO and only one Status information, according certain criteria.

Criteria example:

if one item is in PROGRESS or DELIVERED set PO status as PROGRESS
if one item is in OPEN (But don't exist PROGRESS) set PO Status as OPEN.
if all item is in PROGRESS or DELIVERED or OPEN set PO Status as PROGRESS or DELIVERED or OPEN accordingly.

OVERVIEW TABLE Example:

PO	Status
1	PROGRESS
2	OPEN
3	Delivered

June7 · Accepted Answer · 2023-01-11 22:33:54Z

1

I will offer one approach to consider. Assuming there are only 3 status categories:

Query1:

SELECT OrderDetails.PO, 
Count(OrderDetails.PN) AS CntPN, 
Sum(IIf([Status]="Progress",1,0)) AS CntP, 
Sum(IIf([Status]="Open",1,0)) AS CntO, 
Sum(IIf([Status]="Delivered",1,0)) AS CntD
FROM OrderDetails
GROUP BY OrderDetails.PO;

Query2:

SELECT Query1.PO, Switch([CntP]=[CntPN],"Progress", [CntO]=[CntPN],"Open", [CntD]=[CntPN],"Delivered", CntO>0 AND CntP=0,"Open", CntP>0 Or CntD>0,"Progress", True,Null) AS S
FROM Query1;

Query1 could be a CROSSTAB instead of using IIf() expressions to emulate. CROSSTAB will return Null instead of 0 so would have to deal with that. Then adjust Query2 structure for different field names.

Anything else will probably need a VBA custom function.

edited Jan 11, 2023 at 22:33

answered Jan 11, 2023 at 21:47

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access Query Loop Update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related