counting three columns then sum those counts per employee

[hollis via AccessMonster.com]

I am counting fields to track and reward employee productivity. These are
date type fields. The associate enters a date for up to three different tasks
they provide each record. They get credit for each date and can have
anywhere from 0 to 3 entries.

My first select query SQL statement is:

SELECT Audit.[1st Action], Audit.[2nd Action], Audit.CompDate, AssocID.
AssocName, AssocID.AssocInitials
FROM AssocID INNER JOIN Audit ON AssocID.AssocID = Audit.Assoc
WHERE ((Month([1st Action])=Month(Date())) AND ((AssocID.AssocInitials)=
[Enter Associates Initials:])) OR ((Month([2nd Action])=Month(Date()))) OR (
(Month([CompDate])=Month(Date())));

I have created a second sub-query to count these records:

SELECT Count(qryProdByAssoc.[1st Action]) AS [CountOf1st Action], Count
(qryProdByAssoc.[2nd Action]) AS [CountOf2nd Action], Count(qryProdByAssoc.
CompDate) AS CountOfCompDate
FROM qryProdByAssoc;


1. I would like the first query to default the previous month/year.
2. I would like the second query to sum the three columns and then to append
to a Productivity table the sum, month/year, and associate initials.

Q 1. If it is possible, how would you select, count, and sum in one query?
Q 2. If you can not do Q1, should I create two more queries...one to UNION
what I want from qry1 with qry2...then another to APPEND that UNION data to
the Productivity table?
Q 3. If I remove the parameter in qry1 that asks for the associates initials,
how should I get the sum of each associate's total count for the month?

I first tried a cross tab query wizard but it did not provide the complete
results I needed as I could only choose one of my date fields not all of them.


Thank you in advance for any help!

 

[Duane Hookom]

I would not spend any additional time on this until the table(s) were
normalized. Each employee action should create a new record in a table
rather than having up to three actions in a single table.