Can anyone help ?????????? (1 Viewer)

[barrygraham]

Hello access guru's,

Does anyone have the answer to my query I posted yesterday: Crosstab Report - linking from an unbound form.

I am sorry if I am being impatient, but this has been annoying me for 3 days now, and I cant get the code right at all. I keep getting the same error. If only they mentioned useful things like this in the books!

Thanks again,

Barry Graham

 

[Fornatian]

Can't you extrapolate the data in a normal query and then use that as the source for the crosstab query.

I haven't had any experiences of working with crosstab queries but that's how I would try and do it if I was having trouble filtering data.

Ian

 

[barrygraham]

Hi again, I have tried this solution, and I am still getting the same Error:
Does not recognise <field name> as a valid field or expression. Thank you for the suggestion, and for aminute i thought it was actually going to work. Anyway, thanks again, the search continues ......................

PS Any other suggestions, as mine are wearing very very thin now!! As are my patience. Thank you to anyone who has had a think about this problem.

 

[Richie]

Which field on your crosstab has the criteria, is it either column, row, or value item and does the form stay open while the query runs?

 

[barrygraham]

Hi there Richie,

The Criteria are all row headings, and the form does stay open when the button is pressed. This is why it is so annoying.

To give you a bit more info, the crosstab takes it "values" from an attendance table and counts how many times a student has been 1. Present, 2.Sick, 3.Exam and so on .....

These attendance "status's" are then used as column headings. I have used the Nz function so that if a student has been present in all subjects and has no other attendance status to show, the database inserts a zero into the field.

Now, all of the students other information is from other tables, such as name, address, tutor, main centre, faculty etc ......... these are all row headings. I cant see for the life of me why I cant use any criteria to filter the report from an unbound form. I have used this method time and time again, using the same filters (tutor, centre etc...) without any hiccups!

If you can help me you will instantly become my new best friend!

Thank you, Barry .

 

[Richie]

Ill try briefly remove the criteria from the row heading field, add the field again to the end of the grid and add the criteria to that, do not set it to row col source etc.

 

[Shep]

Hello again Barry,
I tried your scenario and found that indeed-a control's value on an unbound form is not usable as criteria in a crosstab query unless you also supply that same expression as a Parameter in the crosstab query. You must also specify the correct data type. 'Text' worked for me. Hope this helps. Just copy and paste the criteria expression in as a Parameter.
Shep

[This message has been edited by Shep (edited 12-08-2000).]

 

[barrygraham]

Hello again,

I have finally solved the problem, for anyone that is interested. Thank you again for all of your help on this problem, before I asked on this site I thought I was the only one who didnt know about crosstabs and was starting to think I was thick!!!!!

To solve the problem what I did was this:

I took all of the data in the crosstab and deleted anything that wasnt necessary. This means all I was left with was 1 row heading, 1 column heading and 1 "value" (We will call this query 1)

I then used this data in another "non - crosstab" query and added some more essential fields, like the subject name and the centre(we will call this query 2).

In a third query I got all of the students details together, ie, main centre, faculty, tutor group etc......... (we will call this query 3)

Now to the reports.

I created a "Main Report" with all of the students details in it (Query 3)

I created another report with all of the Attendance Stats in it (Query 2)

I used Query 2's report as a subreport linked to the main report using the student's enrolment number.

Low and behold I can now print the report using any filter I please WAHHOOOO! A full weeks work in VB and came down to a simple bloody sub-report. Would you beleive it????

Anyway, thank you very very much to anyone who got in touch and helped me out with this problem. I hope that the solution that I came up with helps someone else in some way.

TTFN Barry.

PS Shep, if you are reading this, I still have your problem in mind ............

 

[Fornatian]

Well done that man!