Budget History

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,

In my projects database, I have a projects table (tblProjects) in a
one-to-many relationship with an approved budget amounts table (tblApproved).
tblApproved holds the current and past approved budget amounts for each
project, so I use a sub-query to produce a report with all the latest budget
amounts as follows:

(SELECT Max(tblTemp.dtApprovedDate) FROM tblApproved AS tblTemp WHERE
tblTemp.lngID = tblApproved.lngID)

I'd like to produce a budget history report that will display a column for
each of the last three approved budget dates dynamically. Would this be
possible using a similar sub-query approach?
 
I don't believe a subquery in the select can return more than a single
record. Consider using a subreport which you can limit to any number of
records.
 
Thanks for the reply Mr. Hookom. If I use subreports, is there a way to
query for the second/third latest budget entries by date?

J. Mullenbach
 
You can add a text box to the detail section of the sorted subreport:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No
Then add code to the On Format event of the section:
Cancel = Me.txtCount =1 OR Me.txtCount>3
 
Back
Top