Am I losing my marbles?

[Blackshark]

Hi all,

I have a query (Num_Ass) that looks at a list of staff in one table and returns the number of checks carried out against them (located in a second table. So Num_Ass returns a pay number and the number of 'check records' that have that pay number.

I have added a where clause with [month?] as the criteria, that when run, works out for this year, at a given month, how many staff have had at least 1 check. It works AOK when I run it and I am asked to input a month, I enter 3 lets say and it comes back with 1560 records say.

So, I now want a query that lists the months (ie 1 to 12) and returns the count from the above query when the [month?] criteria is provided with that particular month.


SELECT Month.MonthNum AS [month?], Year(Date()) AS [year?], DCount("[Q2N1]","MBR_TechAnalysis_Sub1","[month?]=" & [MonthNum]) AS EngCount
FROM [Month];



month? year? EngCount
1 2003 0
2 2003 0
3 2003 0
4 2003 0
5 2003 0
6 2003 0
7 2003 0
8 2003 0
9 2003 0
10 2003 0
11 2003 0
12 2003 0

Thats the SQL and the results. Where am I going wrong?

MonthNum is a number field, values 1 to 12. When I run the query and enter the values at the [month?] prompt I get results back.


Am I losing it or has 10 hours of work on a Saturday fried my brain?

Thanks for all assistance

Tim

 

[Blackshark]

Well, 3 hours wasted. I have not solved why I could not do the above but I have found a solution.

Altered my frst query to group the QA records by pay number and list the min month value. Then my DCount work s a treat.

Regards

 

[MichaelRed]

look up crosstab query (hel can be helpful?)



MichaelRed
m.red@att.net

Searching for employment in all the wrong places