count

[steve111]

hi ,

I have a query that puts several options in the field called status ( thanks to help from this forum) these are

invoiced, waiting purchasing and parts ordered these are results from an IIF STATEMENT

I have put this query into a form as it looks better

what I would like to do is

1. count all the results in the status field
2 count all the ones that says invoiced
3 count all the ones that says waiting purchasing
4 count all the ones that says parts ordered

I have managed to do the first one =count[status]
but I don't know how to do the others

any help appreciated

steve

 

[CJ_London]

I have put this query into a form as it looks better

It may look better, but it will work better in a query

1. count all the results in the status field

Since there is always a result this will be the same as recordcount

2 count all the ones that says invoiced
3 count all the ones that says waiting purchasing
4 count all the ones that says parts ordered

if you have the result in the form, you need to parse through the form, counting each record where the status is 'invoiced' etc. Depending on the number of records, this can be slow

If the result is in a query then you would use the dcount function e.g.

=dcount("*","myQuery","Status='Invoiced'")

where myQuery is the name of the recordsource to your form (must be a query)

 

[Brianwarnock]

As each Dcount represents a subquery I believe the overhead will be unnecessarily high, just use the sum iif approach

Sum(iif(status="invoiced",1,0))
Repeat in next cols of query for other conditions.

Brian

 

[CJ_London]

@Brian - another way but not sure if the OP is ready for this way yet

 

[steve111]

gents

I have made a mistake but what you did works great but it needs changing I have attached the file for you to see what I have done wrong

this query is grouped as you will see when you look at the results on line 31

I know there is 15 records on that "pco0096" can we change the formula to only count one record

sorry for the problem but as I am new to his I can only se the problem if I comes

thanks steve

 

[CJ_London]

@Brian - over to you

 

[smig]

I would go with Brian's way.

Create a Status table
Working with IDs is faster then strings.

 

[Brianwarnock]

@Brian - over to you

I don't have Access any more so cannot look at the attachment and do not understand the problem, i.e. what he is trying to do.

Brian

 

[steve111]

Hi everyone

I have managed to get the result I want ( I think) I moved the "batch no" from the parts on order to the order header this then only shows once rather than all records per order. just learning access ) but with all your help I am learning slowly

thanks for all you help

steve

 

[steve111]

hi

I have not managed to get it correct because I need to take care of old records that are not on this system

I have created a table called old data

as you can see in the query line 18 and 19 I have added some old data from that table
I know need the status calculation to include

if invoice old is not empty also change status to invoiced
if it is empty but pco purchase old is not empty then status is parts ordered

sorry to be a pain

thanks

steve

 

[steve111]

hi

had to change the data type in the old data table

I have not managed to get it correct because I need to take care of old records that are not on this system

I have created a table called old data

as you can see in the query line 18 and 19 I have added some old data from that table
I know need the status calculation to include

if invoice old is not empty also change status to invoiced
if it is empty but pco purchase old is not empty then status is parts ordered

sorry to be a pain

thanks

steve

 

[steve111]

hi ,
managed to use this to get the results

Status: IIf(Nz([Invoice No],0)>0,"Invoiced",IIf(Nz([invoice old],0)>0,"Invoiced",IIf(Nz([pco No] Or [pco purchased old],"")<>"","parts ordered","Waiting Purchasing")))