Count with multiple criteria (3) on a query (1 Viewer)

[Garuban]

Hi All,

I've been trying to create a formula that can count based on 3 criteria, until now this is the formula that I got but is showing me blank cells

DCount("*","TblGP Data","EmployeeName = '" & [EmployeeName] & "' and Recruiter = '" & [GP Data].[Recruiter] & "' and [Week End Date] =# " & Format([Week End Date],"mm\/dd\/yyyy") & "#")

EmployeeName is text
Recruiter is text
Week End Date is date

Basically I need the calculated field to show me how many times these 3 values from the row evaluated get repeated TOGETHER in the whole query,

A good example is what I had on excel, since that's what I'm trying to replicate on access, where the 3 values gets concatenated and then I used a countif (1/the formula),

This is what I'm trying to replicate on access:

Thanks!

 

[cheekybuddha]

Where are you trying to use this value?

 

[Garuban]

Where are you trying to use this value?

On a calculated field on a query

 

[cheekybuddha]

SELECT
  EmployeeName,
  Recruiter,
  [Week End Date],
  COUNT(*) AS Occurences
FROM [TblGP Data]
GROUP BY
  EmployeeName,
  Recruiter,
  [Week End Date]
ORDER BY
  EmployeeName,
  Recruiter,
  [Week End Date]
;

 

[plog]

Divide, conquer, isolate and just get something to work.

DCount("*","TblGP Data")

Does the above return a value? If so, add 1 of the crtierion. Does that work? If so add another criteriion, if not, find out what about that criterion causes it to fail.

 

[Garuban]

Divide, conquer, isolate and just get something to work.

DCount("*","TblGP Data")

Does the above return a value? If so, add 1 of the crtierion. Does that work? If so add another criteriion, if not, find out what about that criterion causes it to fail.

That gives me an error

 

[cheekybuddha]

Did you try the query I posted?

 

[cheekybuddha]

Also, is your table named 'TblGP Data' or 'GP Data' ?

 

[Garuban]

Also, is your table named 'TblGP Data' or 'GP Data' ?

Yeah the table is 'GP Data', the tbl is because I saw some solution in this forum and it was like that.

Regarding your solution, it works almost perfectly lol, because it shows me 1 line with the occurrences and how many occurrences are in the table, but for my query I need it to show me for instance if there are 12 occurencies, 12 lines with the number 12. Maybe that's caused by the line 'grouped by'

I really appreciate your help haha, I'vebeen trying to solve this for a while now

 

[cheekybuddha]

Ok, I see.

So try your original DCount expression which looked ok, but change to use the real table name

 

[Garuban]

Ok, I see.

So try your original DCount expression which looked ok, but change to use the real table name

Divide, conquer, isolate and just get something to work.

DCount("*","TblGP Data")

Does the above return a value? If so, add 1 of the crtierion. Does that work? If so add another criteriion, if not, find out what about that criterion causes it to fail.

So removing tbl from the equation starts giving me values but if I add the first criteria the Query gets stuck loading for some reason and access crashes

 

[plog]

What does the DCount look like with that first criterion?

 

[Garuban]

What does the DCount look like with that first criterion?

When I ran this it showed me some values:

DCount("*","GP Data")

And this is how it looks with the first criterion:

DCount("*","GP Data","[EmployeeName] = '" & [EmployeeName] & "'")

 

[plog]

The divide/conquer/isolate process is working. You've now isolated 1 part of the longer code that has a problem.

1. Does GP Data have a field called [EmployeeName]?

2. Does the query you are running this code in have a field called [EmployeName] available to it?

Hint, one of those 2 is not true. Look carefully and don't assume you know the answer.

 

[Garuban]

The divide/conquer/isolate process is working. You've now isolated 1 part of the longer code that has a problem.

1. Does GP Data have a field called [EmployeeName]?

2. Does the query you are running this code in have a field called [EmployeName] available to it?

Hint, one of those 2 is not true. Look carefully and don't assume you know the answer.

Both seem to be true, the EmployeeName field on the query is pulled directly from the gp data table

 

[cheekybuddha]

Also, please post the whole SQL of your query (that crashed)

 

[plog]

Can you post a copy of your database with just the elements we need?

 

[Garuban]

Can you post a copy of your database with just the elements we need?

With reduced data it seems to work...

 

[plog]

New theory--you got an Irishmen gumming up the works. If I add a record with the EmployeeName of:

O'Toole, James

Your query throws an error in the DCount because of that apostrophe. My guess is your full database has one of those names. The way to fix it is to wrap both [EmployeeName] fields in your Dcount with Replace(https://www.techonthenet.com/access/functions/string/replace.php) to remove the apostrophes.

DCount("*","GP Data","Replace([EmployeeName], "'", "") = '" & Replace([EmployeeName], "'", "") & "'")

 

[Josef P.]

You can leave the apostrophes. They only have to be doubled in the string.

DCount("*","GP Data","[EmployeeName] = '" & Replace([EmployeeName], "'", "''") & "'")

Does the query need to remain editable?
If not, I would use a subselect instead of DCount, then you don't have to bother with text composition.

SELECT
     [GP Data].EmployeeName, [GP Data].[Week End Date]
     , (select count(*) from [GP Data] as X where X.EmployeeName = [GP Data].EmployeeName) AS [RecCount]
FROM
     [GP Data]