Group and calculate in a querie, is it possible?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is part of the database I've been talking about for the past few posts.
My colleague uses Access 2003, I have 2002, so please let me know if the
answer may be different based upon which we are using. The actual database is
being created on his 2003 version, but I may test things out with a test
database on my 2002 version.

We have a table containing employee info (names, extensions, etc.), the name
of the supervisor, and their stats. What we want to do is to total a couple
stats for the supervisor, then use the two totals to get an average. In other
words, say supervisor Ben has five employees. In the table, those five
employees will all be listed with their stats, and Ben's name as their
supervisor. We want to be able to add, say TotalCalls for those five reps to
get a total for Ben, then, say TotalSolved for those five reps to get the
total for Ben of that as well. Then, we'd want to divde TotalSolved by
TotalCalls to get the overall percentage of calls Ben's employees solved.

Can this be done in a query? I know it can be done in a report, but we need
to take the data we get and pull it into an Excell spreadsheet. I don't think
a report can translate into an Excell spreadsheet.

To further illustrate my example just suppose this is the table:

Emp Supervisor Total Calls Total Solved
Meep Ben 135 135
Batman Ben 100 50
Superman Ben 150 140

What we would then want is to be able to calculate Ben's total calls to get
385. We'd also want to calculate Ben's total solved to get 325. We'd then
want to divde total solved by total calls to get about 0.844.... Or, 84%
(84.4%, whatever we decide to do with decimals). Is this possible in a query
so it will make it easy to then pull the needed information into an Excell
spreadsheet?

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy
 
YEs it is possible. Do you want the employee name also? That complicates
things, so let's go with the simple solution first.

SELECT Supervisor,
Sum([Total Calls]) as NumCalls,
Sum([Total Solved]) as NumSolved,
Sum([Total Calls])/Sum([Total Solved]) as SuccessRate
FROM [YourTable]
GROUP BY Supervisor

Substitute field and table names as appropriate
 
(Woops. I think I forgot to post this, because it was still here when I
logged out. Sorrt if I actually did not forget and it shows up twice.) Nope.
We don't need employee names, just the supervisors. Thanks! If this works,
you are a life saver. I will probably get back to my colleague some time
today, so I'll get back to you to let you know if it worked. It looks to me
like it should work, though.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy


John Spencer (MVP) said:
YEs it is possible. Do you want the employee name also? That complicates
things, so let's go with the simple solution first.

SELECT Supervisor,
Sum([Total Calls]) as NumCalls,
Sum([Total Solved]) as NumSolved,
Sum([Total Calls])/Sum([Total Solved]) as SuccessRate
FROM [YourTable]
GROUP BY Supervisor

Substitute field and table names as appropriate
This is part of the database I've been talking about for the past few posts.
My colleague uses Access 2003, I have 2002, so please let me know if the
answer may be different based upon which we are using. The actual database is
being created on his 2003 version, but I may test things out with a test
database on my 2002 version.

We have a table containing employee info (names, extensions, etc.), the name
of the supervisor, and their stats. What we want to do is to total a couple
stats for the supervisor, then use the two totals to get an average. In other
words, say supervisor Ben has five employees. In the table, those five
employees will all be listed with their stats, and Ben's name as their
supervisor. We want to be able to add, say TotalCalls for those five reps to
get a total for Ben, then, say TotalSolved for those five reps to get the
total for Ben of that as well. Then, we'd want to divde TotalSolved by
TotalCalls to get the overall percentage of calls Ben's employees solved.

Can this be done in a query? I know it can be done in a report, but we need
to take the data we get and pull it into an Excell spreadsheet. I don't think
a report can translate into an Excell spreadsheet.

To further illustrate my example just suppose this is the table:

Emp Supervisor Total Calls Total Solved
Meep Ben 135 135
Batman Ben 100 50
Superman Ben 150 140

