Solved Need help creating a query to calculate "Time Utilisation" (1 Viewer)

tcneo

Member
Local time
Today, 22:57
Joined
Dec 7, 2021
Messages
68
Hi everyone,

I'm trying to create a particular query (so as to output it as a report).

But before I describe the query (Current Issue), let me describe my database first (attached). (note: dummy data is fed into this database)


Tables:

Analyst_Availability: Each record has a date, analyst_ID and number of mins the analyst is available for that day.

Analyst_Details: Analyst_ID, initials, name and email address

Analytical_Method: analytical_method_ID, name of analytical method, type of instrument that this test method uses, duration of test

AnalyticalMethod_for_each_Product: junction table that matches analytical method to each product, each product has one or more test methods.

Competency_Analyst_AnalyticalMethod: junction table that matches analytical method to each analyst, basically, this list the analytical methods that each analyst is qualified to perform

Instrument_Table: Instrument_ID, Instrument Type, name, brand, model serial number

Instrument_Type: Instrument_Type, each type of instrument will have one or more of that type of instrument

Product_Table: Product_ID and names

Task_Detail_Table: Task_DetailID, Task_ID, Date of Task Detail, Analyst assigned, Instrument assigned, Analytical method to be used, quantity of test, status and remarks

Task_Table: Task_ID, Product_ID, Date task is created, Date task is due


General work flow:

Supervisor creates a Task, which is basically specifies which Product is to be tested and when it is due.

Planning staff will then create Task Details. Each Product has to be tested using one or more analytical methods. That is why each Task Detail is mapped to a Task. The analytical method to be used, the instrument to be used and the analyst performing the task is specified in this task detail.


Current issue:

I am not able to create a query that correctly calculates the Time Utilisation for each day.

Time Utilisation is defined as the total time used in that day divided by the total number of available minutes multiplied by 100%.

I am able to use a query (myQuery_for_Analyst_Time_Used) to calculate the total time used by each analyst for each. In the query, I take the sum of the duration of the analytical method multipled by the qty for each analyst.

Now, when I want to calculate Time Utilisation. I thought that I could divide the above sum by the number of mins the analyst is available for that day. But it doesn’t work. It seems like instead of dividing by 500 mins (the amount of time the analyst is available for that day), it divides by 500 * n (where n is the number of task details that analyst is assigned for the day.)

I also tried to average the time spent by each analyst for each date, but Access doesn't allow 2 consecutive "expressions" (sum followed by an average).

My non-working myQuery_for_Time_Utilisation is included in the file.


How to use my file:
  • Open the Main Menu file.
  • Go to Main Tab
  • To see the Task Details, select the Dates in From & Till and click “Detailed Tasks for “Selected Date Range” to see what Task Details are currently availanble (currently, i have created task details for 2 and 3 Dec only)
  • Enter Date and click “Daily Task Report” to see the task details assigned.
  • Go to navigation pane to see the 2 queries mentioned in Current Issue.

As mentioned in my intro post, this is the first database I have created. And I only started using Access last month. I'm sure I have made many mistakes in the database that I create. I thank everyone in advance for reading this post, looking at my database and for your patience and helpfulness!
 

Attachments

  • Test Planner.accdb
    3.1 MB · Views: 330

oleronesoftwares

Passionate Learner
Local time
Today, 07:57
Joined
Sep 22, 2014
Messages
1,159
I get the error"unrecognized database format" when i try to open your file after downloading it.
 

tcneo

Member
Local time
Today, 22:57
Joined
Dec 7, 2021
Messages
68
I get the error"unrecognized database format" when i try to open your file after downloading it.
hmm... i'm not sure why. I created this using Access 2016 if that helps.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:57
Joined
May 7, 2009
Messages
19,169
see the Two new Queries (numbered as precedence).
 

Attachments

  • Test Planner.accdb
    3.1 MB · Views: 339

Users who are viewing this thread

Top Bottom