JakeB52
asked on
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.
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.
ASKER
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.
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.
ASKER
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_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("S
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
Compile error in query expression:
ShnCombine("Book_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("S
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
OK, whereabouts did you paste the code? It needs to go into a new module rather than a form's module.
ASKER
I put it in new module.
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?
Dim rst As Recordset
to:
Dim rst As DAO.Recordset
Does that make a difference?
ASKER
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.
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.
OK, this:
ShnCombine("Book_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?
ShnCombine("Book_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?
ASKER
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"
This is my SQL for testing:
SELECT Books.Book_Id, Books.Name, Book_Revision.Revision_Yea
FROM Book_Revision INNER JOIN Books ON Book_Revision.Book_Id = Books.Book_Id;
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"
This is my SQL for testing:
SELECT Books.Book_Id, Books.Name, Book_Revision.Revision_Yea
FROM Book_Revision INNER JOIN Books ON Book_Revision.Book_Id = Books.Book_Id;
That looks right - although try this:
SELECT Books.Book_Id, Books.Name, Book_Revision.Revision_Yea
FROM Book_Revision INNER JOIN Books ON Book_Revision.Book_Id = Books.Book_Id;
Is the Revision_Year a numeric or text field?
SELECT Books.Book_Id, Books.Name, Book_Revision.Revision_Yea
FROM Book_Revision INNER JOIN Books ON Book_Revision.Book_Id = Books.Book_Id;
Is the Revision_Year a numeric or text field?
ASKER
Made revisions to SQL but I get the same results.
Revision_Year is a text field
Revision_Year is a text field
OK, could you try this - press CTRL+G to open the Immediate window. Type:
?shnCombine("Book_Revision
Replace 171 with a number that you know exists in the Book_Revision table. Does it give nothing back?
?shnCombine("Book_Revision
Replace 171 with a number that you know exists in the Book_Revision table. Does it give nothing back?
ASKER
I'm actually getting an error on this line
strCombine = strCombine & rst(strGetField) & ","
Runtime error 3265:
Item not found in this collection
strCombine = strCombine & rst(strGetField) & ","
Runtime error 3265:
Item not found in this collection
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Got it!!!! shanesuebsahakarn you're a life saver! Thanks.
https://www.experts-exchange.com/questions/21110424/Combine-multiple-records-in-a-query.html