Frequency criteria

[zhuang7]

I got the following question about creating queries for frequency use

There are three columns ,i.e. Customer ID , Sales Order Number, Ship Date,

I need to compute the total level of the freqeuency for the past 6 months.

If customer places at least 1 order , indicate 1 (Even though customer may place 5 orders, still indicate as 1)

If customer places zero order for Sep , indicate as 0

Therefore the frequency ranges from 0 - 6 , maximum at 6.

I couldn't figure this out. Please help.

 

[vbaInet]

You have two solutions:

1. Counting using a SELECT query and the Count function:

The main problem is that if there is no transaction for a particular month there will no count entry for that month too. So in order to overcome this you can :

* drop the Customer ID and Sales Order Number fields into the query
* create a Months table (i.e. a Number field from 1 to 12, called MonthID)
* join that to Month([DateField]) in your query
* under the criteria for Ship Date

BETWEEN Month(Date()) AND DateAdd("m", -6, Date())

* untick the Show checkbox for Ship Date
* right click the Criteria row and select Totals
* change Sales Order Number to IIF(Count([Sales Order Number])<>0, 1, 0) and change Group By to Expression (in the Total row)

2. Use a crosstab query:

This is your easiest option. You will need to change the PIVOT line so that it displays fixed months from Jan to Dec, then apply the filters as explained above and use the IIF(Count()) code too.

 

[zhuang7]

What does it mean by "join that to Month([DateField]) in your query". Please explain.

 

[gemma-the-husky]

what vba is saying, is that you need a separate query or table, that JUST lists all your months, and you need to include this in your final query.

so that if the actual data query does not return anything, you will still get to see the zero against that month.

It's a matter of playing around until you get it working.

 

[vbaInet]

what vba is saying, is that you need a separate query or table, that JUST lists all your months, and you need to include this in your final query.

so that if the actual data query does not return anything, you will still get to see the zero against that month.

It's a matter of playing around until you get it working.

... and I forgot to mention that you will need an OUTTER JOIN to this Months table. Include ALL from the Months table.

Month([DateField]) is a calculation you will put in your initial query and the field you will join to the MonthID field in your Months table.