Standard Report

[kittyfool via AccessMonster.com]

hi Gurus,

I got a report which i wish to have a standardise format for all my sales
staff. For example, my sales staff sells toyota cars, the report will have
toyota and the number of cars sold. But if sales staff B does not sell any
toyota car for that month, i still want the category Toyota to appear in that
report.

How can i do that? because what i have now is if there is sales for that
model then the model will appear. if there is no sales for that model in that
month, the model will not appear.

Help is much appreciated.

 

[Guest]

You need a table or query that has all of the unique values you want to
display in your report. Add this table/query to your report's record source
query and use a join the includes all of the records from the unique values
table.

 

[Rob Parker]

To do this, you need a way to introduce all the car types into your
recordset. There are probably other ways, but a simple one is to create a
separate table (tblCarTypes) containing all the car types you sell. It
needs only a single field, with a record for each car type. Another
possible way to do this would be to build a Select Distinct query which
pulls car types from an existing table.

Include this new table (or query) in the query that your report is based on,
with a left (or right - depending on your actual query design) join to the
spot field in your existing table - in the query design grid, right click
the
link, then select "Show all records from tblCarTypes and ..." in the Join
Properties tab. If you have other joins in your existing query, and you get
an "ambiguous join" message when you try to run the query, you might need to
re-arrange things to get all joins flowing in the same direction.

Use the CarType field from this table in your report to ensure that every
car type appears.

HTH,

Rob