Query not giving exact result

[mobile2]

I have a query that has the following criteria for the invoice_date field ">#30/04/04#". This shows me what customers have spent after April. I have also asked it to show me who has not spent after April by putting "Is null" in "or:". I also want to know customers who have not spent after April but may have spent previously. I have tried entering the following expression to show this but have been unsuccessful "null >=30/4/4". I know there is a company who spent in March but not in April but can't think how to show this. Please can you help?

 

[lupins46]

I have a query that has the following criteria for the invoice_date field ">#30/04/04#". This shows me what customers have spent after April. "

Yes that's fine.

"I have also asked it to show me who has not spent after April by putting "Is null" in "or:". "

No. Assuming we are looking at a table of invoices here, then this will show customers who have never been invoiced, not those who have no April invoice.

"I also want to know customers who have not spent after April but may have spent previously. "

This is much more complex.
You would need to create multiple queries to achieve this.

You could get an overall summary of invoicing patterns by using a group query.

Start a new query and select the customerid.
In the next column you need an expression like this:
aprInv:IIf([invoicedate] Between #01/04/2004# And #30/04/2004#,1,0)

In the next column:
MarInv:IIf([invoicedate] Between #01/03/2004# And #31/03/2004#,1,0)

Now convert to a groups query by clicking the toolbar button. Change the GroupBy for columns 2 & 3 to Count.

You need to use your own fieldnames, obviously, for customerid and invoicedate.

If you run this query it will summarise the situation and you can set criteria for columns 1 and 2 if you want.

 

[mobile2]

Thanks for the advice, when you talk about converting to a groups query do you mean selecting "Totals" from the "View" menu? I'm using MS Access 97.

 

[lupins46]

Yes.