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

 

[SherryLynn]

Hello,

I'm not sure how many characters - I think it depends on your font size and type. I could estimate based on what I use. The Family is left justified currently.

Sherry

 

[SherryLynn]

One thing I just thought of. What if the query we (you) created had the Family last name and Family ID in one column and the kids names together in another column? Would that work easier?

Sherry

 

[PaulBricker]

I don't think putting them in separate columns or fields would help. One thing you can do is simply add more spaces between rst!Family and myID by increasing the spaces between the quote marks in the string.
strFam = rst![Family] & " " & myID
This will now insert 6 spaces between the Family and the ID so that they are separated by more space.
As for the kids, I don't know what will happen if there are more kids that space on the line. See if you can set up an example and see what happens. Give the Smiths another dozen kids or something and test it out.

Paul

 

[SherryLynn]

Thanks Paul. I will try that and see what happens.

Sherry

 

[SherryLynn]

Hello,

Hmmm...I have a new problem. I created a few more dummy records with more children's names etc. I then re-ran my make table query which pulls together the fields which we have your query running on, I get the same run-time error 3464. I looked at the new table created by my make table query and the Family ID field is reset to Autonumber instead of text. I am assuming each time I run this query this will happen. Can something be changed in the original query to keep it from doing that?

Also, I added extra children's names to see what would happen on the label, and they don't show up. I tried adjusting the size of the field in design view of the report and that won't do it. ??

Sherry

 

[PaulBricker]

There has to be something in one of your fields that is changing it back to Autonumber. What is the field you are using to populate that field in your MakeTable query. Is it an autonumber field. Are you concatenating any fields to get a certain result?
As for the kids, that could be a problem. We would need to see how many characters we can put on a line and try and adjust the code accordingly. One other thought is try sending them to Word and see if you can manipulate them easier there. As I said, I don't know anything about labels.
What's the max number of kids before they don't show up on the label.

Paul

 

[SherryLynn]

Hi Paul,

The field I am using if the FamilyID field which is the primary key field for that table.

Regarding the labels. Once I tried adjusting the field further the children were showing up on a third line. That seems to be ok, but I still need to have the file number justified to the right. I tried putting those spaces in, but then the labels aren't formatted for the file properly.

Any ideas?

 

[PaulBricker]

When you say the labels aren't formatted for the file properly what do you mean. I haven't found a right justify constant that will allow me to do that so I'm trying to create one.

Paul

 

[SherryLynn]

Hi Paul,

On the labels the file number needs to be on the far right because of how they are physically filed. I need the number to be at the right edge of the label kinda like the example below:

SMITH xx JONES S0099876
Mary, Ken, Jim, Bob

I'm sorry, I didn't think about this when we were pulling the data together etc.

Sherry

 

[PaulBricker]

It really wouldn't have made much difference when we were pulling things together. The problem is everthing is now in a single field and therefore isn't independent of the other data in the field. If we bring it all together we lose the abibity to justify it according to your needs. If we leave it separate then we can't get it into one label (at least as far as I know). I don't suppose there is anyway that you are aware of that you could put 2 textboxes together to create one label is there? If you could do that, we could put the Family and Name in one box and the ID in the other. Left justify the Family/Name info and right justify the ID info. I don't know labels and don't have any to practice one.

Paul

 

[PaulBricker]

Sherry I just played with the label wizard and it does allow you to set fields in different location on the label. If we put the info in two textboxes would that work for you?

Paul

 

[SherryLynn]

Yes, I think that would work very well. Can we do that?

Sherry

 

[PaulBricker]

Sherry, we sort of already have it. You have the ID field in the query already so if we just modify the code a little it would look like this:
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 [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

The only thing I changed was the value for strFam. This will give you the ID in the first column of your query and the Family/Names in the second column. Then if my guess is correct when you use the Report Wizard you can put the Family/Name field on the left side of the label and pull the ID over to the right.

Let me know how it goes.

Paul

 

[SherryLynn]

Hi Paul,

I was just working on this again. The change to the module worked very well. However, I realize now that we have been concentrating on the FamilyID being on the label when it's the File# that needs to be there (I think I wrote that somewhere along the way). Oops! I think I'm getting fuzzy headed with all the stuff we've been doing..haha. Is this a problem or something simple to fix? It still will probably work better to keep the family names together and the file number in a separate field.

Also, I'm still not sure how to resolve that issue around the FamilyID field changing to an autonumber field each time I run that make table query. Is this fixable?

Thanks so much,
Sherry

 

[PaulBricker]

Sherry, if there is one File# for each family then you should be able to add that field to the query with the rest of the info and just use it on the label instead of the ID field. Now, as for the make table, I'm just not sure how to answer that question. Is there some reason we have to have a Table at all. This information is already in the query and therefore should be available to the label report the same way a Table is. Do you need the table to retain some of the information for your records or something.

Paul

 

[SherryLynn]

Hi Paul,

The make table query pulls information which meet a certain parameter (i.e. files(records) which were created "today" or between a date range as well as an "Area&quot. If this can be worked into the query you created or part of the report, then I don't really need the table. I only created the table to get those particular results to print the labels.
What do you think?

Sherry

 

[PaulBricker]

If your info is all in the Make Table query that creates tblLabels_Export, just change it to a Select query and then set the source for the label query that calls our Function, to the make table (now a select) query. You would have to change the name of the table tblLabels_Export in the code we use to the name of your query but that should be all you have to do. If you have any questions, post the names of your queries and I'll modify the code.

Paul

 

[SherryLynn]

Hello,

I changed the make table query to a select query. The name of this query is queLabels-Export 2. This query creates (did create) the table tblLabels-Export. Then I changed the expression in the query we created (named queLabel Create Test) from tblLabels-Export to queLabels-Export 2. In the module I changed the values where they were tblLabels-Export to queLabels-Export 2. In the label report I changed the source to queLabels-Export 2. When I run the report it asks me for the appropriate parameters, but then I get a run time error '3061': too few parameters. Expected 3. When I choose Debug is stops at the line:
Set rst: CurrentDb.OpenRecordset(strSQL, dbOpenDynaset).

What did I do wrong?

Sherry

 

[PaulBricker]

I know you have paramters in qryLabels_Export2 but how do you pass the parameters to the query. Do you have a form open or something like that.

Paul

 

[SherryLynn]

Hi Paul,

Sorry I haven't had a chance to get back here till now. Regarding the parameters for the query, boxes appear asking for the information, there are three. Beginning date range, end date range (between...and), and area. Once I enter the information the querry comes up with those results.