What we would then want is to be able to calculate Ben's total calls to get
385. We'd also want to calculate Ben's total solved to get 325. We'd then
want to divde total solved by total calls to get about 0.844.... Or, 84%
(84.4%, whatever we decide to do with decimals). Is this possible in a query
so it will make it easy to then pull the needed information into an Excell
spreadsheet?

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy
Click to expand...
Click to expand...
 
Wait, now as I am trying it, another question comes into mind... Where do I
enter the code you gave me? I can't find if or where Access actually lets you
enter code to build a query. It just either lets you do it in design view
(which doesn't allow that code to be entered anywhere, because it doesn't
work) or with the wizard. How would I get Access to use that code as the
query?

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy


Paul (ESI) said:
(Woops. I think I forgot to post this, because it was still here when I
logged out. Sorrt if I actually did not forget and it shows up twice.) Nope.
We don't need employee names, just the supervisors. Thanks! If this works,
you are a life saver. I will probably get back to my colleague some time
today, so I'll get back to you to let you know if it worked. It looks to me
like it should work, though.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy


John Spencer (MVP) said:
YEs it is possible. Do you want the employee name also? That complicates
things, so let's go with the simple solution first.

SELECT Supervisor,
Sum([Total Calls]) as NumCalls,
Sum([Total Solved]) as NumSolved,
Sum([Total Calls])/Sum([Total Solved]) as SuccessRate
FROM [YourTable]
GROUP BY Supervisor

Substitute field and table names as appropriate
This is part of the database I've been talking about for the past few posts.
My colleague uses Access 2003, I have 2002, so please let me know if the
answer may be different based upon which we are using. The actual database is
being created on his 2003 version, but I may test things out with a test
database on my 2002 version.

We have a table containing employee info (names, extensions, etc.), the name
of the supervisor, and their stats. What we want to do is to total a couple
stats for the supervisor, then use the two totals to get an average. In other
words, say supervisor Ben has five employees. In the table, those five
employees will all be listed with their stats, and Ben's name as their
supervisor. We want to be able to add, say TotalCalls for those five reps to
get a total for Ben, then, say TotalSolved for those five reps to get the
total for Ben of that as well. Then, we'd want to divde TotalSolved by
TotalCalls to get the overall percentage of calls Ben's employees solved.

Can this be done in a query? I know it can be done in a report, but we need
to take the data we get and pull it into an Excell spreadsheet. I don't think
a report can translate into an Excell spreadsheet.

To further illustrate my example just suppose this is the table:

Emp Supervisor Total Calls Total Solved
Meep Ben 135 135
Batman Ben 100 50
Superman Ben 150 140

What we would then want is to be able to calculate Ben's total calls to get
385. We'd also want to calculate Ben's total solved to get 325. We'd then
want to divde total solved by total calls to get about 0.844.... Or, 84%
(84.4%, whatever we decide to do with decimals). Is this possible in a query
so it will make it easy to then pull the needed information into an Excell
spreadsheet?

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy
Click to expand...
Click to expand...
Click to expand...
 
Woops! Nevermind. I figured it out moments after posting that. LOL!

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy


Paul (ESI) said:
(Woops. I think I forgot to post this, because it was still here when I
logged out. Sorrt if I actually did not forget and it shows up twice.) Nope.
We don't need employee names, just the supervisors. Thanks! If this works,
you are a life saver. I will probably get back to my colleague some time
today, so I'll get back to you to let you know if it worked. It looks to me
like it should work, though.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy


John Spencer (MVP) said:
YEs it is possible. Do you want the employee name also? That complicates
things, so let's go with the simple solution first.

SELECT Supervisor,
Sum([Total Calls]) as NumCalls,
Sum([Total Solved]) as NumSolved,
Sum([Total Calls])/Sum([Total Solved]) as SuccessRate
FROM [YourTable]
GROUP BY Supervisor

Substitute field and table names as appropriate
This is part of the database I've been talking about for the past few posts.
My colleague uses Access 2003, I have 2002, so please let me know if the
answer may be different based upon which we are using. The actual database is
being created on his 2003 version, but I may test things out with a test
database on my 2002 version.

