Auto-Update Data

[PCHomepage]

A whlie back I created a database in Access 2007 and added several pie-chart reports to provide statistics. It appears, though, that the reports are locked into the data from the date I created them. Is there a way to make them update themselves each time they are opened?

 

[dhookom]

The chart controls have a row source that should be a table, query, or SQL statement. These are typically requeried whenever the report is run. I'm not sure how you would even be able to lock in the data unless you created a table based on the data from a particular point in time.

Duane
Hook'D on Access
MS Access MVP

 

[PCHomepage]

Yes, these reports are each based on a query and I expected the query would be run each time the report was opened as you said, but this does not seem to be the case. The data shown in each one is from the creation date of several months ago. I can open the report in Design mode and, after fiddling around, get it to update but it does not do so automatically and manually forcing the update also messes up the layout considerably, making the pie-charts virtually unreadable with the labels on top of each other.

Actually I have created several reports for different time periods but really I need only a single one and then some selector for picking the time period (this year, last year & all). Possibly the act of submitting it to the query would trigger a refresh but I am not sure how to do that. I am a Web programmer, not a desktop programmer and rarely work with Access.

 

[dhookom]

What report view are you seeing these results? Did you try print preview?

Duane
Hook'D on Access
MS Access MVP

 

[PCHomepage]

By default, I have the reports opening to a print preview.

 

[dhookom]

How about providing the Row Sources?
Are there any values in the Link Master/Child properties?

Duane
Hook'D on Access
MS Access MVP

 

[PCHomepage]

Each report uses a couple different queries with one of the simpler ones looking something like this:

SELECT Count(Orders.OrderID) AS ReqCount, [Request Type].ReqType
FROM Orders, [Request Type]
WHERE ((Year(Orders.OrderDate))<Year(Now()) And ([Request Type].ID=Orders.ReqType))
GROUP BY [Request Type].ReqType
ORDER BY [Request Type].ReqType;

which pulls up rows like this:

ReqCount	ReqType
247	County
163	General Public
48	PRA (Public Records Act)
3	Unknown

 

[dhookom]

I asked for Row Sources. Did you provide chart row sources or what?

Duane
Hook'D on Access
MS Access MVP

 

[PCHomepage]

I thought that's what I provided. Did I misunderstand?

 

[dhookom]

Each report uses a couple different queries

I didn't know if the SQL you provided was a report record source or the chart row source.

I also asked about the link master/child which you didn't respond to.

Duane
Hook'D on Access
MS Access MVP

 

[PCHomepage]

The SQL was what a portion of the report uses - there are a couple different ones generating different pie-charts for similar data on each report. I showed the query and the results of running the query.

As for link master/child, I know what the terms mean and thought that posting one of the queries would answer the question. Was there there something else needed? It seems we are at cross-purposes so maybe there is still something I am not understanding.

My question basically: is there a setting or switch somewhere in Access, perhaps in the properties or that can be added to a macro, to force it to refresh the reports?

 

[dhookom]

There is no "switch" that turns on or off the refresh of data in reports. It is nearly impossible to turn it off. A report will almost always (99.9% of the time) display the current data as it is stored in your current tables.

If you don't understand the difference between a Report Record Source and a Chart Row Source, please open your report in design view and check the properties of your report and of your chart controls. A chart control will also have Link Master and Link Child properties.

If you aren't seeing the data in the chart that you think you should be seeing then open the data sheet views of the Row Sources.

A simple trouble-shooting method is to attempt to re-create a simpler version of your report to see if it behaves the same.

Duane
Hook'D on Access
MS Access MVP

 

[PCHomepage]

Ah! I see what you mean. As I mentioned, I am a Web programmer and hardly ever deal with desktop database applications so thanks for your patience.

There are no Master or Child Field properties, and the Data Source is:

SELECT [ReqType],[ReqCount] FROM [Requests by Type Current];

There is also a property for Update Options and it is set to Automatic.

 

[dhookom]

What are the responses to my questions/suggestions:

If you aren't seeing the data in the chart that you think you should be seeing then open the data sheet views of the Row Sources.

A simple trouble-shooting method is to attempt to re-create a simpler version of your report to see if it behaves the same.

Duane
Hook'D on Access
MS Access MVP

 

[PCHomepage]

That's what I've had to do in the past to make the report be up-to-date. Viewing the Row Sources shows the proper data and, of course, running the query manually shows it. Running the query manually does not affect the report but viewing the row data does.

 

[dhookom]

If your data in your reports is not reflecting the current data in your tables/queries then something is seriously off with your application. Did you attempt to create a simple example of a report with a chart?

Duane
Hook'D on Access
MS Access MVP

 

[PCHomepage]

That's what I am trying to do now as it seems very odd to me too.