CrossTab query

[craig1988]

Hi All

A very simple ask but I have thrawled through tutorials to try and find what I am looking for with no luck.

I have a table "tblCorrectionLog" that records get input to. Within the table there is a field that must be populated with an ErrorCode. Every record gets a timestamp.

I would like to build a crosstab query that Shows a scoreboard of the number of corrections logged under their ErrorCode for a particular time period (Between [Forms]... And [Forms]...)

See below

[orange]

Readers will need more info if they are to provide focused answers/assistance.

What are the table(s) or query(ies) involved?
What have you tried specifically (query sql)?

[craig1988]

Ill explain a little better. I havent as of yet tried any queries or sql.


There are 2 tables I believe i need to focus on.

"tblCorrectionList" - This is a table that contains a list of Corrections. There is currently 15 but this will shrink or grow. Each one has a unique ErrorID.
"tblCorrectionLog" - This is the table where the corrections are logged. Users use a bound form to add corrections. They enter who commited the error (staffID), the type of error (ErrorID) and it also gets a timestamp (Now()).


What I want to pull from these tables is a crosstab query. I want the user to input a start date and end date on the form "frmReports" and I want the crosstab query to return;

The ErrorID's as row headings, The dates (Start date to End date) as Column Headings and the COUNT of each ErrorID on the date.

So, like my img in the original post, the query will look at each ErrorID and count how many times a correction of that kind was logged for each day within the date span (Start date to end date).

[craig1988]

Hi again

So I have tried to experiment myself and have got somewhere but not exactly what I was looking for!!

I was able to get the correct data for the entire Table "tblCorrectionLog", but I only want records from a certain date period.

Originally I was basing the crosstab query on the "tblCorrectionLog" so I tried to base it on a query instead (The query pulls records from a specific date period using a form to get the start date and end date Between [Forms]![frmReport]![txtStartDate] And [Forms]![frmReport]![txtEndDate]). This SELECT query is called "qryGrabForCrosstab".

When I try to run the crosstab query based on "qryGrabForCrosstab" I got a MsgBox telling me "The Microsoft database engine does not recognize '[Forms]![frmReport]![txtStartDate]' as a valid field name or expression".

What am I doing wrong?? The logic appears to be correct?? Can I enter parameters in my original approach by basing the crosstab directly on the table??