Need Access to show data column when no data results present

I am using a simple crosstab query in Access 2013 showing a sales region with the sales amount in each category. On any given day they may or maybe sale a certain item. I would like my query to still show the sales category but with a $0.00 or even a blank.

In my screenshots on Monday they sold bananas and strawberries so it of course shows them. On Tuesday they did not sales bananas or strawberries but I would like to still see the column headers that say 'bananas' 'strawberries'.

Can this be done?

screenshot 1screenshot 2
TRANSFORM Sum(dds_Daily_MOP_Tb.MOP_Amount) AS SumOfMOP_Amount
SELECT store.region
FROM store INNER JOIN (mop INNER JOIN dds_Daily_MOP_Tb ON mop.mop = dds_Daily_MOP_Tb.MOP) ON store.STORE = dds_Daily_MOP_Tb.Store_Number
WHERE (((dds_Daily_MOP_Tb.Operations_Day)=#10/28/2015#))
GROUP BY store.region
PIVOT mop.number_name;

Open in new window

Cole100IT Systems ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:

but you must define the columns in the Pivot statement, similar to:

PIVOT mop.number_name IN("apples", "bananas",  "lemons", "apples")

Note that you must declare all of the categories in that IN statement which you want to display.  If they are not in that list, they will not show up.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
There are two ways to do this with a crosstab.
1. Add column headers.  This method only works if you have a small, unchanging set of columns.  So if you were always doing Jan-Dec, that would be fine.  If you wanted to use "Apple", "Pear", "Peach" that would be fine but if the recordset contained "Banana", it would not show since you didn't include "Banana" as a column header so this method can be dangerous.
2. Join to the table that defines the values using a left join so that you'll get a value even if you have no data.  So if you wanted all departments but departments didn't always have a quantity, then left joining to the department table would get you all the departments.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.