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]

OK, I'll see what I can do. It may take a little bit for me to put together but I'll try and do it this evening.

Paul

 

[SherryLynn]

Thanks so much. I really appreciate all the time and effort you have put into working on this with me. It's people like you that make this site such a great resource for the inexperienced (like me).

Sherry

 

[PaulBricker]

Sherry, before I get into something that may not be productive anyway. Let's rehash what we have and what we need. I don't see why a make table is turning something into an autonumber and I'm not sure we need to use that value anyway. Sooooo!
1. We are dealing with two different fields here. These are the fields I'm interested in. File No and Family ID. I want to know what table the fields are in, whether they are primary keys/foreign keys and what datatypes they are in the underlying tables.
2. I want you to turn queLabels_Export2 into a Make Table query again and post the SQL here.

We've come this far(63 posts and counting). I don't want to switch focus again until I'm sure that we can't do what we want with what we have.
So lets look at this info first and go from there.

Paul

 

[SherryLynn]

Hi Paul,

1. The FamilyID field is part of tblFamily and is the primary key, datatype is autonumber. The File field is also part of the tblFamily and is a text field. The other table linked to this is the tblChildren which stores the children's names and other identifying information.

2. I have changed the queLabels-Export 2 back into a make table query and here is the SQL:

SELECT DISTINCTROW tblFamily.Family, tblFamily.File, tblChildren.Name, tblFamily.FamilyID, tblFamily.DateCreated, tblFamily.Area INTO [tblLabels-Export]
FROM tblFamily INNER JOIN tblChildren ON tblFamily.FamilyID = tblChildren.FamilyID
WHERE (((tblFamily.DateCreated) Between [enter start] And [enter end]) AND ((tblFamily.Area)=[Enter Area:]));

 

[PaulBricker]

Sherry, this may be just confusion on my part between FamilyID and FileNo. I assumed that the FamilyID were the numbers
S003394
that were part of your origianl post. This number is text and because I assumed it was the FamilyID, the original code I wrote checked for text. This may be all we need to clear up. To do that, we have to modify the syntax of our code just slightly to this.

Function getFamily(myID As Long) As String
Dim strSQL As String
Dim strHolder As String, strFam As String
Dim rst As DAO.Recordset
strSQL = "Select * From [tblLabels-Export] Where [tblLabels-Export].[FamilyID]= " & myID
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
strFam = rst![Family]
strHolder = strHolder & rst![Name] & " ,"
rst.MoveNext
Loop
getFamily = strFam & vbCrLf & Mid(strHolder, 1, Len(strHolder) - 2)

End Function

Now what this will do is look at the FamilyID as a Number an not text and then put the string together from there. So what we have to do is put the original make table query back together so we create tblLabels-Export and then use this code to put the Family field and childrens names together along with the FileNo in query queLabel Create test. Anyway, just in case I don't have all the names correct, we basically want to put it back the way it was and try it out.

Paul

 

[SherryLynn]

Hi Paul,

Ok, I think I did everything I needed to. When I try to run the queLabel Create Test it gives me the following error message:

You tried to execute a query that doesn't include the specified expression 'getFamily([tblLabels-Export].[FamilyID])' as part of an aggregate funtion.

I had changed the reference in the expression to look at the tblLabels-Export rather than queLabels-Export 2.

I know I missed something??

Sherry

 

[SherryLynn]

I figured that out, I forgot to put the FamilyID field into the query grid. Ok, now I am getting the right information. The query creates 3 columns, FamilyID, Family Name and children's names, File No. I think this should do it. I am going to try it out on the labels, but it looks good to me.

I have one last question, if you don't mind too much? For the family last name, is there a way to make it uppercase and underlined? I wondered if this is something that can be added to the module, or is there a simpler way to do this in the make table query? I hate to even ask, since you've given me so much help already.

Thanks, Sherry

 

[PaulBricker]

The underline will probably have to be done in the Report. I don't know of any constant that will do it in the code. The uppercase we can do using the UCase() function. In the label query you can just add a field
myFamily:UCase([Family]) and then reference myFamily in your Label report.
Sounds like we are almost there.

Paul

 

[SherryLynn]

Hi Paul,

Which query do I add the expression to? The make table query (my original one) queLabels-Export 2 or the queLabel Create Test (the one we created together)? Secondly, how do I reference that in the Label report. I'm not very familiar with that method. Will this only set the Family last name as uppercase?

Sherry

 

[PaulBricker]

You would put that expression in queLabel
Create Test. Then when you create your report using that query the field myFamily will be available to you to using in that Report. It will set whatever name is in the Family Field to Upper Case.

Paul

 

[SherryLynn]

Hello,

I followed your instructions which worked well, but now I have two fields showing on the label. The one field shows the combined Last name and the children's names and another field that shows just the last names in uppercase?? We're so close


Sherry

 

[PaulBricker]

Sorry, I'm losing touch with what field is what. Change the bold code to read:

Function getFamily(myID As Long) As String
Dim strSQL As String
Dim strHolder As String, strFam As String
Dim rst As DAO.Recordset
strSQL = "Select * From [tblLabels-Export] Where [tblLabels-Export].[FamilyID]= " & myID
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
strFam = rst![Family]
strHolder = strHolder & rst![Name] & " ,"
rst.MoveNext
Loop
getFamily = UCase(strFam) & vbCrLf & Mid(strHolder, 1, Len(strHolder) - 2)

End Function


Paul

 

[SherryLynn]

YEAH!!!!! It's working great. I'll try and figure out something about the underlining, although since everything is in one field, I'm not sure it can be done. Anyway - thank you so much. I could kiss your feet!!! Haha.

Paul,

I want to add this report to my switchboard. I'm not sure how to get it to run the make table query and then run the Label create test to make the labels. Do you have any ideas on that?

Thanks so much.

Sherry

 

[PaulBricker]

To run the make table query you would put this behind the button.
DoCmd.SetWarnings False
DoCmd.OpenQuery "queLabels-Export2"
DoCmd.SetWarnings True
DoCmd.OpenReport"LabelReport"

You don't have to run the queLabel Create Test. It will run automatically when you open the label report.

Paul

 

[SherryLynn]

Thank you very much. I will try that out. Thanks again for all your help with this.

Sherry