Comparing actual result vs expected result

[sarcasym]

I'm new to this forum and wanted to thank all of the contributors for the help they've indirectly given me over the past several months.

I'll provide the basic over view of the structure first:

I have three tables user, question, result

user - uid, name, join_date, complete_date
question - qid, question, dept
result - uid, qid, initial_answer, goal, final_answer

I'm trying to run a report to determine how well a group of users did for a specific time frame, i.e. month, quarter, and year.

What I'm hoping to produce is a report that prompts for start date and end date, no problem there, and then provides the results by "question" the percent that met their goal, exceeded their goal or did not meet their goal.

I have been juggling around a ton of queries one to get a list of all the questions/results for a time frame, then one to count the questions, and queries to compare each expression.

I worked out some psuedo code but have no idea how to implement it into the actual report. I've been flipping through several books that were left in my office and searching the web but I think my lack of knowledge is limiting my search criteria.

Here is the code that I thought would do it based upon my query that gets me the result information, the question (from the question table) and the complete date (from the user table) and i'll call it PercentResultsByDate

while PercentResultsByDate
for each question id
counter++
if final answer > goal
exceeds++
if final answer = goal
meets++
if final answer < goal
failstomeet++

questionid.meets = meets
questionid.exceeds = exceeds
questionid.fails = failstomeet

My problem is not knowing how to use the vb code feature well enough to access the query manipulate it and pass back values to the report. Is it possible to do this with queries somehow? I'm open to reading and scouring if i could be pointed in the right direction.

 

[sarcasym]

I created the query "Query1":

GRESULTS: IIf([Discharge]=[Goal];"Met";IIf([FinalAnswer]>[Goal];"Exceeded";"Not Met"))

To determine if each goal was met in the final answer and paired this query with "Query2"(thanks for a starting place ByteMyzer):

TRANSFORM Round((Min([T2].[CountOfAnswer])/Count([T1].[GRESULTS]))*100,2) AS Expr1
SELECT T1.QUESTION_ID
FROM DeleteMeLater AS T1 INNER JOIN [SELECT T2.QUESTION_ID, T2.GRESULTS, Count(T2.GRESULTS) AS CountOfAnswer
FROM [DeleteMeLater] T2
GROUP BY T2.QUESTION_ID, T2.GRESULTS
]. AS T2 ON T1.QUESTION_ID = T2.QUESTION_ID
GROUP BY T1.QUESTION_ID
PIVOT T2.GRESULTS;



So now I'm getting the results I'm looking for but only if I hard code the date in Query1. If I change the criteria for the date field from something like:
Between #03/01/2010# And #03/31/2010#
to
Between [Start Date] And [End Date]

Access tells me: "The microsoft Jet Database engine does not recognize "[Start Date]" as a valid field name or expression"

Any help would be appreciated.

 

[sarcasym]

I added query parameters to Query 2 for the [Enter Start Date] and [Enter End Date] and now in the report it prompts me for date values twice for each.

I guess since the crosstab query creates the columns dynamically I'm also having trouble if lets when one of the values from the IIF statement doesn't exist. Such as if all the goals were either met or exceeded then not met doesn't exist but since I created the report I get an error.

I'm digging through the example: http://support.microsoft.com/default.aspx?scid=kb;en-us;328320

and will let you know how that goes.

 

[sarcasym]

Following the link I posted and modifying as needed seems to have helped for the most part. The problem I have now is what is showing on the report isn't what is printing out. I have it grouped by department and have roughly 8 departments showing on the preview but when I print depending it looks like it is just looping over the last result for the total number of actual results. So if five questions were answered the last answer would show up five times on the printout.