Solved

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

Posted on 2004-09-10
16
211 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
Comment Utility
0
 

Author Comment

by:JakeB52
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I put it in new module.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:JakeB52
Comment Utility
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
Comment Utility
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
Comment Utility
Made revisions to SQL but I get the same results.

Revision_Year is a text field
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Got it!!!! shanesuebsahakarn you're a life saver! Thanks.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…

772 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

12 Experts available now in Live!

Get 1:1 Help Now