Crosstab query needs to display empty cells

[67159]

I may not be stating this correctly, but here's my problem. I run a query on demand for a sales promo. I have a table with 73 customer account numbers and names along with the account's salesperson number and name.

I need the crosstab query to return sales dollars and also display zero or nothing if nothing has been sold to that customer.

So far all I get is anything with a sale. Does this make sense.

This is what I'm aiming to get, currently I'm entering all the zeros in the spreadsheet. Help!

Account Manager	Customer Name	Total Promo Sales	July	June	May
Region 05 Totals		$6,218.94	$84.24	$4,684.35	$1,450.41
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$126.36	$0.00	$0.00	$126.40
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$97.34	$0.00	$0.00	$97.34
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$90.54	$0.00	$90.54	$0.00
Salesperson Name	Customer Name	$591.10	$0.00	$0.00	$591.10
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$131.82	$0.00	$131.82	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00
Salesperson Name	Customer Name	$384.48	$0.00	$384.48	$0.00
Salesperson Name	Customer Name	$275.50	$0.00	$275.50	$0.00
Salesperson Name	Customer Name	$1,655.28	$0.00	$1,655.30	$0.00
Salesperson Name	Customer Name	$0.00	$0.00	$0.00	$0.00

[CJ_London]

Format the controls - something like

;;;"$0.00"

[67159]

Forgive my ignorance, but I'm not sure what you mean by "controls". I've never used the semicolon in access. What does it mean and do? Thank you for your help. Have a great Friday!

[CJ_London]

controls are used in forms and reports to display your data.

If you are just using a query then open the properties for the value column (select properties from the ribbon or right click and select properties) - you'll find it there

format has 4 'states' - positive, negative, zero and text - the last one also applies to null. The first three only apply to numbers. The semi column is used to separate the different states so access knows which state the format is to be applied to.

so you could format as $0.00;[red]$0.00;;$0.00

which will show negative values in red and nulls as $0.00

or something like this

[Green]$0.00;[red]$0.00;;"nothing found"

or
Currency;[Red]Currency;"-","N/A"

see this link for more information on formatting

https://msdn.microsoft.com/en-us/lib.../gg251755.aspx

find the section 'Different Formats for Different Numeric Values (Format Function)' about halfway down

Although it refers to the format function, the same rules apply for the format property

[67159]

Ajax,

Sorry for the delay. Thank you for your response. I learned something new.
That helps fill in the empty cells where a hit has occurred.
However, I have a table with 73 customers. The rows with no hits during the given times don't show up at all. Any thoughts?

[CJ_London]

you need another table or query which lists all the salespersons names which you then left join to the crosstab query - then where a salesperson is not in the crosstab, you will get nulls for that salesperson

[June7]

Do you want all salespersons or all customers?

Options:

1. a dataset of all possible customer and date combinations joined to the sales data then build crosstab

2. join crosstab to Customers table

[67159]

B-E-A-utiful. God bless you, Ajax! Worked perfectly. Now I have all the accounts and their month by month data. Thank you so much.