Solved

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

Posted on 2004-09-10
16
213 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

776 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