Hi all...
I am developing my first access app for building construction work purpose
Actually all I want is to make monthly progress reports to submit to my boss
This is my masterTable
Project Name | Locality | EstimatedCost | Stage1CompletedDate | Stage2CompletedDate | Stage3Date | etc.... | ProjectCompletedDate |
I am regularly updating this table whenever each stage completed and adding new projects whenever new projects assigns to me.
The format in which i want to get the report is as follows
progress report for May-2020 (FY 2020-21) ' (FY is Financial Year ie. from 1st April 2020 to 31st March 2021)
myDesignation | Number of projects cleared Stage1 in this month | Total stage1 cleared during this FY | Number of projects cleared Stage2 in this month | Total stage2 cleared during this FY |...etc...No.Project Completed| Total Project Completed in FY
My plan is
I dont know how to make a blank recordset having same filed as in progressReportTable
could anyone please tell
I am developing my first access app for building construction work purpose
Actually all I want is to make monthly progress reports to submit to my boss
This is my masterTable
Project Name | Locality | EstimatedCost | Stage1CompletedDate | Stage2CompletedDate | Stage3Date | etc.... | ProjectCompletedDate |
I am regularly updating this table whenever each stage completed and adding new projects whenever new projects assigns to me.
The format in which i want to get the report is as follows
progress report for May-2020 (FY 2020-21) ' (FY is Financial Year ie. from 1st April 2020 to 31st March 2021)
myDesignation | Number of projects cleared Stage1 in this month | Total stage1 cleared during this FY | Number of projects cleared Stage2 in this month | Total stage2 cleared during this FY |...etc...No.Project Completed| Total Project Completed in FY
My plan is
- To make a separate Table for progress reports ---because I always want to get the same reports submitted in past months (if i query every time, any wrong updation will show incorrect past reports)
- Sum(IIf(Year([MasterTable]![Stage1Date])=Year(me.TxtReportDt.Text) And Month([MasterTable]![Stage1Date])=Month(me.TxtReportDt.Text),1,0)) this will get number of projects completed stage1 during reporting month
- Sum(IIf([MasterTable]![Stage1Date]>=FyStartDate And [MasterTable]![Stage1Date]<=(DateSerial(Year(TxtReportDt.Text),Month(TxtReportDt.Text)+1,1)-1),1,0)) this will get total number of projects completed stage1 during Financial Year (FY)
- Make a recordset which have same fields as progressReportTable and insert these values to each of the record set fields
- append recordset with the original progressReportTable
- generate report with progressReportTable Table
I dont know how to make a blank recordset having same filed as in progressReportTable
could anyone please tell
- is it a good plan?
- how to create a blank recordset having same filed as in progressReportTable?
- is any other easier method available to make progress report