Search results

Hi, this is just for knowledge building purposes. I was looking to find out how i can write the following formula in Access. =COUNTIF($C$2:$C$9,$C2) I have attached a sample xls file, but wanted to test something like this in access. I understand the first part where I would write an IIF...

thanks guys for all your help. gemma-the-husky managed to give me the correct answer ;). Really appreciate it. the following is the formula that i used in my query: Query: IIf(nz([Europe],0)>0 And nz([UK],0)>0,"Neither",IIf(nz([Europe],0)>0,"Europe","UK"))

SOS, MSAccessRookie, I have tried both but they still dont get to the second IIF Argument as with the Formula that MSAccessRookie added it puts every1 as Europe even if it has figures in the UK Column? Where is this going wrong? Thanks once again btw...

SOS, that didnt work, but i have managed to change it to: Expr1: IIf([UK]>0 And [Europe]>0,"Neither",IIf([Europe]=0,"UK","Europe")) but it seems to fail on the IIf([Europe]=0 as the 0 values are actually BLANKS. Would that make a difference?

No, there have numbers in the fields as im trying add an extra column that will identify if they belong either one of the areas or both.

SOS, Thanks for your quick response.:) I have tried that query, but this gives me a "compile error, in query expression 'Switch([Europe]=0,"UK",[UK]=0,"Europe",[Europe]>0 And [UK]>0,"Neither") Any ideas why?

Hi, I am trying to write a query with 2 IIF arguments but it doesnt work. I have managed to get the first argument to work which will check to see if Europe = 0 and UK = 0 then this will put the value "Neither" (I put the and between 0 and [UK] to make it work). i have put the second IIF...

I am looking to write an Excel SumProduct but within a database and was wondering if somebody help guide me to any tutorials or help me to understand the basics of converting one to a database query. Thanks in advance.

lol... iv actually found the solution within the SQL.

Just a quick question, I have created a make table query and when this is ran it will overwrite my table "Table1". I now want to copy this query but want it to create a table called "Table2" now but how would i change this in the query? Could somebody please help. I know i would be able to...

Brian, thanks for that, worked brilliantly, just tried it now, been away for most of the day...

DCrake, I have ran the 2 queries you pasted, but this does not rank the times for each queue and ranks all the times as a whole. Just to make sure the "ABS" part of the query was the actual time right?

thanks for that dcrake.

DCrake, i have wrote the following: SELECT AHTVolData.[Agent AHT for this Q], DCount("*","AHTVolData","[Agent AHT for this Q]<=" & [Agent AHT for this Q]) AS Rank FROM AHTVolData GROUP BY AHTVolData.[Agent AHT for this Q], DCount("*","AHTVolData","[Agent AHT for this Q]<=" & [Agent AHT for...

vbaInet, thats correct I will be ranking on the time field...

Thanks for your response DCrake. I have attached a sample spreadsheet which shows the excel formula that I have used alongside the Access Ranking Formula. Thanks once again.

Re: Access Rank Query Ok, I have now managed to write an SQL Query in Access in order to rank data. The following is the query I have written: SELECT a.[Q Name], a.[Q Time], Count(*) AS rank FROM (SELECT [Q Name], [Q Time] FROM Table1 GROUP BY [Q Name], [Q Time]) AS a INNER JOIN (SELECT...

I have the following query in Excel that I use to rank points against a queue: =SUMPRODUCT(--($G$4:$G$4431=$G4)*($J4>$J$4:$J$4431))+1 Column G = Queue Name Column J = Points the query above is what i have in column 4. What I would like to do is run a query in access that would do the same...

Hi, I am writing a couple of macros in Access 2007 to add data within a table and empty the data from the table as well. I have managed to write a "TransferSpreadsheet" to add the data but don't know how to write one to empty the table (leaving the headers in). Would i need to write a query...

Thanks pbaldy, my error. just for future purposes and for building my knowledge, if i wanted to do the same with a manager name besides the agent name, im guessing i would have to add another subquery right? (SELECT DISTINCT ID, [Agent Name] FROM Table1) AS D (SELECT DISTINCT...