show 0 values at graph using a query (1 Viewer)

[npa3000]

Hello, so i wanna create a query that returns me some recordsets( sum of orders/per week).

After that, i want to make a graph using that data.

The problem is that i want to display in x axis all the weeks of a year(52), but the query returns only the weeks that orders exist. And my task is to show all the weeks, even the orders are 0.

Do i have to change my query code or i have to customize my chart in Access?

 

[theDBguy]

Hi. Welcome to AWF!

I think you'll have to change your query. I think the chart can only use what's available to it.

 

[npa3000]

Hi. Welcome to AWF!

I think you'll have to change your query. I think the chart can only use what's available to it.

Thanks for the quick answer.
Well, my sql code is this

SELECT ORDERS.Week, Sum(ORDERS.TOTAL_ORDERS) AS SumOfTOTAL_ORDERS
FROM ORDERS
WHERE (((ORDERS.CLIENT_ID)=[Forms]![Contact Details]![ID]))
GROUP BY ORDERS.Week;

What do you think i should change?

 

[plog]

You can't create records for which there is none. So, you need to find a datasource that will allow you to report on every week use it along with your existing query in a new query.

Do you have a table--doesn't have to be ORDERS, but could be--that does have every week in it? If not you can make a table with 54 records in it like so:

ReportWeeks
ReportWeek
1
2
3
...

Then you use it and your above query--let's call it sub1--like so:

SELECT ReportWeek, NZ(SumOfTotal_Orders
FROM ReportWeeks
LEFT JOIN sub1 ON ReportWeek=Wek
GROUP BY ReportWeek

That forces every record from your ReportWeeks datasource to appear even if there is no data for it in sub1 because its on the left side of the LEFT JOIN.

 

[arnelgp]

here is a sample.