Solved

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

Posted on 2004-09-10
16
212 Views
Last Modified: 2008-03-06
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.
0
Comment
Question by:JakeB52
  • 9
  • 7
16 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12028860
0
 

Author Comment

by:JakeB52
ID: 12029782
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
 

Author Comment

by:JakeB52
ID: 12045807
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12045944
OK, whereabouts did you paste the code? It needs to go into a new module rather than a form's module.
0
 

Author Comment

by:JakeB52
ID: 12045958
I put it in new module.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12046008
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
 

Author Comment

by:JakeB52
ID: 12046215
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12046373
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:JakeB52
ID: 12047009
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12047370
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
 

Author Comment

by:JakeB52
ID: 12047438
Made revisions to SQL but I get the same results.

Revision_Year is a text field
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12047607
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
 

Author Comment

by:JakeB52
ID: 12047862
I'm actually getting an error on this line

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

Runtime error 3265:
Item not found in this collection

0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 150 total points
ID: 12047938
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
 

Author Comment

by:JakeB52
ID: 12048037
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
 

Author Comment

by:JakeB52
ID: 12053281
Got it!!!! shanesuebsahakarn you're a life saver! Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now