Combining Same Field Values in a Query... 3

[SherryLynn]

I am hoping someone here has an easy answer for me. I have a database that tracks families and their children. One of my main tables (Family Table) records the Family's Last Name, File No., etc. The other table (Child Table) records the name of each child in the family (there can be 1 or 10 children), date of birth, etc. I want to create a query to make File labels on each family. The label should include the Last Name, File No. and all the children's names separated by commas. Here's what I want it to look like:

SMITH xx JONES S003394
John, Mary, Ken, Shelley

I have created a make table query on these fields. The table it creates includes a record for each child:

SMITH xx JONES S003394 John
SMITH xx JONES S003394 Mary
SMITH xx JONES S003394 Ken
SMITH xx JONES S003394 Shelley

I would like it to create a table that makes one record for the family:

SMITH xx JONES S003394 John, Mary, Ken, Shelley

I appreciate any and all help. I haven't been able to even get close to a solution.

Thanks,
Sherry

 

[PaulBricker]

Sherry the only way to do this is using a Function. Put this code in a module changing Table/Field names to your names. Save it as funGetFamily

Function getFamily(myID as String) as String
Dim strSQL as String
Dim strHolder as String, strFam as String
Dim rst as DAO.Recordset
strSQL = "Select * From [YourTable] Where [YourTable].[File No]= '" & myID & "'"
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
strFam = rst![Last Name] & " " & myID
strHolder = strHolder & rst![First Name] & " ,"
rst.MoveNext
Loop
getFamily = strFam & " " & Mid(strHolder,1,Len(strHolder)-2)
End Function

Now, create a query using your MakeTable. Add the ID field to the query. In the next column put this expression
MyFam:getFamily([File No]). Then on the menu bar go to View...Totals. On the Totals line, Group By the File No field and set the next column to Expression.

Open the query and see if it gives you what you need.

Paul

 

[SherryLynn]

Thank you Paul. I will try that today. I appreciate your help.

Sherry

 

[PaulBricker]

Sherry if you want to show the names under the Family Name and File No then change this line

getFamily = strFam & " " & Mid(strHolder,1,Len(strHolder)-2)

to this

getFamily = strFam & vbCrLf & Mid(strHolder,1,Len(strHolder)-2)

Paul

 

[SherryLynn]

Hi Paul,

I'm not very experienced with Visual Basic code so I'm struggling a little with this. I wrote the code and created the module, although I think I might be getting confused about which of my codes go where. I haven't had any luck getting this to work for me so far.

Should I be replacing the "myID" with the FamilyID or the File No. field? The table's primary key is the FamilyID if that helps.

Thanks again for your help!

Sherry

 

[PaulBricker]

No. myID is a variable that we use to pas the File No to the function. Post your code here and I will try and clean it up so you just have to copy and paste it into your database.

Paul

 

[SherryLynn]

Hi Paul,

Here is what I have in the module:

Function getFamily(FamilyID As String) As String
Dim strSQL As String
Dim strHolder As String, strFam As String
Dim rst As DAO.Recordset
strSQL = "Select * From [tblFamily] Where [tblFamily].[FamilyID]= " ' & FamilyID & "' "Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenDynaset)"

rst.MoveFirst
Do Until rst.EOF
strFam = rst![tblFamily].[Family] & "" & FamilyID
strHolder = strHolder & rst![Name] & " ,"
rst.MoveNext
Loop
getFamily = strFam & "" & Mid(strHolder, 1, Len(strHolder) - 2)

End Function

 

[PaulBricker]

Function getFamily(myID As String) As String
Dim strSQL As String
Dim strHolder As String, strFam As String
Dim rst As DAO.Recordset
strSQL = "Select * From [tblFamily] Where [tblFamily].[FamilyID]= "' & myID & "'"
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenDynaset)

rst.MoveFirst
Do Until rst.EOF
strFam = rst![Family] & " " & myID
strHolder = strHolder & rst![Name] & " ,"
rst.MoveNext
Loop
getFamily = strFam & " " & Mid(strHolder, 1, Len(strHolder) - 2)

End Function


OK, copy and paste this into a module. Make sure that the line for strSQL is all on the same line in your Module. Same thing with the getFamily line. We don't want individual lines in the code to run to two lines.
Then in a new column of your query put
myFam:getFamily([FamilyID])

That should do what you need. Let me know if you still have problems. The first couple of times thru can be bumpy.

Paul

 

[SherryLynn]

Hi Paul,

Thank you for getting back to me so fast. I pasted the code into my module, adjusted the lines so they were all together (deleted the old code). I put the expression in the query, but I am getting the following message when I try to run it:

The specified field "[FamilyID]" could refer to more than one table listed in the FROM clause of your SQL statement.

Can you help?

Thanks, Sherry

 

[PaulBricker]

