Creating a dynamic report from crosstab in an attached database below. PLS HELP.

[chris92]

Pls help. I have a database with 3 tables two queries (1 cross tab and 1 normal query). The crosstab was formed from the normal query. Then a report that the source is the crosstab. I would like the report to be dynamic so that if the values are edited or added, the value would reflect on the report. Thanks. Pls I someone help.

 

[Isskint]

You need to make the select query a parameter query, so the Semester and Year of Exam can be entered each time the report is run.
Alternatively you can provide a pop up form for the users to select this criteria and have the select query reference these values as the parameters.

 

[chris92]

You need to make the select query a parameter query, so the Semester and Year of Exam can be entered each time the report is run.
Alternatively you can provide a pop up form for the users to select this criteria and have the select query reference these values as the parameters.

Hi Isskint, am not good at the options you gave me at all. Pls can you help me make it work. Thanks for understanding and being there for me.

 

[Isskint]

There are LOTS of examples of both methods on this forum. I appreciate some times the 'explanations' themselves need explaining:banghead:, so i will help with the 1st method.

OK in your query 100 1st sem result replace the criteria "first" with [Enter Semester] under Semester. Also replace the criteria "yr. 1" with [Enter Exam Year] under YEAR OF EXAM. The square brackets [ ] make the query ask for these values each time the query/report is run.

 

[chris92]

Sup iss, I did ur suggested replacement and incuded them in my parameters but I can't view other results except for first in semester and yr. 1 for year of exam . Thanks

 

[Isskint]

The SQL for that query should look like this;

SELECT courses.SEMESTER, results.COURSE_CODE, results.SCORE, results.SESSION, Switch([score]>=70,"A",[score]>=60,"B",[score]>=50,"C",[score]>=45,"D",[score]>=40,"E",[score]<40,"F") AS GRADE, results.YEAR_OF_EXAM, Switch([GRADE]="A",5,[GRADE]="B",4,[GRADE]="C",3,[GRADE]="D",2,[GRADE]="E",1,[GRADE]="F",0) AS POINTS, courses.[CREDIT LOAD], [points]*[credit load] AS XP, courses.[COURSE TITLE], results.REGISTRATION_NUMBER, IIf([grade]="f",1,0) AS [check]
FROM courses INNER JOIN results ON courses.COURSE_CODE = results.COURSE_CODE
WHERE (((courses.SEMESTER)=[Enter Semester]) AND ((results.YEAR_OF_EXAM)=[Enter Exam Year]));

There is an extra steps you will need to do also because crosstab queries do not like paramaeters. See this link for an explanation http://support.microsoft.com/kb/209778.
Something else that would be useful is to create a form for users to select the semester and exam year from. I suggest this because otherwise you may be prompted for semester and exam year several times.


Have a look at the attached

 

[chris92]

This is the error when i enterred Second for semester and yr. 2 for yr of exam: "The microsoft access database engine does not recognize " as a valid field name or expression"

 

[tania]

Hello ,


Source table name Reportss
Column name "Order ID" ,"telco" , "Status" , "Date Submitted"


Requirement :- requirement is to show the weekly rejection count and group by two colums Telco and Status

I have created the Cross tab Query

Weeks column heading

Telco and Status row heading


How to get the count of total rows in source table

And how to count the number of rejection per week

And how to get the percentage by dividing rejection by total numbers


TRANSFORM Count(Reportss.[Order ID]) AS [CountOfOrder ID]

SELECT Reportss.telco AS Expr1, Reportss.Status

FROM Reportss

WHERE Reportss.[Status]="Rejected"

GROUP BY Reportss.telco, Reportss.Status

PIVOT Format([Date Submitted],"ww") In ("1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","51 ","52");