Create a form - that lists all current employees and Dcount of Activity

[crimedog]

I tried Continuous form that lists (from Query) all current Employees. Then unbound Text box with DCount formula.



I have Table Employees and Table Activity

I have a Query1 that lists activity for current month
I have a query2 that lists current employees (ExitDate Is Null).

I want a form that - lists all the current Employees (Query2)
And a count from Query1 (if there are no records for that employee the 0)

The reason I would like it on a continuous form: I don't want to have to edit the form design every time there is a new employee or an employee leaves.


Thanks

[June7]

Why would a non-continuous form require editing form design when employees come/go?

Post your attempted DLookup expression. Why does it not work, what happens - error message, wrong results, nothing?

If you want to provide db for analysis, follow instructions at bottom of my post.

[crimedog]

=DCount("[AccountName]","Q2ActivityTeam","[TypeDescription]='Call'")

On the continuous form - this counts the calls for all the employees in the Query

=DCount("[AccountName]","Q2ActivityTeam","[TypeDescription]='Call' AND [PersonnelName]=" & [TxtPersonnelName])

Returns #Error - Q2ActivityTeam contains [PersonnelName] I named the Text box on the form [TxtPersonnelName]

[June7]

Parameters for text type field need apostrophe delimiters.

=DCount("[AccountName]","Q2ActivityTeam","[TypeDescription]='Call' AND [PersonnelName]='" & [TxtPersonnelName] & "'")

Really should search on personnel ID not name because names make poor unique identifiers. What if you have multiple Jane Smith?

Name parts should be in separate fields: FName, MName, LName, Prefix, Suffix

[crimedog]

Yeah, I am looking into someone else's SQL - but should have keyed it to the PersonnelID

And that worked (the '" & "'") - How would you do it without a continuous form?

Thanks for the help!

[June7]

How are you doing it on Continuous form - textbox in form footer section? Do the same on Single View form.

Would not work for Datasheet View.