Help with Access Rolling Aggregate Based on Single Column w/ Multiple Values Query

[Surjeet729]

I need help with an Access query.

The request is for a rolling average in days that a ticket is in a given status.
IDs are set to auto-increment, so the same ticket will have multiple IDs for different months. I've been working with Ticket_Name for this reason.
The set-up is below:

Date | Ticket_Name | Status | Prior Status | Creation_Date | Last_Modified_Date | Status Length in Days

8/21 AB Open Open 6/21 7/21 (missing, need)
8/21 BC Open Pending Date_Field Date_Field
8/21 CD Pending *Blank*
9/21 AB In Review Open
9/21 BC Closed Open
9/21 CD Open Pending
10/21 AB Closed In Review
10/21 BC Closed Closed
10/21 CD Open Open

For reporting purposes the ultimate goal is:

Status | Average time spent in status in days
Pending 7
Open 45
In Review 10
Closed ---

I've made some progress subtracting the ticket date from current date to create a "count of days" column and grouping the tickets by which ones equal each other. I have yet to successfully integrate any grouping of statuses into this query.

I think a partition by or subquery is required. A more efficient processing option if possible is preferred, as some options I've tried make Access stall out.

Any help is greatly appreciated! Thank you.

 

[plog]

I can't see what the answers should be so can you redo your data and provide 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you query should produce when you feed it the data from A.

Again, 2 sets of data--A & B, starting data and expected results based on A.

 

[theDBguy]

Hi. Welcome to AWF!

A rolling average of how many days for each ticket status? Is that what you meant?

So, if a ticket is open, it would be 100% on the first day and then 50% on the second day, and so on?

 

[Surjeet729]

Yes, a rolling count of days for each ticket status. By average, they're looing for the average time all tickets are in a given status. So average for "Pending" might be 14, average for "Open" 45 days etc.

 

[theDBguy]

Yes, a rolling count of days for each ticket status. By average, they're looing for the average time all tickets are in a given status. So average for "Pending" might be 14, average for "Open" 45 days etc.

Based on your sample data, I am not sure you have enough info to calculate a rolling average. Instead, you might be able to simply calculate the elapsed time (number of days) a ticket was in one status until that status changed.

 

[Surjeet729]

Agreed, and that would be a good first step!

 

[theDBguy]

Agreed, and that would be a good first step!

Okay, just to clarify, does your table only record status changes? It doesn't record a daily status, correct?

In other words, for the same ticket name, you won't have an entry everyday unless the status changed each day, right?

 

[June7]

Advise to use the table builder on the post edit menu to provide sample data or copy/paste from Access table.

 

[Surjeet729]

I can't see what the answers should be so can you redo your data and provide 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you query should produce when you feed it the data from A.

Again, 2 sets of data--A & B, starting data and expected results based on A.

A. Current Data: (also has an ID column, however the same ticket may have multiple IDs. There is a creation_date column that say when the ticket was initially made and a last_modified date as well.) I'm not 100% confident that the last_modified date corresponds to status, although there's a good likelihood there's correlation between those fields.

Date | Ticket_Name | Status | Prior Status | Creation_Date | Last_Modified_Date

8/21 AB Open Open 6/21 7/21
8/21 BC Open Pending
8/21 CD Pending *Blank*
9/21 AB In Review Open
9/21 BC Closed Open
9/21 CD Open Pending
10/21 AB Closed In Review
10/21 BC Closed Closed

B. Expected Results: This has some creative license.

It could be this to start:

Date | Ticket_Name | Status | Prior Status | Status Length in Days

8/21 AB Open Open 34
8/21 BC Open Pending 26
8/21 CD Pending *Blank* 5
9/21 AB In Review Open 7
9/21 BC Closed Open 4
9/21 CD Open Pending 30
10/21 AB Closed In Review 4
10/21 BC Closed Closed 34
10/21 CD Open Open 60

For reporting purposes the ultimate goal is:

Status | Average time spent in status in days
Pending 7
Open 45
In Review 10
Closed ---

 

[Surjeet729]

Okay, just to clarify, does your table only record status changes? It doesn't record a daily status, correct?

In other words, for the same ticket name, you won't have an entry everyday unless the status changed each day, right?

The table tracks monthly statuses for each ticket. It pulls data from monthly reports.

 

[plog]

How do you arrive at 34 for the first record?

8/21 AB Open Open 34

It's creation date was 6/21 and last modified date was 7/21 neither is exactly 34 days prior to 8/21. Nor do you have a prior record.

I need precise, accurate and all-encompassing example data to discern the rules for this. That probably means you spend half an hour manually computing the B data I need, but in the long run doing it this way will save a lot of time over trying to use words to achieve the same thing.

 

[June7]

Or provide database. Follow instructions at bottom of my post.

Did you see post #8?

 

[plog]

Actually, I think I got it. Attached is a database that accomplishes what you want. You'll have to tweak it to your table--you never gave me that name, and [Date] is a poor choice for a field name so I used Status_Date.

Here's how it works. I wrote a subquery to find the next ID of each Ticket_Name--AB 8/21 finds AB 9/21, AB 9/21 finds AB 10/21. Then it calculates the days between the records date and the next record that way you will know how long it was in that status. It then averages all the status days and gives you your final result.

Notes:

Prior_Status is not used in this, nor should it be stored. It's a value you can calculate so it shouldn't be in your table.

If there is no Next record for a record it uses todays date. That means most likely you want to filter out the Closed records because something closed last year just keep racking up days in that status.

If you have 2 consecutive records with the same status that will throw off the average, e.g Open, Open. They will be treated like 2 seperate instances instead of being added together. You would fix this by diving into the SQL in sub1 and adding a criteria to the inner sub query of NextID and making sure the current records status is different than the Next record's status.

If a record reverts back to a prior status--e.g. Open, then Pending, then Open again, it will count those 2 different open instances as different records when calculating the average instead of adding them together. I don't know if that's what you want, but that's how it will work.

Give the database a test with some of your data to make sure it works. If it doesn't, provide me with the sampe data you use so I can see where and how it fails.

 

[Surjeet729]

Actually, I think I got it. Attached is a database that accomplishes what you want. You'll have to tweak it to your table--you never gave me that name, and [Date] is a poor choice for a field name so I used Status_Date.

Here's how it works. I wrote a subquery to find the next ID of each Ticket_Name--AB 8/21 finds AB 9/21, AB 9/21 finds AB 10/21. Then it calculates the days between the records date and the next record that way you will know how long it was in that status. It then averages all the status days and gives you your final result.

Notes:

Prior_Status is not used in this, nor should it be stored. It's a value you can calculate so it shouldn't be in your table.

If there is no Next record for a record it uses todays date. That means most likely you want to filter out the Closed records because something closed last year just keep racking up days in that status.

If you have 2 consecutive records with the same status that will throw off the average, e.g Open, Open. They will be treated like 2 seperate instances instead of being added together. You would fix this by diving into the SQL in sub1 and adding a criteria to the inner sub query of NextID and making sure the current records status is different than the Next record's status.

If a record reverts back to a prior status--e.g. Open, then Pending, then Open again, it will count those 2 different open instances as different records when calculating the average instead of adding them together. I don't know if that's what you want, but that's how it will work.

Give the database a test with some of your data to make sure it works. If it doesn't, provide me with the sampe data you use so I can see where and how it fails.

Thank you so much!! I'll test this out!