Doable??? Label Report with a grouping

[Valery]

Hi all!

I am stumped by this one. I did a report where individual letters are sent to multiple recipients. Works fine. Tried to do the same for a label report - it destroys the label format.

In the letter reports, the recipients are grouped by UnitNum. Also, the query pulls recipients according to their status. Can't seem to do this for the labels.

Here is what a sample label should look like:

M. John Doe
Mrs Jane Doe
M. Little Doe
333 Pole Street
City, Province
Postal Code

My next step - or should it be in this step (???) - is that I would like to link a given report to the label report - so the user, in one click, could print labels directly related to the recipients involved in that report.

Makes sense to anyone?

THANK YOU

 

[sneuberg]

- it destroys the label format.

I don't understand what you mean by that. Could you upload the database or give us a screen shot of the report in design view and a screen shot of the record source of the report?

Did you use the wizard to create the label report?

 

[Valery]

Thank you Sneuberg. Will rephrase - that was so incorrect. And providing a sample is a great idea.

The label report was created using the Label Wizard. But the UnitNumber as well as the tenant names are all on separate labels despite having grouped them like in my report (letters) which is working fine.

The result is the following lines printing on separate labels, not onto ONE label per unit number.

Unit Number 7
M. John Doe
Mrs Jane Doe
M. Little Doe
Address Line
City, Province
Postal Code

Here is a sample of the database with one report and one label report. Please CONSIDER the following:

1) The address will be in a label control as all recipients will always have the same address - just different Unit numbers

2) They are using Avery 5163 labels.

3) Ideally, I would like a cmdbutton that generate labels based on the query of a given report. So user opens/prints the report, clicks a button and print labels with the same recipients selected by the report and in the same printing order as the report (letters).

A big THANK YOU for helping!!

 

[sneuberg]

Your upload didn't include the queries for the report so just to see what could be done I created a new report, rptWantToBeLabels in the attached database, that groups the information the way you want. But I didn't bother trying to make it match up with Avery 5163 labels because it's not going to work anyway.

The main problem is the variable number of tenants which varies between two and four in the sample data you provided. For the labels to print out correctly the records on the report have to be the same size. I don't know how to do that with this data as it is.

The only thing I can think of right now is to add a Yes/No field to the tblTenants the would indicate whose name would be on the label, i.e., limit the name to one name.

 

[sneuberg]

I had another thought. Ironically if your data were not in normal form (names in separate columns) you could do this without any problems. So the question is can the data be made to appear that way with a query. If you can choose a maximum number of names, let's say four, it might be possible to denormalize, so to speak, the table with subqueries. With the data in separate columns you could easily get what you want with the wizard.

I'm not very good when it comes to subqueries but some of the forum members can knock them out in no time. Maybe you can attract their attention by starting a new thread with a title that better explains what you need. Perhaps "Query to group multiple records into single records for address labels". I suggest including a screen shot of the tblTenants and a link to this thread.

 

[Valery]

The report did include a query - just not an outside saved query... It can just be saved if you need it as a separate file. Why create a new report?

The option of having one name on the label is absolutely not possible. I thought of a mail merge to Word but I really would like to avoid that for every report (there will be many).

I will try to repost.. submit it as you suggest - good idea and figure out how to link to this thread.

Thank you for the prompt response and best efforts.

 

[sneuberg]

The report did include a query - just not an outside saved query... It can just be saved if you need it as a separate file. Why create a new report? .

When I ran the rptLabels report I got the message, "The record source qryLabelMembers specified on this form or report does not exist" so I couldn't look at the record source you have for this. So that's why I created a new report.

I will try to repost.. submit it as you suggest - good idea and figure out how to link to this thread.

Just copy and paste this:

http://www.access-programmers.co.uk/forums/showthread.php?p=1476138#post1476138

 

[Valery]

Mmm... strange - I get no error message...

Here is the query for the main report:
SELECT tblUnits.UnitID, tblUnits.UnitNum, (IIf([Gender]="M","M. " & [FirstName] & " " & [LastName],"Mme " & [FirstName] & " " & [LastName])) AS Salutation, tblTenants.TenantID, tblTenants.Unit_Num, tblTenants.FirstName, tblTenants.LastName, tblTenants.Phone, tblTenants.Status, tblTenants.Gender, tblTenants.DOB, tblTenants.MailList, tblTenants.MoveInDate, tblTenants.Comments
FROM tblUnits LEFT JOIN tblTenants ON tblUnits.UnitNum = tblTenants.Unit_Num
WHERE (((tblTenants.MailList)=True))
ORDER BY tblUnits.UnitID;