Yes, change your expression to this (if that's the right table).
myFam:getFamily([tblFamily].[FamilyID])

Just to recap, this should be a Totals query with two fields. FamilyID and my expression. The Totals line should be visible in the query Field Grid and you should have GroupBy for the FamilyID field and Expression for the myFam: expression.

If you have problems, open the query in SQL view and copy and paste the SQL here so I can see it.
I'll look for it in the morning. Time for me to sign off.

Paul

 

[SherryLynn]

Hello,

I tried it again. Tell me if I did the right thing. I had been putting the expression into the query I had already created which had a few fields in it (Family, File, Name, FamilyID). When I got your last message I created a new query with the two tables (Family & Child). I only added the FamilyID field and then put the expression in the next column. When I tried to run it I got the following error. By the way I made it a create table query - was that right?

Error message:
Run-time error '91':
Object variable or With block variable not set.

I copied the SQL statement as you suggested from the query:

SELECT tblFamily.FamilyID, getFamily([tblFamily].[FamilyID]) AS myFam INTO [tblLabel Create Test]
FROM tblFamily INNER JOIN tblChildren ON tblFamily.FamilyID = tblChildren.FamilyID
GROUP BY tblFamily.FamilyID;

What am I doing wrong?

Thanks again for all your help Paul.

Sherry

 

[PaulBricker]

Sherry, the problem is we have two tables and to run this Function, I need the information in one table/query. In your first post you said, "I have created a make table query on these fields. The table it creates includes a record for each child:", and then you showed how the records looked
SMITH xx JONES S003394 John
SMITH xx JONES S003394 Mary
SMITH xx JONES S003394 Ken
SMITH xx JONES S003394 Shelley

This is the structure we need our information in before we can run the query that puts it all in one record. If you have a table that looks like this then that is what we want to use for the source of our query. I'm going to assume you have a table or query that has the information in this structure and I'm going to call it Table1 for this example. This would be the SQL for the Query
Select Table1.FamilyID, getFamily([FamilyID]) AS myFam
From Table1
Group By Table1.FamilyID

Try it out and let me know. Don't worry, we'll get it together.

Paul

 

[SherryLynn]

Thanks very much - I was misunderstanding that. I will try it now and see what happens.

I'll let you know.

Sherry

 

[SherryLynn]

Hi Paul,

I did everything as you said, but still haven't had luck. Once I realized I needed to create a separate query (with the FamilyID field and the expression) I did that. I made it a make table query. I changed the table referred to in the module and the query to tblLabels-Export. This is the table which holds the data as I had stated earlier (Last Name, File#, Children's names). I was referring to the wrong tables initially because I thought I needed to use the ones which I used for the first query which pulls this data together for me. Anyhow, when I run the query with the expression I get errors.

First I get this:
Run-time error '91':
Object variable or With block variable not set.

Then if I select "End" (rather than "Debug&quot on this error message, it goes to another error message as follows:

Microsoft Access can't add all the records in the update or append query.
It set 2 field(s) to Null due to a type conversion failure. A type conversion failure is caused when the data in one or more fields doesn't match the DataType or FieldSize property in the destination table. For example, leaving blank fields in a Yes/No field or entering text in a numeric field will cause this error. Do you want to ignore the errors and run the update or append query anyway?

I choose yes and it creates my table. When I look at the table it shows two columns: FamilyID and myFam. In the Family ID column it shows the correct FamilyID numbers, but no other data.

Here is the SQL statement from the query I created as you directed:

SELECT [tblLabels-Export].FamilyID, getFamily([tblLabels-Export].[FamilyID]) AS myFam INTO [tblLabel Create Test]
FROM [tblLabels-Export]
GROUP BY [tblLabels-Export].FamilyID;

I don't know where to go from here. It seems that we are really close. Thanks for all your help.

Sherry

 

[PaulBricker]

DON'T make it a make table. Take that part out of it. I don't think we can run a make table and have the function perform in it at the same time. Now to deal with the Object variable not set, here's what you do. Open the Function in Design View. On the Menu bar go to Tools...References. Scroll down till your see the Microsoft DAO 3.6 Object Library. It may be 3.5 or something else depending on the version you are runnning but select the highest DAO library available by putting a check in the box next to it. Go up and hit the save button. Then try and run the query again. Remember to turn it back to a regular Select query.

Paul

 

[SherryLynn]

Hi Paul,

I've done what you said, but am getting the following message when I try to save the change (selecting the Microsoft DAO 3.6 Object Library):

Name Conflicts with existing module, project or object library.

Sherry

 

[PaulBricker]

That means that some other DAO Library is selected. You will find it up at the top of the list with the other checked boxes. You can uncheck that but I'm a little surprised because that error usually means that we are missing a reference. Go ahead and select the 3.6 library and I'll look at the code again.

Paul

 

[PaulBricker]

Sherry, if you still get the message, hit the debug button and tell me what line the code stops on. Also, what version of Access are you using. That may make a difference. If nothing works you might email me a small sample with 2 or 3 records (phoney names are OK) to
pbricker@attbi.com

Paul

 

[PaulBricker]

First email address is wrong, second one is correct. Guess I didn't get to the Stop button fast enough.

Paul

 

[SherryLynn]

Hi Paul,

I did what you said (changed the library) and didn't get the "Name Conflicts" message. Ran the query and got the same Run-time error '91'. When I run Debug, the code stops at:

rst.MoveFirst

I am running Access 97 on Win 98 SE at home, but it's Win NT at work.

What do you think?

Sherry