how to pass parameters for "subquery" which record source's query is based of

[fcortes]

how to pass parameters for "subquery" which record source's query is based of

Hi

I have a query, called query1, that has a parameter1.
I made another query, called query2 that is based query1.
I then got another query, called query3, which is based on query2, and is this last query, query3 that is the record source of a reportA.

opening the reportA, will cause the parameter value from query1 to be called and when entered I get the expected result. up to that point, all is great! a bit slow, guess due to the query on top of a query but, it works and I couldn't be happier.

Hoping to make the report calling more user friendly, I have created a form so the user can, trough a dropdownbox, select the text of parameter1 but Here's where i'm stuck: when I get the button to take the parameter1 from dropdown box on the form, I'm not sure how to pass the parameter to query1, rather than Query3 which seems to be happening when I do:

DoCmd.OpenReport "reportA", acViewReport, , "[Parameter1] = '" & <textfromdropdown> & "'"

Unfortunately, the result from parameter1 being applied to query3, is not the same as the one I would obtained if the parameter1 were to be aplied for query 1, despite the fact that the field, let's call it field1, is found on query1, query2, and query3. The result on the reportA is of course not the one expected once the report finally opens.

I'm possibly doing the whole thing wrong with the query on top of query thing, but it works, slow, but it works, is there a way to get the parameter where it's needed through the code in my form?, or is there a better approach to get this to work, possibly merge my queries into one somehow? or do DoCmd.OpenQuery ?? in the right order before openReport is executed?? what would be the basic of the syntax for this "embedded " approach?

Thank you.

 

[vbaInet]

Re: how to pass parameters for "subquery" which record source's query is based of

Why not show us the SQL statement of all of your queries.

If you want to pass the parameter to query1 you will need to change the SQL statement of the query in code.

 

[fcortes]

Re: how to pass parameters for "subquery" which record source's query is based of

Thank you so much for replying

here are my queries

Query 1


SELECT [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, Avg(CInt(Nz([Grade]))) AS Average, [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period]
FROM [All Classes P1 Query]
GROUP BY [All Classes P1 Query].Class, [All Classes P1 Query].[Student ID], [All Classes P1 Query].[Full Name], [All Classes P1 Query].Subject, [All Classes P1 Query].Qualification, [All Classes P1 Query].[Student - Class - Grade].[Class Grading Period];


Query 2

SELECT [All Classes P1 Rank-Pre Query].Class, [All Classes P1 Rank-Pre Query].[Student ID], [All Classes P1 Rank-Pre Query].[Full Name], Avg(CDbl(Nz([Average]))) AS PG
FROM [All Classes P1 Rank-Pre Query]
GROUP BY [All Classes P1 Rank-Pre Query].Class, [All Classes P1 Rank-Pre Query].[Student ID], [All Classes P1 Rank-Pre Query].[Full Name]
ORDER BY Avg(CDbl(Nz([Average]))) DESC;

Query 3

SELECT DISTINCTROW [All Classes P1 Rank Query_1].Class, [All Classes P1 Rank Query_1].[Student ID], [All Classes P1 Rank Query_1].[Full Name], [All Classes P1 Rank Query_1].PG, (Select Count(*) From [All Classes P1 Rank Query] Where [PG] > [All Classes P1 Rank Query_1].[PG])+1 AS Ranking
FROM [All Classes P1 Rank Query] AS [All Classes P1 Rank Query_1]
ORDER BY [All Classes P1 Rank Query_1].PG DESC;


In english

the database is to generate reports for a school
I'm trying to rank students on the first term per class (they have 4 terms in the year)
Query 1 gets some data from a general query: class, student id, student name, average per subject
Query 2 gets almost the same data from query 1 but averages out all the averages making a General Average (PG)
Query 3 takes the same output from Query2 and generates the ranking (field) per class.

If I give the class parameter on Query 1 or query 2, i get the expected result, a rank value of the student for their class, but If I give the parameter on query 3 which is the based of my ranking report and the one I know how to pass the parameter with the DoCmd.OpenReport function, the ranking field value for the student appears but not for the class subset but the student's ranking value in the whole school.

I'm guessing that query 1 and query 2 complete first having the whole school included as they're not being filtered therefore by the time that query3 is excecuted the source values are not for class subset but for the whole school, therefore I get the unexpected ranking field. (which is a nice to have thing but no the one I need at the moment)

I really struggled getting the ranking field to work on query3 so I've being looking at how to get query 2 and query3 combined into one query which could be possibly the solution I'm looking for, but so far getting the ranking field addition into query 2 is still a challenge for the newbie that I am.

Any help or ideas is greatly appreaciated.

 

[vbaInet]

Re: how to pass parameters for "subquery" which record source's query is based of

So which field exactly are you trying to filter by?

The ranking can be done in your report instead. Do you know about the Running Sum property of a textbox in a report? Look it up.

 

[fcortes]

Re: how to pass parameters for "subquery" which record source's query is based of

Hi

It came across in my google searches while looking for ranking solutions in access but I never tried it out. I will try it and I'll let you know.. the query is basically filtered by Class, which is the parameter that I want to pass.

while still doing the search on my own for an answer I've tried:

1. to rank again on "query 4" based on the given results of my query 3 which if it would've worked would've been the solution but apparently 2 subqueries is too much for access as the other ranking field with a subquery crashed access. (the query never finished running). bummer!!

then...

2. I came across the option to rank with a temp table. which looks promising and quite fast when I tried out but passing a parameter to an append query (the class parameter) is given me this error: query input must contain at least one table or query, which it's kind of insane since I'm using the sql text from a good working query, yet in vba it seems to be 'wrong' or so I think... (that's my current challenge)

I will try your running sum suggestion, it could be what I need. "keeping my fingers crossed".

Thank you again for replying!

Cheers for now!

 

[vbaInet]

Re: how to pass parameters for "subquery" which record source's query is based of

Running Sum will do it for you.

1. Group By the field you would like to base the ranking on.
2. Place a textbox in the Detail section of your report and enter =1 in it's Control Source property.
3. Look for the Running Sum property in the Properties sheet of the textbox and change it Over Group.
4. Run your report to test.

See the following links for more information:
http://msdn.microsoft.com/en-us/library/office/aa196561%28v=office.11%29.aspx
http://office.microsoft.com/en-us/access-help/summing-in-reports-HA001122444.aspx

 

[fcortes]

Re: how to pass parameters for "subquery" which record source's query is based of

running sum did it for me

I just put =+1 for the rank's textbox record source, I had no need to group by class for now as the report is being generated per a class selected on the form and give it a spin and boila!!! I got the numbering/ranking needed on my report (aleluya music in the background!!) ..I was happy!

It's a shame the tie issue but at this point I don't have the luxury of time to be picky!

You, dear sir, have resolved my inquiry

Thank you so much!

 

[vbaInet]

Re: how to pass parameters for "subquery" which record source's query is based of

Good to hear!

By the way it's =1, the plus(+) is not needed. What tie issue?

 

[fcortes]

Re: how to pass parameters for "subquery" which record source's query is based of

hi, thanks..

the tie issues is simply that when two students get the same grade, the ranking field obtained with the running sum will not reflect that they're in fact on the same position in the ranking, it does not happen often but, it happens every now and then. the subquery dealed with the situation a bit but at this point (given the time) the running sum gives me a more than ideal answer. Thank you again!

 

[vbaInet]

Re: how to pass parameters for "subquery" which record source's query is based of

I'm alsmost certain that I uploaded a sample db last week but I don't seem to see it. So here it is again.