Grouping and the Current Record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wonder if you can help me with a particular reporting problem.

My report uses an SQL recordsource, and I group and sort on StudentID and
ClassID and Assignments.

I run another SQL query which populates a text box which resides in the
ClassID group footer. This query lists all of the assignments not yet marked
as verfified (check box). This all works fine except the query does not
filter the StudentID and ClassID so all records are returned into the text
box.

Is there a way in which I can pass the grouped current record StudentID and
ClassID from the Report Recordsource into the query in the group footer for
ClassID.

Thanks for any help.

Cheers
 
TJS,
You wrote...
I run another SQL query which populates a "text box" which resides in the
ClassID group footer. This query lists all of the assignments not yet marked
as verfified (check box).
Click to expand...
A text box can't display the values from multiple records delivered by a query. Do you
mean a subform, with it's own query RecordSource?

If so...
What is the Link Parent Field and Link Child Field. Sounds like it should be ClassID.
OR...
A filter in the ClassID subform query, against ClassID...
= Reports!rptYourReportName!ClassID
 
How do you "run another SQL query"? I would expect you to design a subreport
to return the values you want to display. Use this subreport with Link
Master/Child to your StudentID and ClassID.
 
I may have misunderstood the realtionship bewteen the main report and what I'm assuming
is a subreport.
The Parent Child should be both StudentID and ClassID.
Same logic if you use the query filter...
 
I call a function that runs an sql and fills the text box with the result. I
was hoping I could link this query with the report recordsource and the
current record.

As you suggest I may have to open a sub report.

Thanks
 
Report recordsource:

