ms access query to find open tickets in queue each friday

I have a table with service ticket #, Enter Date, and Closed date.

What I am trying to query is the quantity of open tickets (tickets entered not closed) for each week.

So If a ticket was entered on last Monday but was closed any time during the week - it would not be counted.

If a ticket was entered on last Monday - is was not closed by Friday - it would count as 1 Ticket in Queue for that week.

I am not sure how to structure the query to return records per week with number of Open tickets in Queue.

Any suggestions?

Thanks for your help.
johnmadiganAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Does a "week" include weekends...

And to be clear, are we talking about a Sunday through Saturday "week", ...or the "Week Number" as defined as each "week" in the year.
0
Rey Obrero (Capricorn1)Commented:
try this query


SELECT DatePart("ww",[EnterDate]) AS WeekNumber, Count(DatePart("ww",[EnterDate])) AS NumOfTicketsEntered, Count(DatePart("ww",[ClosedDate])) AS NumOfTicketsClosed, [NumOfTicketsEntered]-[NumOfTicketsClosed] AS CountOfOpenTickets
FROM YOURTABLE
GROUP BY DatePart("ww",[EnterDate])
0
johnmadiganAuthor Commented:
Yes Sunday to Saturday will work.

I have been working with DatePart - to get the week number and year.  So I can convert the Enter date and Closed date to the week numbers.

I can do a query to eliminate where the tickets were open and closed in the same week.

Having trouble figuring out how to account for tickets that stay open multiple weeks?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

johnmadiganAuthor Commented:
Rey,

That worked - is there a way to convert the WeekNumb column to the saturday date?  I have to move these numbers to an existing spreadsheet with saturday dates.

Thanks,
0
johnmadiganAuthor Commented:
I was looking a little closer and I noticed it does not account for tickets that span a week:

The query returned for week 4 - 80 tic entered, 74 closed = 6 open - that works but ...

Week 5 - 75 tic entered, 73 closed  ,,, showing 2 open  - this include the 6 from week 4 and the 2 from week 5 for a total of 8 open tickets.

Does this make sense?
0
johnmadiganAuthor Commented:
I have a ticket# 813 that was entered in on 1.21.2015 and was never closed.  So I need to count this ticket for each of the following weeks since this is an open ticket (would count as 1 each week since it is still open)

Not sure how to account for this?

Thanks,
0
Rey Obrero (Capricorn1)Commented:
here is the query with a Saturday date

SELECT [EnterDate] -7 + Weekday([EnterDate]) AS SatDate, Count(DatePart("ww",[EnterDate])) AS NumOfTicketsEntered, Count(DatePart("ww",[ClosedDate])) AS NumOfTicketsClosed, [NumOfTicketsEntered]-[NumOfTicketsClosed] AS CountOfOpenTickets
 FROM YOURTABLE
 GROUP BY [EnterDate] -7 + Weekday([EnterDate])

as for your other problem, you will need VBA user define function to do that.
and I suggest that you post another question for that. It will be better to add a sample db in that question.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.