You need to introduce the missing years and months into the database
by creating two tables, Years and Months, the former with a column
YearNumber and values 2008, 2009, 2010 etc as far ahead as you want to
cater for, the latter with a column MonthNumber with values from 1 to
12.
You also need a table Models with column Model and values Toyota,
Honda etc. This table is not just a required for your present
problem, but is a fundamental requirement of your database's logical
model as without it referential integrity can't be enforced.
With these tables you no longer need to use the IIf function to
conditionally aggregate the values as you can group by Model. The
query for this is a little complex as you need to firstly join Years ,
Months and Models to return the Cartesian product of these tables,
i.e. one row for every possible year/month/model and then join this to
your main table, which I've assumed for this example to be a table
Sales with columns SaleDate and Model. So first create a query as
follows:
SELECT YearNumber, MonthNumber, Model
FROM Years, Months, Models;
and save it as ymm.
You need to group the final query by the year, month and model
returned by the ymm query so that all models are included for every
year/month whether a sale has been made or not, but count the non-Null
Model column positions in Sales so as to count only these where a sale
has been made. The database may not be of sales at all of course, but
that's not relevant, it’s the principle which matters. So the query
is like this:
SELECT YearNumber, MonthNumber, ymm.Model,
COUNT(Sales.Model) AS NumberOfSales
FROM ymm LEFT JOIN Sales
ON ymm.Model=Sales.Model
AND ymm.MonthNumber=MONTH(Sales.SaleDate)
AND ymm.YearNumber=YEAR(Sales.SaleDate)
GROUP BY YearNumber, MonthNumber, ymm.Model;
You'll have noticed that Stefan queried the use of multiple forms. In
fact you only need one form, which should be in continuous form view
and bound to this query, just open it filtered to the year and month
in question if you want to return the counts for just one month. Evan
better would be to include two unbound combo boxes in the form header,
one for years and one for months and use these to filter the form.
That way you'll be able to switch between any year and/or month while
the form is still open simply by selecting from the combo boxes.
Ken Sheridan
Stafford, England
Hi, I have the following code below which is counting instances and
displaying the results in text boxes (6 in total) on a form, each month has a
form of its own so when i open the form the query looks between the fixed
dates of the month start and month end, but say I open May 2009 which of
course contains no data yet, the form just appears as a white blank screen,
it works ok as soon as I enter data into the table for the month of May, but
I would like to be able to open any form to view it even if the text boxes
just display zero's - anything I can add to the code or VB to do this ??.
Thanks
Model: Count(IIf([Model] Like '*Toyota*',1,Null))
Model: Count(IIf([Model] Like '*Honda*',1,Null))
