I'm building an application to monitor progress against Technical Activity
numbers. I have two tables:
tblTechAct
TechnicalActivity (PK)
Description
ActualStart
ActualFinish
tblProgress
TechnicalActivity (FK)
WeekNo
Progress
The app imports data from another system (Oracle) and the data is used to
populate an Excel spreadsheet with Week Number columns containing the
progress data.
In an ideal world I would import the Technical Activity numbers into
tblTechAct and then import progress data on a weekly basis. BUT ... of
course the list of Technical Activities will grow with time and, because of
the nature of the Excel spreadsheet, I need to "fake" previous weeks'
progress for new Technical Activities (the Excel spreadsheet is created from
scratch every time the routine is run, it is not an existing one that is
"added to" each week).
So for example, if TA No XYZ123 is added in Week 750, I need to add an entry
for it in tblProgress for weeks 720 onwards at 0% progress.
My question is, what is the best method of doing this? Importing into
tblTechAct is easy but what about tblProgress? My immediate thought was to
have a query to identify numbers in tblTechAct that aren't in tblProgress
and write a function to add a record for each unique entry in the WeekNo
field. Do I need a function or can it be done in queries.
Sorry about the rambling post, I hope it makes sense.
Many thanks.
Keith.
