Creating Graphs in Reports

[katejohnson]

Warning: First time post and self-taught Access user.



I have come to a point where I am looking to create a few simple graphs to insert into a report and I can not seem to figure out what I'm doing wrong.

The process I've been using is:
Blank Report>Design View> Add chart control>Choose Query for data pull>Add 2 fields (ex. Week Ending and Sales)>pick line chart

...and then non of the choices for the Y axis match up to the range that I am looking for.

My data is arranged by Week Ending and I am looking to make 4 different point charts to show:
Sales/Week
Orders/Week
Conversion Rate/Week
Total Affiliates and Active Affiliates/Week

I want the range of weeks to be grouped by month and to span the last year.

Help!

Thanks!

[rpeare]

Any chance you could upload a sample copy of your database? Just make a copy of yours, take out any private info, put in some junk data that will show what you're after then compact/repair, zip the file and upload it to the site. Without seeing the query running your graph it'll be hard for me to help.

[katejohnson]

Any chance you could upload a sample copy of your database? Just make a copy of yours, take out any private info, put in some junk data that will show what you're after then compact/repair, zip the file and upload it to the site. Without seeing the query running your graph it'll be hard for me to help.

I attached the database, hopefully this helps.

This database is the same for the other question on this board, as well, regarding month over month calculations from a [week ending] field.

[rpeare]

So the query SAMPLE QUERY 1 is just reporting on the table you have in there I'm assuming you've run an aggregate query, and just cut and paste that into your database for the sake of example.

I see what your problem is, you're trying to look at the PREVIEW before you finish the product, what I would suggest instead is this:

Create a graph, include the WEEK ENDING and TOTAL $ SALES fields (by the way, you shouldn't ever use special characters in ANY of your object names in ms access, it can really screw things up with your coding)

Just accept whatever it tells you, select your line graph then accept everything and let it create it.

The graph wizard is a joke so when it's done creating go to the ROW SOURCE property of the graph and look at the query it built, it's trying to make a crosstab which you don't want so put in this code:

SELECT [Sample Query 1].[Week Ending], Sum([Sample Query 1].[Total $ Sales]) AS [Net Sales] FROM [Sample Query 1] GROUP BY [Sample Query 1].[Week Ending] HAVING ((([Sample Query 1].[Week Ending]) Between [Enter Start Date] And [Enter End Date]));

Then you should have a line graph of total sales by week ending.