And for the subreport:
SELECT TOP 1 tblRentalRates.RatesID, tblRentalRates.ApprDate, tblRentalRates.EffectDate, tblRentalRates.[1Bed], tblRentalRates.[2BedLow], tblRentalRates.[2BedHigh], tblRentalRates.[2BedAdapted], tblRentalRates.[3BedLow], tblRentalRates.[3BedHigh]
FROM tblRentalRates
WHERE (((tblRentalRates.ApprDate)<=Date()))
ORDER BY tblRentalRates.ApprDate DESC;

Thank you for letting me know. I will save them separately.

The link you gave me returns Page not found. So funny - my queries give you an error and your web link gives me one, LOL !

 

[sneuberg]

The main report and subreport are working, but they didn't seem to concern this problem. The rptLabels is the one that was giving me the error. But I don't need to see record source any longer anyway.

I don't know about the link. I click on it and it takes me to this thread. Try this one. I'll leave off the post number.


http://www.access-programmers.co.uk/forums/showthread.php?p=1476151

 

[sneuberg]

I wrote some code that might satisfy your needs if nobody can come up with a query to do this. You can try this out by opening the form (Labels Form) in the attached database and clicking on Open Label Report. This button runs the code which takes the data from the tblTenants table and puts it in the tblLabelLines in a form that is suitable for labels. Then it opens the rptLabels which is based on that table. I created this report using the wizard and chose the Avery 5163 labels and a font size of eight. This allows eight lines on the label so the code selects the first three (first as lower TenantID) tenants names if there are more than three. For labels where less than eight lines are used the bottom lines are left blank. I could fill these from the bottom up if you prefer. Only tenant on the mail list are shown. If you want to see it with all of the tenant names, open the qryTenants in design view and erase True from the MailList criteria.

I haven't documented the code yet. I thought I get your comments on it first. This might actually end up a better solution than a query as I believe any query that can do this will be very complicated and difficult to maintain. The drawback to this code approach is that it uses and reuses the table tblLabelsLines which can cause bloat. If there were thousands of tenants involved that could be a concern. It probably isn't in your case. The bloat can be reduce by a periodic compact and repair.

Concerning your desire:

My next step - or should it be in this step (???) - is that I would like to link a given report to the label report - so the user, in one click, could print labels directly related to the recipients involved in that report

I think it could be set up so that you could print the letter reports and the labels with one click, but don't you have to change the paper to the labels in the printer? Wouldn't two clicks be better?

Please give me your feedback on this proposed solution and I'll provide a version with documentation and instructions on how to modify the code for different label sizes, etc. Also the address is hard coded right now. I assume you will want this stored in a table so that the user can update it through a form.

 

[Valery]

Hi,

I understand the creation of labels based on a table that has the lines in separate fields vs separate records. But how will that table created, each time the users want to output labels for a certain report?

I remember a database - a long time ago (like 25 years!) where the programmer concatenated the fields and then separated them again... this is what I thought it would be for these labels.

Not sure how to use what you have graciously provided me with...

Notes: Yes, we may have 1,000 tenants. They need only ONE size of label. The address is not in any table as it is the same for everyone and will remain the same for everyone. They are living in a COOP.

Please assist further on how to create this table, over and over again?

Until I can resolve this label issue, I am working on something that will allow the users to pick and choose the recipients for a given report. Providing choices like "All Members and Occupants", Members Only, Occupants Only, and then a possibility to select tenants, one by one...

THANK YOU for your efforts. Looking forward to hearing from you.

 

[Valery]

Hi Sneuberg,

I found this on the Web. It is way too advance for me but perhaps you can make sense of it. It seems to be what is needed here... Correct me if I am wrong...

http://accessdatabasetutorial.com/2...-multiple-rows-into-single-line-in-ms-access/

Thanks! Can't wait to have your feedback!

 

[sneuberg]

Hi,

I understand the creation of labels based on a table that has the lines in separate fields vs separate records. But how will that table created, each time the users want to output labels for a certain report?

Each time the report is run the code deletes the records in the table and then creates new records based in the input query. There is nothing you have to do other than click the button.

I remember a database - a long time ago (like 25 years!) where the programmer concatenated the fields and then separated them again... this is what I thought it would be for these labels.

The problem here is the names are in different records. You can't concatenate from one record to another. Usually if you need data from another record you get it with a subquery. But given that a variable number of records involved I believe that any solution involving subqueries is going to be extremely complicated. Your second post came in as I was writing this. If you look at the solution at the link you will find the code that it uses is very similar to mine and in effect is doing the same thing, just more generically. Anyway if you want to exhaust the possibility of a non code solution I suggest you start another thread with that question

