Calculate change from record to record

[Tom]

Dear All,

in my database, I am recording projects in different categories. I
have now created a crosstab query that sums up all project per
category and year, including a total number of projects per year.

How can I also show, how the total number is changing compared to the
previous year? Basically, I am looking for something like (No of
projects of previous record's year-No of projects of current record's
year)/(No of projects of current record's year) at the end of each
record.

A B C Total Change
Y1 3 2 5 10 0 %
Y2 1 7 6 14 + 40 %
Y3 2 2 5 9 - 36 %

Sorry, the answer may be really simple, I just can't see it.


Regards
Tom

 

[Allen Browne]

One way to do this would be in a report. You declare a variable in the
General Declarations section of the report's module, and assign it the
current value in the Detail section's Format event. You can then use
Detail_Print to read the value and compare it to the current record.

It that's not practical, you could try creating another query that uses the
crosstab as in input "table", and use a subquery to read the previous value.
If subqueries are new, see:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[Tom]

One way to do this would be in a report. You declare a variable in the
General Declarations section of the report's module, and assign it the
current value in the Detail section's Format event. You can then use
Detail_Print to read the value and compare it to the current record.

It that's not practical, you could try creating another query that uses the
crosstab as in input "table", and use a subquery to read the previous value.
If subqueries are new, see:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

Dear Allen,

I am afraid this is beyond me. Although I think I understand what you
are suggesting and looking at your web page, this seems to be a very
slick solution, I am unable to implement this for my problem. Below is
my SQL code:

TRANSFORM Count([Main Table].ProjectID) AS CountOfProjectID
SELECT Year([Date]) AS [Year], Count([Main Table].ProjectID) AS [Total
Of ProjectID]
FROM Statustbl LEFT JOIN [Main Table] ON Statustbl.StatusID = [Main
Table].Status
GROUP BY Year([Date])
ORDER BY Year([Date])
PIVOT Statustbl.Status;

This works great so far, showing me projects at various statuses per
year and a total project count per year. Whenever I try to include
the subquery as per our explanations, I get all sorts of errors,
starting with syntax errors and parameters undefined, or empty tables.
Unfortunately, I am a beginner, so I can not really deduce the real
problem from Microsoft's error messages.

Do you (or anybody else) have another trick for me?


TIA, Tom

 

[Allen Browne]

Mixing the subquery with the crosstab could be difficult. Try creating
another query that uses the crosstab query as an input "table." Create the
subquery in this new query.

The other alternative (using code) would work like this:

1. Open the report in design view.

2. Add a text box to the Detail section to show the percentage change.
Give it these properites:
Format Percent
Name txtDiff

3. Set the On Format property of the Detail section to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub ..." and "End Sub" lines, enter:
If FormatCount = 1 Then
If mlngTotal = 0 Then
Me.txtDiff = Null
Else
Me.txtDiff = Me.[Total Of ProjectID] - mlngTotal) / mlngTotal
End If
End If

4. Move the cursor to the top of the code window (just below the Option
lines), and enter:
Private mlngTotal As Long

5. Set the On Print property of the Detail section to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub ..." and "End Sub" lines, enter:
If PrintCount = 1 Then
mlngTotal = Nz(Me.[Total Of ProjectID], 0)
End If

6. Set the On Format event procedure of the Report Header section to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub ..." and "End Sub" lines, enter:
mlngTotal =0

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

One way to do this would be in a report. You declare a variable in the
General Declarations section of the report's module, and assign it the
current value in the Detail section's Format event. You can then use
Detail_Print to read the value and compare it to the current record.

It that's not practical, you could try creating another query that uses
the
crosstab as in input "table", and use a subquery to read the previous
value.
If subqueries are new, see:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

Dear Allen,

I am afraid this is beyond me. Although I think I understand what you
are suggesting and looking at your web page, this seems to be a very
slick solution, I am unable to implement this for my problem. Below is
my SQL code:

TRANSFORM Count([Main Table].ProjectID) AS CountOfProjectID
SELECT Year([Date]) AS [Year], Count([Main Table].ProjectID) AS [Total
Of ProjectID]
FROM Statustbl LEFT JOIN [Main Table] ON Statustbl.StatusID = [Main
Table].Status
GROUP BY Year([Date])
ORDER BY Year([Date])
PIVOT Statustbl.Status;

This works great so far, showing me projects at various statuses per
year and a total project count per year. Whenever I try to include
the subquery as per our explanations, I get all sorts of errors,
starting with syntax errors and parameters undefined, or empty tables.
Unfortunately, I am a beginner, so I can not really deduce the real
problem from Microsoft's error messages.

 

[Tom]

Mixing the subquery with the crosstab could be difficult. Try creating
another query that uses the crosstab query as an input "table." Create the
subquery in this new query.

The other alternative (using code) would work like this:

1. Open the report in design view.

2. Add a text box to the Detail section to show the percentage change.
Give it these properites:
Format Percent
Name txtDiff

3. Set the On Format property of the Detail section to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub ..." and "End Sub" lines, enter:
If FormatCount = 1 Then
If mlngTotal = 0 Then
Me.txtDiff = Null
Else
Me.txtDiff = Me.[Total Of ProjectID] - mlngTotal) / mlngTotal
End If
End If

4. Move the cursor to the top of the code window (just below the Option
lines), and enter:
Private mlngTotal As Long

5. Set the On Print property of the Detail section to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub ..." and "End Sub" lines, enter:
If PrintCount = 1 Then
mlngTotal = Nz(Me.[Total Of ProjectID], 0)
End If

6. Set the On Format event procedure of the Report Header section to:
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
Between the "Private Sub ..." and "End Sub" lines, enter:
mlngTotal =0

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,

I am afraid this is beyond me. Although I think I understand what you
are suggesting and looking at your web page, this seems to be a very
slick solution, I am unable to implement this for my problem. Below is
my SQL code:

TRANSFORM Count([Main Table].ProjectID) AS CountOfProjectID
SELECT Year([Date]) AS [Year], Count([Main Table].ProjectID) AS [Total
Of ProjectID]
FROM Statustbl LEFT JOIN [Main Table] ON Statustbl.StatusID = [Main
Table].Status
GROUP BY Year([Date])
ORDER BY Year([Date])
PIVOT Statustbl.Status;

This works great so far, showing me projects at various statuses per
year and a total project count per year. Whenever I try to include
the subquery as per our explanations, I get all sorts of errors,
starting with syntax errors and parameters undefined, or empty tables.
Unfortunately, I am a beginner, so I can not really deduce the real
problem from Microsoft's error messages.

Dear Allen,

many thanks for your quick response. The suggestion for modifying the
report works like a charm. I still need to think it over a couple of
times to exactly understand what you did (learn the syntax), but at
least I got what I needed.

I do think though that trying to solve this problem in a query is the
cleaner and more flexible solution, so I will work on this when I have
a quite minute (once I tackled this problem I will post the solution).
I am surprise that there is no easy built-in function as I would have
thought this is common problem ...

Again, many thanks or your help!
Tom