Update tables for new year

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

Guest

I'm using Access 2003. I have a Projects form, and a Reports subform linked
by Project_ID and Grant_Type. The Reports table has fields [Project_ID],
[Grant_Type], [Year], [RptQ1], [RptQ2], [RptQ3] and [RptQ4]. At the
beginning of every year, I would like to have a query insert a new record for
each project in the Reports table - so on January 1st 2008 I want every
project to have an entry in the Reports table with that Project_ID,
Grant_Type and the year 2008. I've been playing with the following code:

Dim X AS Integer
Dim Y AS Integer
Dim FY AS Integer

X= CurrentProject.Connection.Execute “SELECT Min ([Project_ID]) FROM
TblReports;â€
Y= CurrentProject.Connection.Execute “SELECT Max ([Project_ID]) FROM
TblReports;â€
FY=InputBox (“What FY?â€)

For I as Integer = X to Y
CurrentProject.Connection.Execute “INSERT INTO TblReports
(Project_ID,Grant_Type,Year) SELECT " & I & ", Grant_Type," & FY & " FROM
TblProjects;"
Next I

But I want it to be able to skip a record if either the Project_ID does not
exist in the Projects table or if there is already a record for 2008 for that
Project_ID/Grant_Type in the Reports table.
 
Hey Todd,
This is how I handled a similar situation (in my case it was monthly
reporting). I used a query to appended a single record for each member to a
specific table used for reporting. This was where the member had no activity
for the reporting period. This record had a zero amount, but it prevented an
error on the reporting and provided a print-out for the member. It was
triggered when I ran the report.

If you have more questions, please ask.
Hope that helps you,
SteveD
 
Thanks Steve. I was wondering the day after I posted this if I wouldn't be
better off using an Update query rather than code (I tend to make things more
complicated than they need to be). This would work better even if I wanted
to do it at the beginning of the year instead of as the report runs (like you
describe). I could use the Project table as the "updator" to get an entry
for each project, with the Reports table as the "updatee" and just put a
variable in the Year field like [What FY?] so it prompts every time.

SteveD said:
Hey Todd,
This is how I handled a similar situation (in my case it was monthly
reporting). I used a query to appended a single record for each member to a
specific table used for reporting. This was where the member had no activity
for the reporting period. This record had a zero amount, but it prevented an
error on the reporting and provided a print-out for the member. It was
triggered when I ran the report.

If you have more questions, please ask.
Hope that helps you,
SteveD

Todd K. said:
I'm using Access 2003. I have a Projects form, and a Reports subform linked
by Project_ID and Grant_Type. The Reports table has fields [Project_ID],
[Grant_Type], [Year], [RptQ1], [RptQ2], [RptQ3] and [RptQ4]. At the
beginning of every year, I would like to have a query insert a new record for
each project in the Reports table - so on January 1st 2008 I want every
project to have an entry in the Reports table with that Project_ID,
Grant_Type and the year 2008. I've been playing with the following code:

Dim X AS Integer
Dim Y AS Integer
Dim FY AS Integer

X= CurrentProject.Connection.Execute “SELECT Min ([Project_ID]) FROM
TblReports;â€
Y= CurrentProject.Connection.Execute “SELECT Max ([Project_ID]) FROM
TblReports;â€
FY=InputBox (“What FY?â€)

For I as Integer = X to Y
CurrentProject.Connection.Execute “INSERT INTO TblReports
(Project_ID,Grant_Type,Year) SELECT " & I & ", Grant_Type," & FY & " FROM
TblProjects;"
Next I

But I want it to be able to skip a record if either the Project_ID does not
exist in the Projects table or if there is already a record for 2008 for that
Project_ID/Grant_Type in the Reports table.
Click to expand...
Click to expand...
 
Back
Top