Solved

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

Posted on 2004-09-10
16
214 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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