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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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,687
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,687
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.

 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,269
Messages
5,510,261
Members
408,783
Latest member
MarcianoPL

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top