Query: count appearances in another query

[epardo87]

Hello,



So I have two queries:

1. [qryStatusPerEmployee]
   
   • [FullName]
   • [Goal]
   • [Valid]
   
   
2. [qryValidCertifications]

• [FullName]
• [Station]
• [Status]

So in query #1 you have the list of employees, with the goal of certifications to have and in query #2 you have the list of valid certifications, where a single employee can appear several times, because he can be certified in different stations, the status already filters the valid certifications from the expired ones.

I would like to have in the [valid] column on query #1, the count of how many times that employee appears in the second query, so that I obtain the number of valid certification each employee has.

Thanks for your time in advance!

[Bulzie]

You should use an autonumber or other unique number as the PK for the employee, if someone has same name it would cause issues. Can you give an example of data in both tables and what you want the final to look like?

[epardo87]

Thanks, I do have a unique number for each employee in the original table, but I concatenated the full name (name, father's lastname and mother's lastname) so it is very unlikely to have the full name duplicated, nevertheless, I'll begin to use it, because, you may never know right?

well, actually I managed to figure it out: I created a third query, placed twice the [FullName] from the 2nd query, totaled it and changed the second column to count, that gave me the number of times each employee appears in the valid certifications. then I included that column in the first query and it works fine!

Thanks!

[Bulzie]

Awesome, good job !!