Divide the total only when the criteria matches

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
Hi

I have this code to calculate my conc rate

ROUND((SUM(t.[CUMULATIVE ENGAGED CHAT TIME])/SUM(t.[ENGAGED TIME])),2) AS Concurreny_Rate

What ive just realised is that the ENGAGED TIME could he duplicate times in there so i need to only take the 1 time

IF The Agent and Hour is the same only sum 1 of those ENGAGED Times (There could be the same times and agents but the Skill could be different) - This is throwing out my total engaged times

How can i get around this

Thank YOu
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,146
As an example

if the data was like this

datetime, Agent, skill, Cumalative Engaged Time, Engaged Chat Time

20/07/2020 08:00, Agent1, CD, 02:00, 01:10:00
20/07/2020 08:00, Agent1, CQ, 01:00, 01:10:00

As the Agent and datetime is the same (skill different) the formula should be 03:00/01:10:00 (So only take 1 of the Engaged time)


ROUND((SUM(t.[CUMULATIVE ENGAGED CHAT TIME])/SUM(
IF(Agent Column and DateTime Column = same then take 1 of the Engaged Time),2) AS Concurreny_Rate

I can do this in Excel where i put 0’s in that column before i import but that wouldmean chaging the database

hoping i can do that in Access in that formula
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,675
Office Version
2013
Platform
Windows
Hi the general purpose solution is to write a query first to get the records you are interested in summing or averaging. That would be step one. Then you write a query that uses the first query as the data source and do your actual summing or averaging. That would be step two.

In this case I don't really see how you will proceed precisely. Data looks a little ugly. But anyway, the two part approach as described above is basically the way to go (and it is really part one that is the key part as the next part will be your current query basically).
 

Sahil123

Well-known Member
Joined
Oct 31, 2011
Messages
673
Thank You - i think with this data set - it might be better to clean out in excel and then import in
 

Sahil123

Well-known Member
Joined
Oct 31, 2011
Messages
673
Only probs i have at the moment is that i have a table that has got several months of data that is already stored in access so for now would have been nice to clean existing table in access and then going forward import in clean data

is there a quick way of deleting all the data and then after copying into excel and then copy and paste the data from excel to access (copy and paste)
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,675
Office Version
2013
Platform
Windows
You can write a delete query to delete all the data in a table:
Code:
delete * from Table1;
Copy and paste I think works (I don't use that a lot but I'm pretty sure I have done it). Also you can use the Import from Excel feature which is my goto method.

 

Watch MrExcel Video

Forum statistics

Threads
1,102,565
Messages
5,487,595
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top