finding the customers who are in between stages

[akapag22]

Hi Friends,

I have a table Customer as shown in the following way:

CustName Stages DOE

A 1 06/12/2015
B 1 06/12/2015
A 2 06/12/2015
A 3 06/13/2015


A 1 07/12/2015
C 1 07/12/2015
C 2 08/12/2015

Now i need to know customer who have passed stage 1 but have not reached the stage 3.
Though the A has reached stage 3, it gets started again and so is considered still at 1.
Similarly with C , its at stage 2 and not yet reached 3 so it should show up in the result

So my result should be A, B,C as all have stage 1 but not stage 3 ...how can i get it done now..

Thank You

[June7]

Try a nested query that returns the latest record for each customer and apply filter criteria to the Stages field: <3.

Table needs a unique identifier field, autonumber will serve.

The TOP N operator can return the latest record for each customer. Review: http://allenbrowne.com/subquery-01.html#TopN

SELECT Customer.ID, Customer.CustName, Customer.Stages, Customer.DOE
FROM Customer
WHERE (((Customer.Stages)<3) AND ((Customer.[ID]) In (
SELECT TOP 1 ID
FROM Customer AS Dupe
WHERE Dupe.CustName = Customer.CustName
ORDER BY Dupe.DOE DESC, Dupe.ID DESC)));

[akapag22]

HI June7,

Thank You much.

But the stages are not stored in order. I used them for example sake.


new example

CustName Stages DOE

A stage1 06/12/2015
B stage1 06/12/2015
A stage2 06/12/2015
A stage3 06/13/2015
A stage 1 07/12/2015
C stage1 07/12/2015
C stage2 08/12/2015

How can i do in this scenario... Basically if the customer reached stage 1, i need to check if he has passed stage3 else show them in the result..Though it has autonumber field attched..they are not in order.

[June7]

I don't understand. Can a customer have stage 3 without stage 1 and 2? How can stages not be accomplished in chronological order? Is DOE the date stage was reached?

[akapag22]

Yes June They can do that even though they should not..Thats the reason i need these kind of reports to make sure to catch any missing stage in the chronology. yes DOE is the date stage is reached. Thank You

[June7]

That gets complicated. Say a customer went through stages 1, 2, 3 and so is eligible to start over but somehow next record entered is a stage 2 or 3. Do you really want to allow this? This can be prevented with data validation at the time record is input.

A query could return a count of each stage for each customer. If there are more stage 2 or stage 3 than stage 1, then something is out of wack.

A CROSSTAB query could generate the counts.

Or emulate a CROSSTAB with expressions:

SELECT CustName, Sum(IIf([Stage]=1,1,0)) AS CountStage1, Sum(IIf([Stage]=2,1,0)) AS CountStage2, Sum(IIf([Stage]=3,1,0)) AS CountStage3 FROM Customer GROUP BY CustName;

[akapag22]

Thank You June.. I checked with my superiors and they said we proceed as they would go in the chronological order.