Simple Query Probably / But not simple enough for me lol :)

[bayside blue]

I need a query that looks at a number of fields,

Status 6 achieved... 5... 4... 3... 2... etc.

But it must only count them if the next field is empty and exclude the fields before

i.e.

Status 5 Achieved :
Status 4 Achieved : 10/10/03
Status 3 Achieved : 22/04/03
Status 2 Achieved : 10/03/03

So that this example it would count status 4 but leave Status 3 & 2 out of the count.

At the moment i have all the fields listed in the query. Is there a formula i can use to say only count this if the next field it looks like in the query is blank.

Many thanks,
Martin

 

[miccysoft]

Re: Simple Query Probably / But not simple enough for me lol

you could do it with an if statement

in b1 =if(a1="",1,0)
put that next to it then it would only display a 1 once. then you can count that. either that or a macro.

 

[bayside blue]

Re: Simple Query Probably / But not simple enough for me lol

Would that work in access?? Looks similar to excel?

 

[SydneyGeek]

Re: Simple Query Probably / But not simple enough for me lol

Try this: Create a calculated expression in a query.
Max_Status: IIf([Status 6 Acheived]>0,6,IIf([Status 5 Acheived]>0,5,IIf([Status 4 Acheived]>0,4,IIf([Status 3 Acheived]>0,3,IIf([Status 2 Acheived]>0,2,IIf([Status 1 Acheived]>0,1,0))))))
Then build a second query using this one as the source. Add the Max_Status field TWICE.
Turn it into a Totals query by clicking the Sigma button on the query design toolbar. In the first occurrence of Max_Status, leave the summary as Group By. In the second, change the summary function to Count.

Denis

 

[miccysoft]

Re: Simple Query Probably / But not simple enough for me lol

hadnt realized I was in access nevermind. what he said

 

[bayside blue]

Re: Simple Query Probably / But not simple enough for me lol

Thanks alot

Miccysoft too for the good intention