Weekly Planner Template

[MattBaldry]

Hi All,

I was wondering if anyone had a very basic planner form they have used. I have searched the forums and there are plenty out there, but not quite what I am after.

I am trying to display employee workloads and I have a table with a due date, and a query with labour hours. I want to be able take the due date, work backwards with the labour time to work out start, and then display this, if possible, as a continuous form layout with employee names as rows and work days as columns. This could then be filtered by month to show employee workload/capacity.

I am thinking continuous form view with each employee on a row and then the days of the month displayed to the right. Something like below.

Employee	1st	2nd	3rd	4th	5th	9th	10th	11th	12th
Name 1		Job 123 Start	Job 123	Job 123	Job 123	Job 123 End
Name 2	Job 456 Start	Job 456 End		Job 789 Start	Job 789	Job 789	Job 789 End

I don't need any special features or to be able to edit the data here (it is pulled from an external table and edited in their software)

I am hoping someone has something they have used before.

~Matt

 

[Pat Hartman]

If your data is properly normalized, a crosstab query will pivot it to display it this way. Use a Where clause to limit the data range. Define the form with 31 day columns and force the crosstab to always return 31 columns even if the data doesn't contain 31. One way to do this is to use the columns property of the query.

 

[MattBaldry]

If your data is properly normalized, a crosstab query will pivot it to display it this way. Use a Where clause to limit the data range. Define the form with 31 day columns and force the crosstab to always return 31 columns even if the data doesn't contain 31. One way to do this is to use the columns property of the query.

Thanks Pat,

I shall have a look into this. I hadn't even thought about cross tab.

~Matt

 

[CJ_London]

just be aware of the overall width - you have 31 columns plus the employee name. Maximum form width is around 55cm. say you use 5 for the name, that leaves 1.6cm for each of the columns. 'Job 123 Start' will need about 2cm and that is without any margins. Of course, does depend on your font size

So suggest do this in a report and set the can grow property to true for the detail controls. You can still show the report in a form as a sub report

 

[MattBaldry]

If your data is properly normalized, a crosstab query will pivot it to display it this way. Use a Where clause to limit the data range. Define the form with 31 day columns and force the crosstab to always return 31 columns even if the data doesn't contain 31. One way to do this is to use the columns property of the query.

Hi Pat,

How would I force the cross tab to show all employees even if they have no jobs assigned? I know I can force columns to appear, but how would I force rows?

We currently have 25 employees, but in week 27, for example, only 7 have assigned jobs with a due date. I would like the crosstab to show all employees so we can see which ones can be assigned jobs within the selected week.

~Matt

EDIT: Sorted this issue

 

[Pat Hartman]

I agree with CJ that this could be awkward as a form.
Here's a sample with months:

 

[MattBaldry]

I agree with CJ that this could be awkward as a form.
Here's a sample with months:
View attachment 101516

Hi Pat,

I have this working now with the weeks I need displayed (based on the selected month) and the full employee list, which is great.

But it has a blank column with <> at the top now. I am assuming this is something to do with me showing all records from tblEmployee and only matching records in the query. Is there anyway to remove this <> column?

I need to show the full employee list as they need to be able to see at a glance who has no jobs assigned.

Also, I can see you have 2 row headings, can you advise the benefits of multiple row headings, as I may be able to make use of these.

~Matt

 

[CJ_London]

if this is the query and not a report, right click on the column header and select hide

When you pass this to your form or report, just don't include it in the view

As to why it is appearing, would need to see the sql you are using

 

[MattBaldry]

if this is the query and not a report, right click on the column header and select hide

When you pass this to your form or report, just don't include it in the view

As to why it is appearing, would need to see the sql you are using

Hi CJ,

Please see below.

TRANSFORM Sum(qryCapacityPlanningLabourTimesMonthWeek.[Job Time (Hours)]) AS [SumOfJob Time (Hours)]
SELECT [EmployeeFirstName] & " " & [EmployeeLastName] AS Employee, qryCapacityPlanningLabourTimesMonthWeek.[Assembly Type]
FROM qryCapacityPlanningLabourTimesMonthWeek RIGHT JOIN tblEmployees ON qryCapacityPlanningLabourTimesMonthWeek.EmployeeID = tblEmployees.EmployeeID
WHERE (((tblEmployees.EmployeeActive)=True) AND ((tblEmployees.EmployeeJobType)=3))
GROUP BY [EmployeeFirstName] & " " & [EmployeeLastName], qryCapacityPlanningLabourTimesMonthWeek.[Assembly Type], tblEmployees.EmployeeActive, tblEmployees.EmployeeJobType
ORDER BY [EmployeeFirstName] & " " & [EmployeeLastName]
PIVOT qryCapacityPlanningLabourTimesMonthWeek.WeekNumberDue;

I used the query builder and this is the SQL. Data all displays perfect for what I need, but the <> appearing has me scratching.

~Matt

 

[CJ_London]

don't have time to set up an equivalent query but you are grouping on tblEmployees.EmployeeActive and tblEmployees.EmployeeJobType but not displaying them (presumable because you are using them in your criteria)

for both these fields, change the group by to Where and untick the show box

 

[arnelgp]

you can use the technique here?

 

[MattBaldry]

you can use the technique here?

Hi Arne,

That looks almost perfect. Am I right in thinking I could manipulate the query to take the end date and work out a start date based on how many hours I have calculated for the job?

~Matt

 

[MattBaldry]

you can use the technique here?

Hi Arne,

I have put my data in but it gives an error, No Current Record. If there is 1 line of data in the job table, it works. As soon as you add a second line, it says no current record.

Could this be due to potentially overlapping dates?

~Matt

 

[arnelgp]

can you upload your employee and job-employee tables?

 

[MattBaldry]

Just in case anyone else is looking for something I found the below, and it was only €100 for the full editable version.

Well worth a look and, while I cannot tie it in to my existing data, it works better than anything I could have created.

~Matt

 

[isladogs]

For info, Alexsander Wojtasz who is the author of that app is giving a presentation to the Access Europe User group on 7 Dec:

Access Europe - Colin Riddington (Annotating Google Maps in Access) - Europe - AccessUserGroups.org

We meet on the first Wednesday of each month from 18:00-19:00 UK time starting Wed 2 March 2022

accessusergroups.org

 

[arnelgp]

For info, Alexsander Wojtasz who is the author of that app is giving a presentation to the Access Europe User group on 7 Dec:

from what i see from all your presentation, all are commercial and you are giving them venue to advertise their product.
in the eventuality to have sales. there is nothing really to "learn" from your presentation all are commercialization.

 

[isladogs]

from what i see from all your presentation, all are commercial and you are giving them venue to advertise their product.
in the eventuality to have sales. there is nothing really to "learn" from your presentation all are commercialization.

That is incorrect.
Most of the presentations have been and will continue to be for free apps.
Where commercial apps are involved, there has always been a free option available with the functionality demonstrated in the presentation.

For example, this week's presentation by Mike Wolfe will be accompanied by a free app with all code used

 

[arnelgp]

For example, this week's presentation by Mike Wolfe will be accompanied by a free app with all code used

that will makes mike and crystal the only, true Free for all code.

 

[isladogs]

Still incorrect.
My previous answer was accurate for both previous and future sessions.
For example, Alan Cossey's code from last month was also free as will be mine in Sept.
Chris Arnold made a free version of his app available after the April session as will Peter Cole in August