How to make a consolidated Report (1 Viewer)

sunkerala

New member
Local time
Today, 09:13
Joined
Apr 26, 2020
Messages
11
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
  1. 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)
  2. 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
  3. 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)
  4. Make a recordset which have same fields as progressReportTable and insert these values to each of the record set fields
  5. append recordset with the original progressReportTable
  6. generate report with progressReportTable Table
My problems

I dont know how to make a blank recordset having same filed as in progressReportTable

could anyone please tell
  1. is it a good plan?
  2. how to create a blank recordset having same filed as in progressReportTable?
  3. is any other easier method available to make progress report
 

arnelgp

error reading drive A:
Local time
Today, 11:43
Joined
May 7, 2009
Messages
10,829
google Gantt chart and some example, then you
will have an idea of what you really need.
 

sunkerala

New member
Local time
Today, 09:13
Joined
Apr 26, 2020
Messages
11
google Gantt chart and some example, then you
will have an idea of what you really need.
Hi Arnelgp,
I googled a little about gantt chart, didn't search in depth. I think gantt chart is suitable in excel. I have done a lot of work in forms,queries and vba in access for this app. so i think it is better to keep this track.

thank you for your support
 

CJ_London

Super Moderator
Staff member
Local time
Today, 03:43
Joined
Feb 19, 2013
Messages
12,569
1.
1. generally not a good idea, your app should prevent wrong updates - based on your business rules.​
2/3 probably OK, not possible to say without knowing your data​
4/5/6 not sure why you want to make a recordset, just use a query.​

2. perhaps something like

set rst=currentdb.openrecordset("SELECT * FROM progressReportTable WHERE False")​
which will return an empty recordset​

3. I suspect the issue is around wanting to make sure data from prior months cannot be changed or added to. If this is the case, use a 'period' column to assign all transactions to the current period and perhaps make use of from/to dates.
 

sunkerala

New member
Local time
Today, 09:13
Joined
Apr 26, 2020
Messages
11
thank you Cj_London for your valuable suggestions:love::love:

as you said, instead of going with recordset, i will use query, i think it is easier
i was wandering around recordset
once again thank youu
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:43
Joined
Feb 19, 2002
Messages
30,036
If you design your app correctly and use dates to mark events, you should be able to reproduce any report at a point in time. If you can't get that working, then export the report each time to a .pdf and keep the .pdf's. Eventually, you'll learn enough about database design to be able to control your reports using selection criteria.
 

Users who are viewing this thread

Top Bottom