How to show/hide fields based on necessity and null value

[srk999]

I have a form with multiple fields. The fields hold a number of action items, one item per field. Also hold corresponding name, one name per field. How can let the user show/hide the fields based on necessity? Because, may be not all of the available fields are needed to list all the action items. Similarly, how do I hide the empty fields and labels in the report?



Any help would be appreciated.

[June7]

Sounds like db structure is not normalized. Multiple similar fields is an indicator.

What you describe is not practical - as demonstrated in https://www.accessforums.net/tutoria...ull-41297.html

Should correct the data structure.

[srk999]

You hit the problem right in the head. The whole setup did feel quite cumbersome to me. What would be the best way to setup a database for something like these tables: http://i.imgur.com/Mopsc2d.png

I need to collect the data from users, and present it in a report that looks as close to these tables as possible. While the Project Progress table has fixed number of rows and columns, the other two tables' rows will vary based on user's necessity. That is where I am tripping hard.

And, thank you for all the help. I am completely redesigning the database.

At the moment my redesigned db has a table for each table in the picture. Still feels quite cumbersome for those two tables with varying number of rows. Also, my setup requires the user to enter a unique ReportID, which is something I'd avoid if possible.
Database1.zip

[June7]

Why does every field (except ReportID) in ProjectProgress have "" as caption?

Where will the percentage completed and planned data come from?

If you want to replicate the ProjectProgress table shown in the image, need to restructure the Access table. Consider:

tblProjects
ProjectID (primary key)
ProjectName
etc

tblRisks
ProjectID (foreign key)
Description
Owner

tblShutdowns
ProjectID (foreign key)
Reason
ShutDate

tblReports
ReportID (primary key)
ReportDate

tblProgressProject
ProjectID (foreign key)
ReportID (foreign key)
ActivityCode (E, P, C, Cx)
PctCompletedToDate
WorkCompletedLast2Weeks
WorkPlannedNext2Weeks

[srk999]

Hey June7, this db.zip is what I got so far based on what you said. I think I am messing up the relationship, and the form won't let me enter data.

Also, I didn't understand this part to make the 5x4 Risks table equivalent in the form:

ActivityCode (E, P, C, Cx)
PctCompletedToDate
WorkCompletedLast2Weeks
WorkPlannedNext2Weeks

Never seen anything like this before in my very short trek with access.

Edit: Also, how do I enter multiple risks and risk owners per report, and multiple shutdowns and dates? I think I am doing something fundamentally wrong.

BTW, the empty quotes were my noob attempt to make those fields caption-free so that I can arrange them manually in the form to look table cells.

[June7]

And I've never seen percentage data accumulated like this.

Data in tblProgressProject would be like:

ProjectID	ReportID	Activity	Pct	Last	Next
1	1	E	100	something	something
1	1	P	100	something	something
1	1	C	90	something	something
1	1	Cx	0	something	something
2	1	E	80	something	something
2	1	P	90	something	something
2	1	C	0	something	something
2	1	Cx	70	something	something

However, I am in no way confident that a ReportID should be in this structure. Maybe each Progress record should instead have a date stamp. Then run the report with filter that would pull progress records dated within a given date range (the report is generated every 2 weeks?). This would eliminate tblReports.

The suggested Risk and Shutdown tables allow for multiple records for each project. Those records would be incorporated in every report. I did not think they were date sensitive. If they are, then add a date field as well and apply same date range filter criteria.

I expect report will require subreports.

[srk999]

That makes more sense. Also, removing the ReportID makes sense as well. Just to make things simple, I am removing the dates fields. Hence there won't be a record of previous reports (they'll be distributed each week in PDF format anyway), and the user will edit reports that matches his/her ProjectID. In fact, I'll just make subforms for each project, to make things even simpler. What do you think?

[June7]

I doubt 'subforms for each project' would be simpler. If you want the report generated for a single project, then filter the main report and the linked subreports will display only related records.