Force rows into a query ... 1

[jhaganjr]

Let's say I have a simple sales order table like ...

SaleID Agent SaleDate
1 A1 7/6/07
2 B2 7/25/07
3 A1 8/12/07
4 C3 8/14/07
5 C3 9/6/07
6 A1 9/10/07

And I want to count each agent's sales within the quarter by month. This is easy with a simple grouping query like ...

SELECT Agent, Format([SaleDate],"mmm yy") AS SaleMo, Count(SaleID) AS Sales
FROM [zz test]
GROUP BY Agent, Format([SaleDate],"mmm yy"), Month([SaleDate])
ORDER BY Agent, Month([SaleDate]);

and I get these results ...

Agent SaleMo Sales
A1 Jul 07 1
A1 Aug 07 1
A1 Sep 07 1
B2 Jul 07 1
C3 Aug 07 1
C3 Sep 07 1

So far, so good. But what I really want is the result set to include one row per agent for each month of the quarter whether they had any sales or not, so that my query result would be ...

Agent SaleMo Sales
A1 Jul 07 1
A1 Aug 07 1
A1 Sep 07 1
B2 Jul 07 1
B2 Aug 07 0
B2 Sep 07 0
C3 Jul 07 0
C3 Aug 07 1
C3 Sep 07 1

It seems like this should be simple, but I can't figure out how to "force" a zero row for EACH agent for EACH month in which each agent has no sales.

Does anyone know how to make this happen?

Thanks a million if you do!
Joe

 

[dhookom]

If there are sales in every month, create a cartesian, group by query for each Agent within the Agent table:

  SELECT Agent, Format([SaleDate],"mmm yy") AS SaleMo
  FROM tblAgent, tblSales
  GROUP BY Agent, Format([SaleDate],"mmm yy");

The add this cartesian query to a query with the results of the above query and set the join properties to include all of the records from the cartesian query.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jhaganjr]

That's great, Duane. Thanks! I can tweak that to the reality of my situation.

Now ... what if there aren't sales in every month? In that case the cartesian query cannot include that month, and so I will have a missing month for each agent instead of a zero month for each agent.

In reality I'll be running this on a quarterly basis for specific kinds of sales. So, there may not be sales in each month.

The only thing I can think of in this case is to use code to append records to a table for each agent and each month in the selected quarter (which would be specified on a form). This would ensure that every month of the quarter is included whether there were sales or not. Then use that table as the LEFT JOIN with my grouping query.

Do you see a more direct solution than this when there aren't sales in each month?

Thanks again!
Joe

 

[dhookom]

I generally keep a table of numbers in most applications. I can then use this table to create dates or whatever. Assume [tblNums] with a single numeric field [Num] and values at least through 12. To get every month in 2007:

SELECT Format(DateSerial([Yr]![Num]+2000,[Mth]![Num],1),"mmm yy") AS MthYr
FROM tblNums AS Yr, tblNums AS Mth
WHERE (((Yr.Num)=7) AND ((Mth.Num) Between 1 And 12));

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JerryKlmns]

Joe,

Although Duane's method is unquestionable, you could define the months in the query's property sheet at Column Headings. The logic could apply to values that only these are needed. Like if you had 10 users and you need to show data only for some of them whether there is something to show or not.

 

[dhookom]

JerryKlmns,
I'm not sure the final desired result is a crosstab query. If it was a crosstab then your suggestion of specifying column headings is a simple, efficient solution.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jhaganjr]

Thanks Jerry,

As it turns out, when I took Duane's ideas to the real situation I realized that there will always be some kind of sale in any month. Therefore, the cartesian query that sets up the agent and month combinations does not filter for type of sale. That way I'm guaranteed to always have a complete list of agents and months for a given quarter.

I only filter for type of sale in the other query that pulls the actual data I want.

So, my final results are exactly what I need and will pull correctly for any given time frame.

Thanks again!