Help with report please!!!

[Keetso]

Hi.

I'm have a report based on numerous select queries and one crosstab query.


This report is set up as follows:

Sales Broker Comp A Comp B Comp C Comp D Total

John Chevy 150.00 100.00 250.00
Mary Dodge 80.00 50.00 130.00
Jane Ford 60.00 60.00
Fred Olds 90.00 70.00 30.00 190.00

Totals 240.00 250.00 60.00 80.00 630.00

Each sales broker in our company is listed with any sales they might've had for any of the companies we represent.

My problem is that this report prints fine when doing year-to-date (like above), but what my boss wants now is to be able to generate this report for any given month. This insn't a problem as my tables are set up so I can generate sales data for any given month, but when I generate the report (for, for example, the month of December), ONLY those sales brokers that had any sales for that month is listed. What my boss wants is to have ALL brokers listed whether they had sales for the selected month or not, BUT only print sales amounts for those that did have sales that month. All others can have zeroes placed.

Can anyone point me in the right direction?

TIA

K

 

[dhookom]

You should create a query based on your crosstab and add the table with all your Sales Brokers names. Join the two "tables" with a join that selects all values from the Sales Broker table. If you want to replace null with 0, use the Nz() function.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[teddyatjorundotnet]

Actually, you won't return anything for an agent, not even null. SQL constructs the dataset from the join statement first. If a value does not appear in one table, it is assigned null at that time. The problem you may run into is that if the broker has any sales at all, then they do in fact exist in the primary dataset and will not be assigned null.

The where clause is assigned after the fact. In order to have the brokers return null for a given date range, you will have to include it in the join statement.

something like:

SELECT sales.sum(sales), broker.name
FROM sales INNER JOIN broker ON sales.brokerid=broker.brokerid AND sales.date between sdate and edate

as opposed to:

SELECT sales.sum(sales), broker.name
FROM sales INNER JOIN broker ON sales.brokerid=broker.brokerid
WHERE sales.date between sdate and edate

 

[Keetso]

Hi Duane.

Thanks for the rapid response. I was definately in the right direction. Just needed to work out the joins as you suggested.

Thanks for your help.

K

 

[Keetso]

Hi Teddy.

I answered Duane before I saw your response, but you were right on the mark as well.

For example, I did as Duane suggested and returned all records, but those records that had no sales for the selected month came up with null values for the "value" field in the crosstab but the name fields, address fields, etc were all blank. I had to add tons of ANDs, but it's all working great now.

Thanks again for all the help.

K