SQL for Report Graphs

[martin DH]

I have a report with graphs, and this report retreives data from a table that is specified by a where-string-generating form (Access 2003).

My problem: the graphs are based on the criteria entered into the form including month and year. The graphs currently only represent the month and year of the last record displayed in the report.

For example, if the report includes records that have months of June, July, and August, the graphs only represent the August records.

The Row Source sql for the graph is as follows:
[CODE=sql]SELECT [NAC_exp],Count([NAC_exp]) AS [CountOfNAC_exp] FROM [COMPILE_HIST] WHERE NAC LIKE Reports![Quick Report]!NAC AND Month LIKE Reports![Quick Report]!Month AND Year LIKE Reports![Quick Report]!Year AND MarketID LIKE Reports![Quick Report]!MarketID GROUP BY [NAC_exp];[/CODE]

Thank you for any help!
martin

[nico5038]

When you need the graphs per month, then use a grouping per month and the graph will show the monthly figures.

Another solution when you want "aal-in-one" is to use the report's query for the graph too.

Nic;o)

[martin DH]

When you need the graphs per month, then use a grouping per month and the graph will show the monthly figures.

Another solution when you want "aal-in-one" is to use the report's query for the graph too.

Nic;o)

I see what you are saying. What I would like however is that if the report contains only August records, the graphs represent the month of August information. If the report contains records for April, August, and December, the graphs represent all three months' information in one graph.

This may be what you are referring to as "all-in-one" but I'm not sure what to do here. The report actually has no query. There is a form that, when supplied with criteria, creates a where string and opens the report with these criteria.

So, do you think there is a way to allow the graphs this flexibility? Thanks.

martin

[nico5038]

Open the report in designmode and directly look into the (Report) properties.
Under the Data tab the form's controlsource will be specified. When this is an SQL string, just place the cursor in the field and press the [...] button at the end.
The query editor will open and now you can use the Save option from the menu to name the query e.g. "qryReportX ". Closing the query will trigger Access to ask a confirmation. Accept this and see that the repot's SQL has been changed into the qryReportX you specified. Finally base the graph on the same query.

Clear ?

Nic;o)

[martin DH]

I think I see what you are saying. You are saying that after the report has been generated, save the Where string used as a new query and set the graphs to go off of that particular query, correct?
If I understand properly, I don't think that will work because there is the possibility for millions of different Where strings (dates are involved). I wouldn't want the user to have to save a new query each time a report is generated with different criteria (always).
Anything else I can let you know to help?
martin

[nico5038]

Incorrect, my proposal is to build the report based on a query (can already be the case, but it could also be a table or a SELECT string).
Now use this query also for the graph.

Nic;o)