Date Tracking

[Alisha]

Hi. I currently have a database (Access 2003) that records workload. It has
numerous tables (approx 32) & queries (about 100) already. It basically
records application information like date received, application number,
applicant, task owner, date completed amongst other things. We currently use
the database to track workload and record monthly statistics. What I have
been asked to do is add a section to this database whereby users can bring up
an application and enter numerous dates. For example the date received may be
yesterday and by today the work is completed for the time being, but next
week I do more work on the application. What I want to be able to do is
effectively have a number of start dates and a number of end dates, have
Access calculate the days inbetween two dates, but also the total days taken
so this information can be used when reporting KPI figures.

Any help would be greatly appreciated

 

[KARL DEWEY]

SELECT [application number], [applicant], [task owner],
Sum(DateDiff("d",[date received], [date completed])+1) AS [Total Work Days]
FROM YourTaable
GROUP BY [application number], [applicant], [task owner];

 

[Ken Sheridan]

It sounds to me like you need a table, SubTasks say, with a foreign key
(ApplicationID say) referencing the primary key of Applications, and
StartDate and EndDate columns, along with any other columns such as
SubTaskDescription say..

To compute the difference between each start and end date is a trivial task
of course, its merely a subtraction of the former from the latter. When you
say 'total days taken' however, do you mean the difference between the
earliest start date and latest end date per application, or the sum of the
differences between each start and end date per application?

In a form an appropriate solution would be to have an applications form, in
single form view and a subtasks form in continuous form view linked on
ApplicationID (or whatever is the key). The subform would, as well as the
bound start and end date controls etc have in its detail section an unbound
computed control with a ControlSource of:

=[EndDate]-[StartDate].

The aggregated total could be in a text box in a footer to the subform with
a ControlSource of either:

=Max([EndDate])-Min([StartDate])

or:

=Sum([EndDate]-[StartDate])

depending on the answer to my question in paragraph 2 above.

You could do similarly in a report grouped by application, putting the
aggregated control in the group footer.

Ken Sheridan
Stafford, England

 

[Alisha]

Firstly thank you for your help. I now have a Table (Tasks), with Application
ID, Application Number, Applicant, Task Desription, Task Start Date, Task End
Date.

I also have a sub form (Applicationssubfrm) with the Application Number,
Task Description, Task Start Date, Task End Date and the Total Days.

The main form (Applicationsmainfrm) has Application Nubmer and Applicant,
then the subform and a footer that I want to calculate the Total Days, but I
keep getting an error (#Error).

The control source for Total Days is =Sum([Days]).

Any further help would be wonderful.

It sounds to me like you need a table, SubTasks say, with a foreign key
(ApplicationID say) referencing the primary key of Applications, and
StartDate and EndDate columns, along with any other columns such as
SubTaskDescription say..

To compute the difference between each start and end date is a trivial task
of course, its merely a subtraction of the former from the latter. When you
say 'total days taken' however, do you mean the difference between the
earliest start date and latest end date per application, or the sum of the
differences between each start and end date per application?

In a form an appropriate solution would be to have an applications form, in
single form view and a subtasks form in continuous form view linked on
ApplicationID (or whatever is the key). The subform would, as well as the
bound start and end date controls etc have in its detail section an unbound
computed control with a ControlSource of:

=[EndDate]-[StartDate].

The aggregated total could be in a text box in a footer to the subform with
a ControlSource of either:

=Max([EndDate])-Min([StartDate])

or:

=Sum([EndDate]-[StartDate])

depending on the answer to my question in paragraph 2 above.

You could do similarly in a report grouped by application, putting the
aggregated control in the group footer.

Ken Sheridan
Stafford, England

Hi. I currently have a database (Access 2003) that records workload. It has
numerous tables (approx 32) & queries (about 100) already. It basically
records application information like date received, application number,
applicant, task owner, date completed amongst other things. We currently use
the database to track workload and record monthly statistics. What I have
been asked to do is add a section to this database whereby users can bring up
an application and enter numerous dates. For example the date received may be
yesterday and by today the work is completed for the time being, but next
week I do more work on the application. What I want to be able to do is
effectively have a number of start dates and a number of end dates, have
Access calculate the days inbetween two dates, but also the total days taken
so this information can be used when reporting KPI figures.

Any help would be greatly appreciated