Divide the total only when the criteria matches

[mahmed1]

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

 

[mahmed1]

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]

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]

Thank You - i think with this data set - it might be better to clean out in excel and then import in

 

[Sahil123]

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]

You can write a delete query to delete all the data in a table:

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.

How to Import an Excel File into Access - Data to Fish

In this guide, I'll show you the full steps to import an Excel file into Access. I'll review an example for demonstration purposes.

datatofish.com

Import or link to data in an Excel workbook - Microsoft Support

You can import Excel data into Access desktop databases or link to an Excel file, which results in a linked table.

support.microsoft.com