Access Report needs to report zero values

[reportings]

I have an Access report that show MTD and YTD sales for a product. When there are over 0 sales for the month it works fine and shows both MTD and YTD. However, if there are not MTD sales, then i lose the row of information for that product including the YTD which has an actual sales volume. I am not sure how/where to change this. Is it on one of the tables, queries or on the report itself? Thanks.

 

[Micron]

What is the report based on - query or table? If query, what does it show in a record with no MTD sales? If that looks OK (you get a YTD figure), the issue is probably with the report; i.e. you may need to have section groups for YTD and MTD with textboxes in each section footer to sum the values. MANY ways to 'skin this cat'.
1) records are only for MTD values, the YTD values are calculated on the report in the detail footer.
2) textbox in detail footer has a sql statement for its source that gets the Sum of the product sales YTD
3) you create a new query that returns both value sets and build a new report using the wizard to see how it responds to your answers regarding grouping and totals. You don't have to use this report, but it would help you design the other.
4) you have a subreport in the detail footer to show the YTD values. I think but am not positive you can place a subreport in a detail footer.
Some of these I might not have suggested if I knew more about what you have.

 

[reportings]

Thanks Micron. The report is based on queries. The data is filtered to only look at a certain sales region. There is no data for MTD sales so the report is not showing the row how I need like:
MTD YTD
Apples 0 24

It omits Apples altogether. There are currently textboxes for MTD and YTD seperately in the "Detail" of the Access report.
I was hoping there was some sort of toggle somewhere to not supress zero values in either the report or query.

What is the report based on - query or table? If query, what does it show in a record with no MTD sales? If that looks OK (you get a YTD figure), the issue is probably with the report; i.e. you may need to have section groups for YTD and MTD with textboxes in each section footer to sum the values. MANY ways to 'skin this cat'.
1) records are only for MTD values, the YTD values are calculated on the report in the detail footer.
2) textbox in detail footer has a sql statement for its source that gets the Sum of the product sales YTD
3) you create a new query that returns both value sets and build a new report using the wizard to see how it responds to your answers regarding grouping and totals. You don't have to use this report, but it would help you design the other.
4) you have a subreport in the detail footer to show the YTD values. I think but am not positive you can place a subreport in a detail footer.
Some of these I might not have suggested if I knew more about what you have.

 

[Micron]

No such thing as a toggle for no data. You said 'queries' not 'query' - one report can only be based on one query AFAIK, so not sure what you have there. Based on limited understanding of your project, I'd say you need to modify the query to include something else when there is no MTD sales for a product so that it returns a record regardless, or check out if a Totals query will help. Or evaluate some of the other suggestions from my first post.

Failing that, anyone in this forum that may want to chime in would probably agree that we'd need more info (report query sql, an image of the report in design view) for starters. You always have the option of posting a stripped down version of your db file somewhere. Unfortunately, I cannot open anything newer than 2007.

 

[reportings]

Thanks. I am ok for this month as there is one unit now for MTD peaches.

Here is the SQL:
SELECT [Daily Report base table].order, [Daily Report base table].BU, [Daily Report base table].[06 Retail], [Daily Report base table].[07 Retail], [Daily Report base table].[Current YTD BP], [Daily Report base table].[07 vs 06 Unit Diff], [Daily Report base table].[07 vs 06 % Diff], [Daily Report base table].[07 Actual vs BP Units], [Daily Report base table].[07 vs BP %], [Daily Report base table].[Prior Month Retail], [Daily Report base table].[Current MTD Retail], [Daily Report base table].[Current MTD BP], [Daily Report base table].[Current vs Prior Mth Ret Units], [Daily Report base table].[Curr Mth vs Prior %], [Daily Report base table].[Current Mth vs BP Units], [Daily Report base table].[Curr Mth vs BP %], [70A BP by BU Totals].[FY BP], [Daily Report base table]![07 Retail]/[70A BP by BU Totals]![FY BP] AS [FYTD vs FY BP]

FROM [Daily Report base table] INNER JOIN [70A BP by BU Totals] ON [Daily Report base table].BU = [70A BP by BU Totals].BU

GROUP BY [Daily Report base table].order, [Daily Report base table].BU, [Daily Report base table].[06 Retail], [Daily Report base table].[07 Retail], [Daily Report base table].[Current YTD BP], [Daily Report base table].[07 vs 06 Unit Diff], [Daily Report base table].[07 vs 06 % Diff], [Daily Report base table].[07 Actual vs BP Units], [Daily Report base table].[07 vs BP %], [Daily Report base table].[Prior Month Retail], [Daily Report base table].[Current MTD Retail], [Daily Report base table].[Current MTD BP], [Daily Report base table].[Current vs Prior Mth Ret Units], [Daily Report base table].[Curr Mth vs Prior %], [Daily Report base table].[Current Mth vs BP Units], [Daily Report base table].[Curr Mth vs BP %], [70A BP by BU Totals].[FY BP]

ORDER BY [Daily Report base table].order;

No such thing as a toggle for no data. You said 'queries' not 'query' - one report can only be based on one query AFAIK, so not sure what you have there. Based on limited understanding of your project, I'd say you need to modify the query to include something else when there is no MTD sales for a product so that it returns a record regardless, or check out if a Totals query will help. Or evaluate some of the other suggestions from my first post.

Failing that, anyone in this forum that may want to chime in would probably agree that we'd need more info (report query sql, an image of the report in design view) for starters. You always have the option of posting a stripped down version of your db file somewhere. Unfortunately, I cannot open anything newer than 2007.