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?
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?
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;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.