Combing an employee list with a survey count

[TammyT]

Background:
Main table - has all of the employee demographic data.

Survey table - store surveys done on random employees throughout the month; may not contain a survey for each employee; some employees may have several surveys done in 1 month.

I need to create a report that shows all employees & how many surveys were done on them for a month (or other given time period). The basic report for counting is not a problem; my problem is displaying the names of employees who did not have surveys, hence it would show as 0; since there are no records for some employees, they would not normally be included in the query that drives the report.

Any ideas? Am I asking the wrong forum?

Thanks!

 

[Crowley16]

in the query that drives the report, you will have to include ALL employees, and on the report, you then just put 0 against NULL Survey fields...

Look at the LEFT/RIGHT JOINs...
or in the query builder double click on the link between the 2 tables...

--------------------
Procrastinate Now!

 

[TammyT]

I'm not understanding your answer - I know I need to include all employees; however, when I use both tables in the query I still only get the listed those employees who have survey records for that month. There are no NULL survey fields if a person doesn't have any surveys. ??

 

[Crowley16]

then you are NOT including all employees...

I'm assuming you're using the query builder...

double click on the link between the 2 tables, and you should see a form come up, in the bottom 1/2 of the form, there's 3 options, select records from both tables where condition is satisfied, select all records from tbl1 and only those from tbl2 where condition is satisfied, and all from tbl2...

choose the relevant option...

--------------------
Procrastinate Now!

 

[TammyT]

Okay - now I follow you (I had built it in Design View, & was modifying) - I've tried all combos, & none are printing all of the employee records.

I was wondering if I needed to create a query with all of the employees, & then take the survey query & put it inside of the employee query (drag the * down, not each field) - what do you think?

 

[Crowley16]

no need to do that, you've already got all employees in your employee table, adding further queries will only slow things down...

--------------------
Procrastinate Now!