By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,385 Members | 847 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,385 IT Pros & Developers. It's quick & easy.

SQL for Report Graphs

100+
P: 114
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:
Expand|Select|Wrap|Line Numbers
  1. 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];
Thank you for any help!
martin
Oct 24 '07 #1
Share this Question
Share on Google+
5 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Oct 25 '07 #2

100+
P: 114
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
Oct 25 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 25 '07 #4

100+
P: 114
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
Oct 25 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 25 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.