INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Microsoft Access - SQL Coding Question

Microsoft Access - SQL Coding Question

(OP)
Good Morning!
I'm new to SQL coding and am trying to find a way to run a query and look to need some help
Table: Last Month Incidents
Used Columns:Assignment Group, Business duration
Issue:I have a table populated by incident tickets along with a field for Business Duration that shows how long they've been opened. I am trying to run a query that groups by the Assignment Group and gives counts for Total Completed, Same Day Completions, Within 3 Days Completions, etc. So far, I've come up with:

SELECT [Last Month Incidents].[Assignment Group], Count(*) AS MonthlyCount,
(SELECT COUNT(*) FROM [Last Month Incidents] WHERE [Last Month Incidents].[Business duration] >432000) AS Over5Days
FROM [Last Month Incidents]
GROUP BY [Last Month Incidents].[Assignment Group];


This gives me Total Completed by each group, but then give me the same number for "Over5Days" in all groups that represents the amount of tickets open for all days for ALL groups. I don't know of a way to keep the "Over5Days" counts specific to their Assignment Groups as well. Is this possible?

RE: Microsoft Access - SQL Coding Question

Hi,

WOW! You have a table of Last Month Incidents that has a field named Business Duration that is populated in SECONDS, in this particular case 432,000 seconds,...

Oops, the clock's ticking. So was that 5 day equivalent calculated at the beginning of August or at the end of August or somewhere in the middle or, to quote an infamous politician, "What difference does it make!"

This sure sounds like a homework assignment to me. Can you state the business case for this exersize?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Microsoft Access - SQL Coding Question

(OP)
Hey Skip,

I'm actually refining the reporting we do by creating an Access Database that will calculate certain numbers that we regularly report on. I pull the information from out ticketing system which gives us Business Duration in seconds neutral 5 days is our SLA on these tickets. By pulling the amount of tickets that took longer than 5 days to close, I'm able to report on what percentage of our tickets we are completing within SLA vs outside of SLA.

To go a step further, I'd include pulling same-day closures, etc to give us more of an idea on how we are doing as a group.

The Business Duration is the total amount of time that a ticket is open before being resolved. Thanks in advance for any assistance!

RE: Microsoft Access - SQL Coding Question

(OP)
The example in my coding is me trying to add a column that will give me the total amount tickets closed by that group that exceeded 5 days opened. The Over5Days is giving me the amount of tickets Over5Days for all groups, not each individual group.

RE: Microsoft Access - SQL Coding Question

(OP)
I can do this by creating new fields and altering data, but since others may need to fill in for me, I'd like to keep this as seamless as possible, so I'm willing to do more work (SQL coding) to minimize manual work.

RE: Microsoft Access - SQL Coding Question


CODE

SELECT [Assignment Group], Count(*) AS MonthlyCount
FROM [Last Month Incidents]
WHERE [Business duration] >432000
GROUP BY [Assignment Group]; 

Why wouldn't that work?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Microsoft Access - SQL Coding Question

(OP)
Yea, that works. I guess I was just hoping to get that output, Total amount closed, and then other "WHERE" statements all in one query. I'd end up using 5 different queries for Totals, same day closures, 3 day closures, 5 day closures.

RE: Microsoft Access - SQL Coding Question

If it were me, I'd import the Last Month Incidents table into an Excel workbook via a query in Excel that can be refreshed each month by command, and construct a report table that would calculate the stats.

I assume that your management wants to see a composite report anyhow.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Microsoft Access - SQL Coding Question

(OP)

CODE -->

SELECT
[Last Month Incidents].[Assignment Group],
Count(*) AS MonthlyCount,
Sum(IIf ([Last Month Incidents].[Business duration] >432000,1,0)) AS Over5Days
FROM [Last Month Incidents]
GROUP BY [Last Month Incidents].[Assignment Group]; 

I actually heard back from a friend that figured it out. The above code as able to give me counts based of a calculation while keeping the count grouped by Assignment Group.

RE: Microsoft Access - SQL Coding Question

What you should have done is post a sample of your data with a statement of the results you would expect. Otherwise we're just speculating.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Microsoft Access - SQL Coding Question

(OP)
Will do next time. I thought I explained it well enough, along with the sample coding.

RE: Microsoft Access - SQL Coding Question

I would think that a bunch of IIFs you might be able to calculate all your stat elements.

But I would forget your last month table a query against your master data. That way, you might also be able to generate trends and comparison stats.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close