Creating "dummy" rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I created a query which give me the following sales result:
Customer Product Year Jan Feb Mar etc
Apple nuts 2003 10 13 18
Apple nuts 2005 20 18 13
Apple bolts 2004 3 2 3
Apple bolts 2005 4 2 3

To make it more readable, I want the year in which no sale was recorded to
also appear so I get this:
Customer Product Year Jan Feb Mar etc
Apple nuts 2003 10 13 18
Apple nuts 2004
Apple nuts 2005 20 18 13
Apple bolts 2003
Apple bolts 2004 3 2 3
Apple bolts 2005 4 2 3

How can I best to this? Is it a programming job for VBA?
Thx
Bon
 
The numbers have to come from somewhere, so create a table that contains the
years. One Number type field named (say) TheYear, marked as primary key.
Save as tblYear.

Presumably you want every possible combination of customer, product, and
year. If so, create a query that contains these 3 tables *without* any line
joining them in the upper pane of the query design window (called a
Cartesian Product.) Save this query.

You can now create a crosstab query based on this query and your sales
table. In the upper pane of query design, you will have 3 lines joining the
2 tables (on customer, product, and year.) Double-click the join lines.
Access pops up a dialog offering 3 options. Choose the one that says:
All records from TheYear, and any matches from ...
(Known as an outer join.)

The crosstab will now output all combinations, even where there were no
sales.
 
Back
Top