Allow User to Customize Columns on Report

[Mlkobefan]

Hi Everyone,

I have only used Access for 2 weeks, but have spent many hours on this forum and throughout the internet reading in order to put together a database that allows users to input labor hours based on activity ID and the associated responsible supervisor. The database currently prints reports (like the one attached) that will group/sort on different fields and then lists hours worked per week. My issue is that I do not know how to allow users to customize the date columns in the reports, in order to choose any of the dates and cooresponding data available in my table. The user should not have to select more than 8 dates for a report.

Currently, I have to go to design view of the report, click add existing fields, and drop in the field with the date I want to show on the report. This will need to be automated so that a user can select a group of dates from a listbox in a form, then have a report generate with only the dates selected. My approach to entering hours in the table may not be the best, with columns for each date stretching out until late 2018. This becomes an issue when trying to create a combo box to show the dates, as they are not data points but column headings.

If anyone has an idea of how I could allow the user to select only a certain group of dates and have it show on the report, the help would be greatly appreciated! This is my last stumbling block before the database can be up and running. Thank you!

Attachments:
Report Screenshot: Shows what I want the report to look like. The user should be able to modify the date columns and change to any other dates available in the table.
My Manual Selection of Dates: Shows how I have to currently modify the date columns in the report by dragging and dropping the correct field into the report. I want to automate this process.
Table Screenshot: Shows how my data is currently being entered, with a column heading for every week from now until 2018.
Report Form Screenshot: This is what the form to create the report SHOULD look like. The list box there is not functional at all, and is not linked to the actual table containing the data. I made this to give an idea of what I want to accomplish. I am expecting I will need some code for the "on click" of the create report button.

[ranman256]

instead of printing reports with dates across the top, with lots of customization,
the dates should run DOWN , this requires NO customization.
this is how databases work, by printing data down ,vertically.

You are just creating problems doing it backwards, (horizontally).
(As I have told many a VP)

[Mlkobefan]

Hi ranman256,

I agree dates should run down, but in this case, I have no idea how to structure my tblHours so that dates are running vertically. Since each activity has many weeks of hours associated with it, wouln't I need to repeat the information for an activity for each new date? This seems very tedious and something I want to avoid.

Edit: Here is a screenshot of my table. The dates columns continue on to the right until 2018 is reached. If restructuing to vertical dates would be my best option, how could I accomplish this so the rest of the data matches up?

[June7]

Your data structure is not normalized - it is 'committing spreadsheet'. Why is ActivityIDFK field empty? Exactly what is an 'activity'? Can the same activity be accomplished on multiple dates or is each activity a unique entity?

Consider:

tblPeople
PeopleID
LastName
FirstName

tblActivities
ActivityID
ActivityName

tblActivitiesDone
ActivityDoneID
ActivityDate
ActivityIDFK

tblActivitiesHours
ActivityDoneIDFK
ResponsiblePersonFK
Hours
Notes

[Mlkobefan]

June7,

The ActivityIDFK field is empty because I removed all of the sample data I had for security reasons. Each activity ID is unique and is a certain task that needs to be completed on the project. However, this database does not worry about activity start and end dates--only the amount of hours each person is working on it per week. So the dates are there to track employee hours, not to see how far along the activity is progressing.

Each activity ID can have multiple people working on it. Also, one person can work on multiple activities. I am creating three reports from this data: A report on activities per Responsible Supervisor, a report on activities per Responsible Person, and a report of all the Responsible People Per Activity ID.

[June7]

So your data structure gets more complicated. You may need a tblProjects and a tblProjectsActivities, etc.

What is an activity? Is there a standard set of activities that can be associated with each project? Or are activities unique to each project? I mean can an activity be "review contract" which is an activity relevant to any and all projects?

[Mlkobefan]

So your data structure gets more complicated. You may need a tblProjects and a tblProjectsActivities, etc.

What is an activity? Is there a standard set of activities that can be associated with each project? Or are activities unique to each project? I mean can an activity be "review contract" which is an activity relevant to any and all projects?

An activity is a task that needs to be completed; each activity is unique and they are all going towards the completition of only one project. An activity is not a generic term like review contract, it is the completition of a specific task for the overall project.
For example, if I were building a house, my activities would be install flooring, install plumbing, complete woodwork, etc. They are specific tasks all going towards the completition of only one project--building the house.

Bakc to my original question--aside from this and the possible restructuring of my data, all I am trying to figure out is how to create a customizable report where the columns with dates in the headings can be changed easily by the user to select other columns in the table. I can do this manually using the "add existing fields" in design view of the report, but I am trying to make this user friendly by way of a form and a combo box.

[CJ_London]

The problem with your table design is that every time you add a new week, you have to add a new column to the report to cater for that week - and at some point you are going to hit the maximum field limit of 255 fields.

However if you want a quick and dirty solution then you could display the data in a datasheet view form, then display this in your report as a subform.

Then in the subform load event have some code which scans through your multiselect listbox and if the date is not selected set the relevant control columnwidth property to 0.

Note that with datasheets you can have a total row at the bottom (in normal view mode, click on the epsilon (Totals) icon in the home tab ribbon) which does away with the need for your ActivytyIDFK footer.

[CJ_London]

Sorry, also meant to say you'll need some code to manage multipage reports as well - but that is the price of having unnormalised data. Follow June7's advice and the code you would need would be minimal

[Mlkobefan]

Sorry, also meant to say you'll need some code to manage multipage reports as well - but that is the price of having unnormalised data. Follow June7's advice and the code you would need would be minimal

