Percent of change on report...Is this possible?

[lmac]

I have a report that shows totals for the month from daily entries (report is based on simple query based on data entry table). I want to add a Percent of Change field to the report that shows the % of change in the Total Sales Per Hour from month to month. Is this possible?

Report has:
.......Total Hours....Total Sales....Sales per Hour....SPH % Change
Aug.....500.............4210...............8.42..................NA
Sept....750.............5900...............7.87..................-6.57%
Oct......800.............6680...............8.35..................6.14%
Nov......825.............7200...............8.73..................4.52%

 

[SydneyGeek]

You may find it simpler to do this in Excel.
In a new workbook, use MS Query to pull the data from Access into the worksheet. In the first blank column, build the formula to get what you need.
Reason for the suggestion: Access is not great at comparing 2 records (rows). I guess you could try to get fancy with a DLOOKUP to pull the previous value, then compare them, but that could be a pain.

Denis

 

[lmac]

Still seeking help for this. This is an Excel spreadsheet I am making into an Access report. Anyone else have any suggestions on how to do this?

 

[SydneyGeek]

If it's already in Excel, consider doing the calc there and importing into Access. Alternatively, why not report in Excel anyway -- unless you have a whole pile of other data in the db that you want to include.

As I mentioned before, you can pull in data from the previous record but it involves either
(1) using a DLOOKUP function or
(2) writing the values to a field in your table, using code

As far as I know, there is no simple control or switch that can create the comparison in a report, unless you have already grabedd / modified the data using one of the above approaches.

Denis

 

[CT Witter]

You could try something like this: MS KB#208953
ACC2000: How to Compare a Field to a Field in a Prior Record - http://support.microsoft.com/default.aspx?scid=kb;en-us;208953

HTH,
CT