Not sure how to use what you have graciously provided me with...


Please assist further on how to create this table, over and over again?

I didn't provide you with any instructions yet. If you are satisfied with the output my solution provides I will give you a better documented solution with instructions. If you want to wait and see if there's a non code solution or pursue the other code solution you just found, please let me know.

Notes: Yes, we may have 1,000 tenants. They need only ONE size of label. The address is not in any table as it is the same for everyone and will remain the same for everyone. They are living in a COOP.

If you have a split system and the bloat becomes a problem I can help you set up what they call a side table. If this isn't a split system then you could check the option for compact and repair on close and shouldn't have any problems.

You don't have to put the address in a table. You can leave it in the code if you want. My instructions will tell you how to change it in that case.

 

[Valery]

Thank you. Really need to think about all this and re-examine everything closely. It is getting confusing (for me). Will definitely get back to you - thank you so much!

 

[Gasman]

PMFJI,

Could you possibly use Allen Browne's Concatenate function, and use carriage return and line feed as delimiter?

http://allenbrowne.com/func-concat.html

 

[sneuberg]

PMFJI,

Could you possibly use Allen Browne's Concatenate function, and use carriage return and line feed as delimiter?

http://allenbrowne.com/func-concat.html

If you look at the data involved you will see that the number of people she wants on the label varies from one label to the next. If it were a fixed number of people then I'm fairly certain the Allen Browne's Concatenate function would be a solution.

 

[Valery]

I believe this is where the link I supplied led to... I suppose it is possible - it's sound right but I would have no idea how to apply this to my sample, let alone a label report.

If anyone wants to try it, I sure could use it!

Thank you!

 

[sneuberg]

Good news. Inspired by Allen Browne's Concatenate function code I've come up with another solution that doesn't require a temporary table. I still have some work to do on this but I wanted to let you know where I'm at. Basically I'm using Allen Browne's concept to return a single field. So you can use the function I've come up with as an expression in a query. The function, found in module GetField Procedure in the attached database, is:

Public Function GetField(UnitNo As Long, TenantOrd As Long, FieldName As String, Optional TableName As String = "qryTenants") As Variant

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim TenantCount As Long

TenantCount = DCount("*", TableName, "Unit_Num = " & UnitNo)
If TenantOrd > TenantCount Or TenantOrd < 1 Or TenantCount = 0 Then
    GetField = Null
    Exit Function
End If

strSQL = "SELECT " & FieldName & "  " & _
    "FROM " & TableName & " " & _
    " WHERE Unit_Num = " & UnitNo & " " & _
    "ORDER BY TenantID ;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.Move TenantOrd - 1
GetField = rs.Fields(FieldName)
rs.Close
db.Close

End Function

Which I include here mainly as a challenge for forum members to do this with subqueries. I now retract my previous assertion that a subquery would be overly complex. Since this code is fairly simple the subquery might not be too bad although there might be a question of performance yet.

If you look in the attached database and open the qryLabels you can see how this function is used in a query. If you look at the SQL of this query you will see that this query gets the distinct value of the unit number. The rest of this query is expressions that use the above function, GetField. Going back to the design view you will see that the expressions are of the form:

Expression Name: GetField(theunitnumberfield, thenumberofthetenant, thefieldname)

For example

FN1: GetField([Unit_Num],1,"FirstName")

Gets the first name of the first tenant in the unit number of that record. First being define as the tenant with the lowest TenantID. If you widen the column and look at the other expressions I think you will see the pattern. The function could retrieve any field in the input query so

Gender1: GetField([Unit_Num],1,"Gender")

Would give you the gender of the first tenant in the unit number. You could make expressions like:

Title1: IIf(GetField([Unit_Num],1,"Gender")="M","Mr.","Ms.")

The rptLabels was create with the wizard from the qryLabels. You can't double click on the report to run it. Ignore the form. It doesn't do anything yet. Which is the problem I still need to resolve. The function doesn't work if the input query has form references, something you will probably want. As an aside the Allen Browne's function wouldn't work with form references either. The code needs querydefs to handle this. I'm working on that.

 

[plog]

snueberg invited to this thread to see if I could provide a SQL based solution. If his previous post using VBA doesn't work for you, let me know and I will help on one that uses just queries.

Reading through this post, I'm not entirely certain what is needed. So, try sneuberg's solution and if needed, post back here and I will give queries a shot.

 

[Valery]

WOW!!!! get back to you later today - have people coming over right now. TYTYTYTYTYTY!! I am blessed!