Problem with Reports and charts not synchronizing correctly

[aw_wolfe]

I am having a problem with charts and reports not synchronizing. So I created a test table to see what I am doing wrong, but still seeing the same issue.

Basically, I have a value I want to graph (Amount) over time for each OfficeName grouped by Region.

So on the report, I group by Region, OfficeName and place the chart into the OfficeName header section. In the chart wizard, I set "report" OfficeName= "chart" OfficeName...note, I've tried using the OfficeID as well to no change.
In the screenshot, you can see that the chart is not trying to graph the correct data for the given Office. I used a pivot in the test chart (OfficeName), but will have to remove as in the real data I want to graph 3 values (Amount, bid, ask). Result is the same without the pivot.

For example: The Knoxville Office should have 3 data points and the chart only shows 2 (but are also from the wrong office---showing the Nashville Office name and dataset).

I'm not sure what I'm doing wrong, this should be a straight forward task. Using the chart wizard to generate the query and 'map' the OfficeName in the report to the OfficeName in the data (or trying to).





simple table export:

"ID","Region","OfficeName","DateTrial","Amount","O fficeID"
1,"East","Chicago",1/1/2017 0:00:00,$10.00,1
2,"East","Chicago",2/4/2017 0:00:00,$5.00,1
3,"East","Chicago",5/3/2017 0:00:00,$20.00,1


4,"East","Vernon Hills",6/3/2017 0:00:00,$200.00,2
5,"West","Englewood",1/1/2017 0:00:00,$15.00,3
6,"West","Englewood",2/3/2017 0:00:00,$40.00,3
7,"Central","Madison",8/3/2017 0:00:00,$16.00,4
8,"Central","Madison",12/30/2017 0:00:00,$205.00,4
9,"Central","Madison",8/3/2017 0:00:00,$117.00,4
10,"Central","Knoxville",3/15/2017 0:00:00,$57.00,5
11,"Central","Knoxville",6/23/2017 0:00:00,$48.00,5
12,"Central","Knoxville",9/1/2017 0:00:00,$25.00,5
13,"Central","Nashville",3/2/2017 0:00:00,$49.00,6
14,"Central","Nashville",1/1/2017 0:00:00,$58.00,6
15,"Central","Molene",1/1/2017 0:00:00,$38.00,7
16,"Central","Molene",3/2/2017 0:00:00,$35.00,7

[isladogs]

Create a query based on your table filtered by office ID.
CHECK IT GIVES THE RESULTS YOU WANT.
Then use the query as the data source for your chart.

[aw_wolfe]

Yes, for example if I manually do a "SELECT testTable.Amount FROM testTable WHERE testTable.OfficeID=1;" it returns correctly. Note: this is from the sql command under query builder...not from the report or chart.

So, again, seems like some disconnect between the report and the graphs or a lack in understanding in how this is supposed to be able to work.

[isladogs]

Try this in your chart record source

"SELECT testTable.Amount FROM testTable WHERE testTable.OfficeID= " & Me.ID & ";"

or possibly

"SELECT testTable.Amount FROM testTable WHERE testTable.OfficeID= " & !Forms!MyFormName.ID & ";"

substituting the name of your form

If neither works, you may well be setting up your chart incorrectly or using an inappropriate chart type for your data

[aw_wolfe]

The chart is a line chart. So should be alright for data.
On the report, I manually put in a row source.

SELECT ID,[testTable].Amount FROM testTable WHERE [testTable].OfficeID=[Reports].[testTable].[OfficeID];

I also put in a field with control source (=[Reports].[testTable].[OfficeID]) to verify that value is being correctly filled in, in the Office header section of the Report (it is).

However, still seeing the same thing, where the data is not lining up....In the Knoxville Office header, all fields are correct, except the chart is filled with the Nashville data.

If I go into query builder and type the query...it prompts me for the OfficeID and then correctly returns the correct rows.

Thoughts on what I need/should check. Driving me crazy, seems like I'm missing something on the chart/Report relationship or how it handles data.

I created a new db with same issue and attached if you have to time to actually open it and see directly.

[isladogs]

I've not had time to look at your database.
Yes you are missing something
As it's a SQL statement you NEED the number delimiters
Look at the code on the right side of the equal signs in the code I gave you.
Because you left them out, Access can't read the ID values which is why it prompts you.

Add these and try again ... then report back!

[aw_wolfe]

I am using Access 2013 on Windows 10. --not sure if different version issue.
From the Report Form, clicking on the properties of the chart, and manually filling in the "row source" column of the chart properties....
quotes do not work -- will not produce a working chart on the report. So using the ending the string with quotes and using & isn't the issue. Further, it does NOT prompt me for the field from the reports page. I only get a field prompt when running the query from the QUERY builder/form (as expected, since undefined field...from the query builder, but is defined when running from the Reports Form--so prompt does not happen then)....never from the Report page.

Thanks for the time you've already given me. I hope that I am explaining well enough so that you understand my issue (maybe I'm not). If you get around to being able to open the db I attached and find something, I'd really appreciate it. Should be straight forward issue once you look at it.

[isladogs]

See attached
I created 2 new queries: qryTotalAmountByOffice & qryTotalAmountByOffice&Date
The report rptTotalAmountByOffice&Date based on the second query should be what you want

I've left the layout similar to that in your original report

HTH

[aw_wolfe]

Thank you and again, thanks for your time. However, if the charts are synching correctly on your computer ( i.e. correct data for each office is being charted in the Office group on the report), then something is wrong with my computer/Access or something, because I am still seeing the same issue. I.E. the chart for the first OfficeName Knoxville is not displaying the correct data (in fact it is showing Nashville).

[isladogs]

Oops - sorry I uploaded the wrong version
Attached version should be correct.
The report is now grouped by Office ID instead of Office Name

[aw_wolfe]

Ok. So I opened the new one and the same issue. NOT synchronized. I'm feeling very frustrated. So, I am going to take a screenshot and post it back to show that on my computer, the report is still showing the wrong data. In order to include both pages, I go into print preview (like ridders52 had done) and low an behold the data in the chart is correct. I close print preview and go to normal view and data is still incorrect. Print preview= data correct. This seems to be a bug with multiple versions of Access (just tried from an old 2007 student version I had on old computer and am seeing the same pattern). I don't see any reason why the data shouldn't be correct/current during a normal report viewing---while only getting updated correctly during a print preview. If there is a reason I can't think of one.

[isladogs]

Hi

I never use Report view or Layout view as I don't find either useful ... so it never occurred to me to check those
I can confirm that the charts are correct in Print Preview (as shown in my last post) but incorrect in both of the other views.

I can only assume that the other 2 views are using sample data rather than the actual data in your case
I've tried tweaking it without success.

A google search came up with this...
https://social.msdn.microsoft.com/Fo...orum=accessdev

So yours isn't a unique case
However, I've looked at other similar charts on my own databases & don't have the same issue
For now, I'm out of ideas...

Suggest that you use Print Preview as your default (set this in the report property sheet)