SQL or Criteria Expression to define a field and perform a subquery?

Hi Experts,

I'm using Access 2000 and I have a query as my record source on my report.

On my report in the Details section I have a text field called “txtRevisions” that has a control source set to “Revision”.

My query results give me something like the following:

Book_Id                   Revision
169            1999
170            1996
170            2002
171            1995
171            1997
171            2000
171            2004

As expected, "txtRevisions" only equals the current record in "Revision" whereas I would like it to equal all revisions for that book id.

I would prefer to have the results populate my query in advance by creating a field in my query called “AllRevisions” and setting my control source on my report to reference it. However, I cannot figure out how to write a criteria expression or SQL for this. I would like my results to look like the following:

Book_Id                  Revision                      AllRevisions

169            1999            1999
170            1996            1996, 2002
170            2002            1996, 2002
171            1995            1995, 1997, 2000, 2003
171            1997            1995, 1997, 2000, 2003
171            2000            1995, 1997, 2000, 2003
171            2004            1995, 1997, 2000, 2003

Any suggestions would be appreciated.
JakeB52Asked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
Ah sorry, there's a correction to the code a bit further down the original article. This line:

Set rst=CurrentDb.OpenRecordset("SELECT [" & strIDField & "] FROM [" & strTable & "] WHERE [" & strIDField & "]=" & strIDValue)

Should read:
Set rst=CurrentDb.OpenRecordset("SELECT [" & strGetField & "] FROM [" & strTable & "] WHERE [" & strIDField & "]=" & strIDValue)

I think that might fix it.
0
 
shanesuebsahakarnCommented:
0
 
JakeB52Author Commented:
Thanks shanesuebsahakarn,

I like this approach but I'm having some difficulties getting the function to work with my tables. I have to leave but I'll take a closer look on Monday and let you know if it worked or not.

Thanks for your patience.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
JakeB52Author Commented:
I’m still having difficulties trying to get the function to work. The error I receive when I run the query is as follows:

Compile error in query expression:
ShnCombine("Book_Revision","[Book_Id]",[Book_Revision].[Book_Id],"Revision")

“Book_Revision” is the name of the second table
“Book_Id” is the linked field used by both tables
“Revision” is the name of the field in the second table

A simplified version of my table design is as follows:

Table name: Books
Book_Id - numeric

Table name: Book_Revision
Book_Id - numeric
Revision -numeric

The code for the function is the same as the suggested solution:

Function shnCombine(strTable As String, strIDField As String, strIDValue As Long, strGetField As String) As String
Dim rst As Recordset

Dim strCombine As String
Set rst = CurrentDb.OpenRecordset("SELECT [" & strIDField & "] FROM [" & strTable & "] WHERE [" & strIDField & "]=" & strIDValue)
Stop

While Not rst.EOF
   strCombine = strCombine & rst(strGetField) & ","
   rst.MoveNext
Wend
rst.Close
Set rst = Nothing
If strCombine <> "" Then strCombine = Left$(strCombine, Len(strCombine) - 1)
shnCombine = strCombine
End Function
0
 
shanesuebsahakarnCommented:
OK, whereabouts did you paste the code? It needs to go into a new module rather than a form's module.
0
 
JakeB52Author Commented:
I put it in new module.
0
 
shanesuebsahakarnCommented:
Ah ok, can you check that you have a reference to DAO - open a code module and click Tools->References. Check that Microsoft DAO 3.5/3.6 is checked. Now change this line:

Dim rst As Recordset

to:
Dim rst As DAO.Recordset

Does that make a difference?
0
 
JakeB52Author Commented:
Yes it does make a difference but I'm getting a new error...

Runtime error 3075:
Syntax error in query expression '[[Book_Id]]'.

I tried removing the extra brackets in the function but then I get...

Runtime error 3265:
Item not found in this collection

By the way, thanks for your help so far. I think I've been staring at this code too long and the obvious just isn't jumping out at me.
0
 
shanesuebsahakarnCommented:
OK, this:

ShnCombine("Book_Revision","Book_Id",[Book_Revision].[Book_Id],"Revision")

Should combine all of the "Revision" fields in the Book_Revision table where the Book_Id in that table is the same as the Book_Id in the current table. Can you post the SQL of the query where you're using this expression?
0
 
JakeB52Author Commented:
Sorry for the delay.

I tried to simplify my query to something manageble. I must apologize,“Revision_Year” is the actual name of the field in the second table not "Revision".

I'm not getting any errors when I run the query. However, nothing is being populated in my field "txtRevisions".

My expression that I'm using is:
txtRevisions: shnCombine("Book_Revision","Book_Id",[Book_Revision].[Book_Id],"Revision_Year")

This is my SQL for testing:
SELECT Books.Book_Id, Books.Name, Book_Revision.Revision_Year, shnCombine("Book_Revision","Book_Id",[Book_Revision].[Book_Id],"Revision_Year") AS txtRevisions
FROM Book_Revision INNER JOIN Books ON Book_Revision.Book_Id = Books.Book_Id;
0
 
shanesuebsahakarnCommented:
That looks right - although try this:

SELECT Books.Book_Id, Books.Name, Book_Revision.Revision_Year, shnCombine("Book_Revision","Book_Id",[Books].[Book_Id],"Revision_Year") AS txtRevisions
FROM Book_Revision INNER JOIN Books ON Book_Revision.Book_Id = Books.Book_Id;

Is the Revision_Year a numeric or text field?
0
 
JakeB52Author Commented:
Made revisions to SQL but I get the same results.

Revision_Year is a text field
0
 
shanesuebsahakarnCommented:
OK, could you try this - press CTRL+G to open the Immediate window. Type:
?shnCombine("Book_Revision","Book_id",171,"Revision_Year")

Replace 171 with a number that you know exists in the Book_Revision table. Does it give nothing back?
0
 
JakeB52Author Commented:
I'm actually getting an error on this line

strCombine = strCombine & rst(strGetField) & ","

Runtime error 3265:
Item not found in this collection

0
 
JakeB52Author Commented:
Getting closer! I'm getting results but they are the same results for every record.
It appears only to be looping through the first Book_Id and dismissing the rest.

I'm going to have to sleep on this one but I'll be back tomorrow with some fresh eyes.

Thanks again for your help.
0
 
JakeB52Author Commented:
Got it!!!! shanesuebsahakarn you're a life saver! Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.