We have a table containing employee info (names, extensions, etc.), the name
of the supervisor, and their stats. What we want to do is to total a couple
stats for the supervisor, then use the two totals to get an average. In other
words, say supervisor Ben has five employees. In the table, those five
employees will all be listed with their stats, and Ben's name as their
supervisor. We want to be able to add, say TotalCalls for those five reps to
get a total for Ben, then, say TotalSolved for those five reps to get the
total for Ben of that as well. Then, we'd want to divde TotalSolved by
TotalCalls to get the overall percentage of calls Ben's employees solved.

Can this be done in a query? I know it can be done in a report, but we need
to take the data we get and pull it into an Excell spreadsheet. I don't think
a report can translate into an Excell spreadsheet.

To further illustrate my example just suppose this is the table:

Emp Supervisor Total Calls Total Solved
Meep Ben 135 135
Batman Ben 100 50
Superman Ben 150 140

What we would then want is to be able to calculate Ben's total calls to get
385. We'd also want to calculate Ben's total solved to get 325. We'd then
want to divde total solved by total calls to get about 0.844.... Or, 84%
(84.4%, whatever we decide to do with decimals). Is this possible in a query
so it will make it easy to then pull the needed information into an Excell
spreadsheet?

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy
Click to expand...
Click to expand...
Click to expand...
 
Awesome! I was just able to test this out with my colleague, and it seems to
be working. Thanks a million! Wow! I just realized that this makes four
replies in a row that are all from me. LOL. I didn't do that on purpose, I
just kept having things to add.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy


Paul (ESI) said:
Woops! Nevermind. I figured it out moments after posting that. LOL!

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy


Paul (ESI) said:
(Woops. I think I forgot to post this, because it was still here when I
logged out. Sorrt if I actually did not forget and it shows up twice.) Nope.
We don't need employee names, just the supervisors. Thanks! If this works,
you are a life saver. I will probably get back to my colleague some time
today, so I'll get back to you to let you know if it worked. It looks to me
like it should work, though.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy


John Spencer (MVP) said:
YEs it is possible. Do you want the employee name also? That complicates
things, so let's go with the simple solution first.

SELECT Supervisor,
Sum([Total Calls]) as NumCalls,
Sum([Total Solved]) as NumSolved,
Sum([Total Calls])/Sum([Total Solved]) as SuccessRate
FROM [YourTable]
GROUP BY Supervisor

Substitute field and table names as appropriate

Paul (ESI) wrote:

This is part of the database I've been talking about for the past few posts.
My colleague uses Access 2003, I have 2002, so please let me know if the
answer may be different based upon which we are using. The actual database is
being created on his 2003 version, but I may test things out with a test
database on my 2002 version.

We have a table containing employee info (names, extensions, etc.), the name
of the supervisor, and their stats. What we want to do is to total a couple
stats for the supervisor, then use the two totals to get an average. In other
words, say supervisor Ben has five employees. In the table, those five
employees will all be listed with their stats, and Ben's name as their
supervisor. We want to be able to add, say TotalCalls for those five reps to
get a total for Ben, then, say TotalSolved for those five reps to get the
total for Ben of that as well. Then, we'd want to divde TotalSolved by
TotalCalls to get the overall percentage of calls Ben's employees solved.

Can this be done in a query? I know it can be done in a report, but we need
to take the data we get and pull it into an Excell spreadsheet. I don't think
a report can translate into an Excell spreadsheet.

To further illustrate my example just suppose this is the table:

Emp Supervisor Total Calls Total Solved
Meep Ben 135 135
Batman Ben 100 50
Superman Ben 150 140

What we would then want is to be able to calculate Ben's total calls to get
385. We'd also want to calculate Ben's total solved to get 325. We'd then
want to divde total solved by total calls to get about 0.844.... Or, 84%
(84.4%, whatever we decide to do with decimals). Is this possible in a query
so it will make it easy to then pull the needed information into an Excell
spreadsheet?

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top