SELECT DISTINCTROW Classes.*, Students.*, Assignments.*, Results.*,
[LastName] & ", " & [FirstName] AS [Student Name] FROM (Students INNER JOIN
((Classes INNER JOIN [Students And Classes] ON Classes.ClassID=[Students And
Classes].ClassID) INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON Students.StudentID=[Students And
Classes].StudentID) INNER JOIN Results ON
(Students.StudentID=Results.StudentID) AND
(Assignments.AssignmentID=Results.AssignmentID);


On Format of the CLASSID Group footer:

=FillTextBox() 'This function fills in the txtNYC textbox


'************************************
Function FillTextBox()

Dim rstMyRecords As Recordset
Dim strSQL As String
Dim strNYC As String

strSQL = "SELECT DISTINCTROW Students.StudentID, Classes.ClassID,
Results.Verification, Assignments.PerformanceID FROM (Students INNER JOIN
((Classes INNER JOIN [Students And Classes] ON Classes.ClassID=[Students And
Classes].ClassID) INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON Students.StudentID=[Students And
Classes].StudentID) INNER JOIN Results ON
(Assignments.AssignmentID=Results.AssignmentID) AND
(Students.StudentID=Results.StudentID)WHERE (((Students.StudentID)=1) AND
((Classes.ClassID)=1) AND ((Results.Verification)=No));"

rstMyRecords.MoveFirst
Do While rstMyRecords.EOF = False
strNYC = strNYC & rstMyRecords(3) & ", "
rstMyRecords.MoveNext
Loop

Reports![A4 Results].txtNYC = strNYC

End Function

My filled textbox contains, or should contain the results written to an
array. This is each of the competency performance ID not checked.

Surely there must be a way to pass the current StudentID and ClassID from
the Report recordsource SQL to the SQL in the Function FillTextBox().

Any clues.
 
The FillTextBox SQL contains the clause

WHERE (((Students.StudentID)=1) AND
((Classes.ClassID)=1) AND ((Results.Verification)=No));"

Aside from the ((Results.Verification)=No) I would like to pass the values
(((Students.StudentID)=?) AND ((Classes.ClassID)=?) from the report
recordsource, i.e., ? = 1, or 2, or 3, etc.




TJS said:
Report recordsource:

SELECT DISTINCTROW Classes.*, Students.*, Assignments.*, Results.*,
[LastName] & ", " & [FirstName] AS [Student Name] FROM (Students INNER JOIN
((Classes INNER JOIN [Students And Classes] ON Classes.ClassID=[Students And
Classes].ClassID) INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON Students.StudentID=[Students And
Classes].StudentID) INNER JOIN Results ON
(Students.StudentID=Results.StudentID) AND
(Assignments.AssignmentID=Results.AssignmentID);


On Format of the CLASSID Group footer:

=FillTextBox() 'This function fills in the txtNYC textbox


'************************************
Function FillTextBox()

Dim rstMyRecords As Recordset
Dim strSQL As String
Dim strNYC As String

strSQL = "SELECT DISTINCTROW Students.StudentID, Classes.ClassID,
Results.Verification, Assignments.PerformanceID FROM (Students INNER JOIN
((Classes INNER JOIN [Students And Classes] ON Classes.ClassID=[Students And
Classes].ClassID) INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON Students.StudentID=[Students And
Classes].StudentID) INNER JOIN Results ON
(Assignments.AssignmentID=Results.AssignmentID) AND
(Students.StudentID=Results.StudentID)WHERE (((Students.StudentID)=1) AND
((Classes.ClassID)=1) AND ((Results.Verification)=No));"

rstMyRecords.MoveFirst
Do While rstMyRecords.EOF = False
strNYC = strNYC & rstMyRecords(3) & ", "
rstMyRecords.MoveNext
Loop

Reports![A4 Results].txtNYC = strNYC

End Function

My filled textbox contains, or should contain the results written to an
array. This is each of the competency performance ID not checked.

Surely there must be a way to pass the current StudentID and ClassID from
the Report recordsource SQL to the SQL in the Function FillTextBox().

Any clues.

Duane Hookom said:
If you need to return only a single record, DLookup() might be an
alternative.
Click to expand...
Click to expand...
 
The txtNYC textbox when filled contains data such as:

1.2, 1.3, 1.4, 1.5, 2.1, 2.4, 3.1, 3.4, 3.5

for each class of each student.

Thanks

Tim



TJS said:
Report recordsource:

SELECT DISTINCTROW Classes.*, Students.*, Assignments.*, Results.*,
[LastName] & ", " & [FirstName] AS [Student Name] FROM (Students INNER JOIN
((Classes INNER JOIN [Students And Classes] ON Classes.ClassID=[Students And
Classes].ClassID) INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON Students.StudentID=[Students And
Classes].StudentID) INNER JOIN Results ON
(Students.StudentID=Results.StudentID) AND
(Assignments.AssignmentID=Results.AssignmentID);


On Format of the CLASSID Group footer:

=FillTextBox() 'This function fills in the txtNYC textbox


'************************************
Function FillTextBox()

Dim rstMyRecords As Recordset
Dim strSQL As String
Dim strNYC As String

strSQL = "SELECT DISTINCTROW Students.StudentID, Classes.ClassID,
Results.Verification, Assignments.PerformanceID FROM (Students INNER JOIN
((Classes INNER JOIN [Students And Classes] ON Classes.ClassID=[Students And
Classes].ClassID) INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON Students.StudentID=[Students And
Classes].StudentID) INNER JOIN Results ON
(Assignments.AssignmentID=Results.AssignmentID) AND
(Students.StudentID=Results.StudentID)WHERE (((Students.StudentID)=1) AND
((Classes.ClassID)=1) AND ((Results.Verification)=No));"

rstMyRecords.MoveFirst
Do While rstMyRecords.EOF = False
strNYC = strNYC & rstMyRecords(3) & ", "
rstMyRecords.MoveNext
Loop

Reports![A4 Results].txtNYC = strNYC

End Function

My filled textbox contains, or should contain the results written to an
array. This is each of the competency performance ID not checked.

Surely there must be a way to pass the current StudentID and ClassID from
the Report recordsource SQL to the SQL in the Function FillTextBox().

Any clues.

Duane Hookom said:
If you need to return only a single record, DLookup() might be an
alternative.
Click to expand...
Click to expand...
 
There is a generic Concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane that you
could use rather than your code (I think).
You would save a query
==qselPerformance=======
SELECT DISTINCTROW Students.StudentID, Classes.ClassID,
Results.Verification, Assignments.PerformanceID
FROM (Students INNER JOIN ((Classes
INNER JOIN [Students And Classes] ON
Classes.ClassID=[Students And Classes].ClassID)
INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON
Students.StudentID=[Students And Classes].StudentID)
INNER JOIN Results ON
(Assignments.AssignmentID=Results.AssignmentID) AND
(Students.StudentID=Results.StudentID)
WHERE Results.Verification=No;
=====================
Then use the concatenate function in a control source of a text box like:
=Concatenate("SELECT PerformanceID FROM qselPerformance WHERE StudentID = "
& [StudentID] & " And ClassID = " & [ClassID])
--
Duane Hookom
MS Access MVP


TJS said:
The txtNYC textbox when filled contains data such as:

1.2, 1.3, 1.4, 1.5, 2.1, 2.4, 3.1, 3.4, 3.5

for each class of each student.

Thanks

Tim



TJS said:
Report recordsource:

SELECT DISTINCTROW Classes.*, Students.*, Assignments.*, Results.*,
[LastName] & ", " & [FirstName] AS [Student Name] FROM (Students INNER
JOIN
((Classes INNER JOIN [Students And Classes] ON Classes.ClassID=[Students
And
Classes].ClassID) INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON Students.StudentID=[Students And
Classes].StudentID) INNER JOIN Results ON
(Students.StudentID=Results.StudentID) AND
(Assignments.AssignmentID=Results.AssignmentID);


On Format of the CLASSID Group footer:

=FillTextBox() 'This function fills in the txtNYC textbox


'************************************
Function FillTextBox()

Dim rstMyRecords As Recordset
Dim strSQL As String
Dim strNYC As String

strSQL = "SELECT DISTINCTROW Students.StudentID, Classes.ClassID,
Results.Verification, Assignments.PerformanceID FROM (Students INNER JOIN
((Classes INNER JOIN [Students And Classes] ON Classes.ClassID=[Students
And
Classes].ClassID) INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON Students.StudentID=[Students And
Classes].StudentID) INNER JOIN Results ON
(Assignments.AssignmentID=Results.AssignmentID) AND
(Students.StudentID=Results.StudentID)WHERE (((Students.StudentID)=1) AND
((Classes.ClassID)=1) AND ((Results.Verification)=No));"

rstMyRecords.MoveFirst
Do While rstMyRecords.EOF = False
strNYC = strNYC & rstMyRecords(3) & ", "
rstMyRecords.MoveNext
Loop

Reports![A4 Results].txtNYC = strNYC

End Function

My filled textbox contains, or should contain the results written to an
array. This is each of the competency performance ID not checked.

Surely there must be a way to pass the current StudentID and ClassID from
the Report recordsource SQL to the SQL in the Function FillTextBox().

Any clues.

Duane Hookom said:
If you need to return only a single record, DLookup() might be an
alternative.
--
Duane Hookom
MS Access MVP

I call a function that runs an sql and fills the text box with the
result.
I
was hoping I could link this query with the report recordsource and
the
current record.

As you suggest I may have to open a sub report.

Thanks

:

How do you "run another SQL query"? I would expect you to design a
subreport
to return the values you want to display. Use this subreport with
Link
Master/Child to your StudentID and ClassID.
--
Duane Hookom
MS Access MVP


I wonder if you can help me with a particular reporting problem.

My report uses an SQL recordsource, and I group and sort on
StudentID
and
ClassID and Assignments.

I run another SQL query which populates a text box which resides
in the
ClassID group footer. This query lists all of the assignments not
yet
marked
as verfified (check box). This all works fine except the query
does not
filter the StudentID and ClassID so all records are returned into
the
text
box.

Is there a way in which I can pass the grouped current record
StudentID
and
ClassID from the Report Recordsource into the query in the group
footer
for
ClassID.

Thanks for any help.

Cheers
Click to expand...
Click to expand...
Click to expand...
 
Duane,

Thanks so much. You put me on the right track. I did get my code to work,
however, your routine is far more elegant, so with a little tweaking of your
coding - magic at last.

Briliant - thanks again.

Tim

Duane Hookom said:
There is a generic Concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane that you
could use rather than your code (I think).
You would save a query
==qselPerformance=======
SELECT DISTINCTROW Students.StudentID, Classes.ClassID,
Results.Verification, Assignments.PerformanceID
FROM (Students INNER JOIN ((Classes
INNER JOIN [Students And Classes] ON
Classes.ClassID=[Students And Classes].ClassID)
INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON
Students.StudentID=[Students And Classes].StudentID)
INNER JOIN Results ON
(Assignments.AssignmentID=Results.AssignmentID) AND
(Students.StudentID=Results.StudentID)
WHERE Results.Verification=No;
=====================
Then use the concatenate function in a control source of a text box like:
=Concatenate("SELECT PerformanceID FROM qselPerformance WHERE StudentID = "
& [StudentID] & " And ClassID = " & [ClassID])
--
Duane Hookom
MS Access MVP


TJS said:
The txtNYC textbox when filled contains data such as:

1.2, 1.3, 1.4, 1.5, 2.1, 2.4, 3.1, 3.4, 3.5

for each class of each student.

Thanks

Tim



TJS said:
Report recordsource:

SELECT DISTINCTROW Classes.*, Students.*, Assignments.*, Results.*,
[LastName] & ", " & [FirstName] AS [Student Name] FROM (Students INNER
JOIN
((Classes INNER JOIN [Students And Classes] ON Classes.ClassID=[Students
And
Classes].ClassID) INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON Students.StudentID=[Students And
Classes].StudentID) INNER JOIN Results ON
(Students.StudentID=Results.StudentID) AND
(Assignments.AssignmentID=Results.AssignmentID);


On Format of the CLASSID Group footer:

=FillTextBox() 'This function fills in the txtNYC textbox


'************************************
Function FillTextBox()

Dim rstMyRecords As Recordset
Dim strSQL As String
Dim strNYC As String

strSQL = "SELECT DISTINCTROW Students.StudentID, Classes.ClassID,
Results.Verification, Assignments.PerformanceID FROM (Students INNER JOIN
((Classes INNER JOIN [Students And Classes] ON Classes.ClassID=[Students
And
Classes].ClassID) INNER JOIN Assignments ON
Classes.ClassID=Assignments.ClassID) ON Students.StudentID=[Students And
Classes].StudentID) INNER JOIN Results ON
(Assignments.AssignmentID=Results.AssignmentID) AND
(Students.StudentID=Results.StudentID)WHERE (((Students.StudentID)=1) AND
((Classes.ClassID)=1) AND ((Results.Verification)=No));"

rstMyRecords.MoveFirst
Do While rstMyRecords.EOF = False
strNYC = strNYC & rstMyRecords(3) & ", "
rstMyRecords.MoveNext
Loop

Reports![A4 Results].txtNYC = strNYC

End Function

My filled textbox contains, or should contain the results written to an
array. This is each of the competency performance ID not checked.

Surely there must be a way to pass the current StudentID and ClassID from
the Report recordsource SQL to the SQL in the Function FillTextBox().

Any clues.

:

If you need to return only a single record, DLookup() might be an
alternative.
--
Duane Hookom
MS Access MVP

I call a function that runs an sql and fills the text box with the
result.
I
was hoping I could link this query with the report recordsource and
the
current record.

As you suggest I may have to open a sub report.

Thanks

:

How do you "run another SQL query"? I would expect you to design a
subreport
to return the values you want to display. Use this subreport with
Link
Master/Child to your StudentID and ClassID.
--
Duane Hookom
MS Access MVP


I wonder if you can help me with a particular reporting problem.

My report uses an SQL recordsource, and I group and sort on
StudentID
and
ClassID and Assignments.

I run another SQL query which populates a text box which resides
in the
ClassID group footer. This query lists all of the assignments not
yet
marked
as verfified (check box). This all works fine except the query
does not
filter the StudentID and ClassID so all records are returned into
the
text
box.

Is there a way in which I can pass the grouped current record
StudentID
and
ClassID from the Report Recordsource into the query in the group
footer
for
ClassID.

Thanks for any help.

Cheers
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Build Report based on 3 conditions 4
Another question about joins 4
Insert records to a related table when an option is selected from list 1
Many to Many relationships 3
append multiselect to table 8
Stymied by SQL 1
populate a combo box in a form 1
SQL query with Many-To-Many relationship??? 2

Back
Top