[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2003 Help with a Graph

Posted on 2012-08-27
14
Medium Priority
?
211 Views
Last Modified: 2013-05-21
I have a report called Test_RPT that is based on a table called test_tbl. The table is created via a query called calculation_qry. The query has criteria of  Between [start date] And [end date].
I thought I could originally tie this query as a select but my chart bombs which is why I went with the create table route as I don't know how to create a graph based on a query with criteria. If there is a better way please let me know as I am a novice at this (also attached) db.
The test_rpt runs correctly however I cannot seem to get the date included on the graph. Can someone help me as to how I can include the dates on my graph.
Safety-new-2x.mdb
0
Comment
Question by:tmaususer
  • 7
  • 4
  • 3
14 Comments
 
LVL 3

Expert Comment

by:DJNafey
ID: 38338001
Hi,

I have just had a quick look at your database but it's a very long time since I've made reports in Access and I couldn't quickly re-create the chart in the same layout as you have it.  However, I could make it run from a SELECT query and get it to prompt me to enter the dates.  To do this, I ran the query builder, select your Test_tbl, dragged in all of the fields and then used a "Group By" on the Date field.  In the 'Criteria', I entered

Between [Start Date] And [End Date]

Does that help at all?
0
 

Author Comment

by:tmaususer
ID: 38338039
Not sure I follow since I am a novice at this. Sorry....
0
 

Author Comment

by:tmaususer
ID: 38338175
I guess the best way I can put it is I have a report called report2 in my db that works great. I basically need something like that but in graph format. Does that make sense?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:tmaususer
ID: 38338180
Sorry forgot to mention if you launch report2 you can use start and end dates of 3/1/12 and 3/31/12.
0
 
LVL 3

Expert Comment

by:DJNafey
ID: 38338271
Oh OK - I think I was only looking at Test_RPT but, if you're already getting date filters working in Report2, then it sounds like you're most of the way there.

I'm no in front of my PC at the moment but can probably get another look at it later if no-one else helps before then.

How are you trying to make the report with the chart? I did it by creating a report in design view, adding a chart, then going into the Properties of the chart, going to the RecordSource value, clicking the 'build' button to run the query builder wizard, then creating a a query based on your table (and with the date filtering statement typed into the Criteria field) to use as the 'source' for the chart. Actually, it wasn't exactly like that because I'm on Access 2010 but that's what I think you should be doing in Access 2003.

If you did it a different way, try following my steps and see how far you get :-)
0
 

Author Comment

by:tmaususer
ID: 38338321
I used the report/chart wizard but when I added my query in which I have set criteria it gives me an error which is why I created a make table and based my graph on the new table. The problem I am having is I cannot create a graph based on dates. Sorry if my explaination is not that great but I think once you can look at my db you will see my issue.
0
 
LVL 3

Expert Comment

by:DJNafey
ID: 38339005
Sorry, I've just read your original post again and the very first thing that you said that you did was what I was suggesting.  You might have done it a different way to me, being on Access 2003 instead of 2010, but it sounds like it should have worked.

I'm going to have another look at it now.
0
 
LVL 3

Expert Comment

by:DJNafey
ID: 38339113
I've just been looking at it for another hour but I can't see how to get a date filter working directly from the report either.

Report2 doesn't run when I try it here in Access 2010.  

Is there anyone else out there who could assist?
0
 

Author Comment

by:tmaususer
ID: 38340782
I'm increasing the points in the hopes someone can help. Basically I want to create a chart based on date parameters from a query. Similiar to report2 in my sample db but representing that data in chart format.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38341919
What is a typical start and end date range?
Post a graphic of the *EXACT* chart you want based on that date range...
Don't describe what you want, ....post a Picture of exactly what you want...
0
 

Author Comment

by:tmaususer
ID: 38342452
A typical date range would be weekly and monthly. I have attached an example per your request.
Would-result-in.doc
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 38343282
Oh, OK, ...you want to add the date range to the chart...


Note that you have a lot of inefficient design aspects in your database here.
(un-normalized tables, Spaces on object names, Lookups in your tables, using Value Lists instead of tables, using Reserved words as Field names...etc)

I strongly suggest that hold off on any more work on this and post questions about your table designs and relationships...

These inefficiencies are why you are having so much trouble doing what you need to do...
These issue will haunt you forever...

sample attached...
Access-EEQ27843759ChartGraphDate.mdb
0
 

Author Comment

by:tmaususer
ID: 38343637
When I hit the view button I receive an error (see attached).
error.png
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39184473
In Access 2003 this may need to be:
acPrintPreview
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question