I do not think the subform idea would work well, as I need the data to be grouped and sorted clearly like in the reports I have now. I am using formating for the total hours to see when an employee is over 40 hours in a week by changing the color of the box, so the current structure of the report works well. The field limit should also not be an issue, as we can delete data that is over 6 months old and the project will only last about 5 more years.

While I agree the data structure might not be the best, I really am having a hard time understanding how I can structure my tables so that I can have the dates running vertically while still allowing me to enter hours for various employees at a time and for different dates. Currently, I am using datasheet view in my forms to allow the user to go in and easily modify the hours worked for all employees for each date (which is the column heading). This can be seen in the attachment in post #3.

The issue I am having can be solved with a dynamic report that uses the combo box of dates in the form as the control source for the report fields, but I run into issue with the dates running vertically again. Since the dates are column headings, a combo box would show the data beneath it and not the date itself; I need a way to select a date from say, tblDates, which vertically lists all dates under one column and then use that selection to choose the matching date column in tblHOURS to populate the query.

[orange]

@Mlkobefan

Have you considered using a project management software package? Based on my reading of your post it seems to fit Project Management --and you can probably acquire one that has a proven track record rather than build your own. Are you in the project management business or the database development business?

Building your own software may take a lot of serious time away from your main line of business---just sayin'.

Good luck on whatever you decide.

[CJ_London]

Unfortunately you are trying to put a square peg into a round hole. Access is not Excel. By having an 'easy' input form for your users, everything else becomes much more complex, inefficient or impossible.

The only other thing I can suggest is as before but instead of setting the columnwidth to 0, you have a routine in the report format event (not sure which one) which hides the relevant column fields (in p

age header, detail and activity footer sections) and then moves the remaining visible columns to the left to fill the gaps.

If your data was stored vertically, you would use a crosstab query to populate your report and none of the above would be required.

Crosstabs are not updateable but here is a link to a form which makes it possible - so you can keep your users happy

http://www.access-programmers.co.uk/...eable+crosstab

good luck with your project

[Mlkobefan]

@Mlkobefan

Have you considered using a project management software package? Based on my reading of your post it seems to fit Project Management --and you can probably acquire one that has a proven track record rather than build your own. Are you in the project management business or the database development business?

Building your own software may take a lot of serious time away from your main line of business---just sayin'.

Good luck on whatever you decide.

I am in the project management business and 100% agree with you--there are many software options available that would allow me to do this but I am being told to reinvent the wheel here.

Unfortunately you are trying to put a square peg into a round hole. Access is not Excel. By having an 'easy' input form for your users, everything else becomes much more complex, inefficient or impossible.

The only other thing I can suggest is as before but instead of setting the columnwidth to 0, you have a routine in the report format event (not sure which one) which hides the relevant column fields (in p

age header, detail and activity footer sections) and then moves the remaining visible columns to the left to fill the gaps.

If your data was stored vertically, you would use a crosstab query to populate your report and none of the above would be required.

Crosstabs are not updateable but here is a link to a form which makes it possible - so you can keep your users happy

http://www.access-programmers.co.uk/...eable+crosstab

good luck with your project

That idea has come to mind before, and I have written code to hide columns in the report if a check box on the form is not selected.

Me.Activity_ID.Visible = Forms!frmCreateReportOnP6Data!Check1

The issue is that a report cannot hold many fields and it would be cumbersome to write this code for all dates from now until project end. I will look through your link and see if I can use that to assist me.

Thanks for the help guys!

[CJ_London]

final suggestion re number of fields - you can have around 750 so should not be a problem

as a precursor to my last suggestion, have all column fields unbound - limited to 8 columns since that is the max you have set your users. Better if these controls are named incrementally so you can easily loop through them e.g. hdr1, hdr2 ..., det1, det2..... Thinking about it, the footer control is calculated so can remain as is.

and in the report load event assign an appropriate field to each controlsource.

[Mlkobefan]

final suggestion re number of fields - you can have around 750 so should not be a problem

as a precursor to my last suggestion, have all column fields unbound - limited to 8 columns since that is the max you have set your users. Better if these controls are named incrementally so you can easily loop through them e.g. hdr1, hdr2 ..., det1, det2..... Thinking about it, the footer control is calculated so can remain as is.

and in the report load event assign an appropriate field to each controlsource.

So I tried something like this (my code is below), but the issue is with the combo boxes themselves. I placed 8 combo boxes in the form, but since the dates are column headings and not actual data, I cannot get the report to pull the correct column. Access just asks me to enter the parameter for the detail sections of the report. If I can figure out how to relate a date selected in the combo box to the date column in tblHOURS I will be set.


Option Compare Database

Private Sub Report_Load()
'Define the headers in the report
Dim i As Integer
'loop through the combos
For i = 1 To 8

'check the combo has some entry
If Nz(Forms.frmCreateReportActivitiesPerResponsibleSu pervisor("cmb" & i).Column(5), "") <> "" Then

'set the header text for this combo choice
Me("txtHead" & i) = Forms.frmCreateReportActivitiesPerResponsibleSuper visor("cmb" & i).Column(6)
End If
Next i

End Sub
Private Sub Report_Open(Cancel As Integer)
'Define the detail source in the report
Dim i As Integer
'loop through the combos
For i = 1 To 8

'check the combo has some entry
If Nz(Forms.frmCreateReportActivitiesPerResponsibleSu pervisor("cmb" & i).Column(5), "") <> "" Then

'set the detail source for this combo choice
Me("txtDetail" & i).ControlSource = Forms.frmCreateReportActivitiesPerResponsibleSuper visor("cmb" & i).Column(5)
End If
Next i
End Sub