Omitting duplicate data and merging records

[anewor]

Ok, i'm not entirely sure that what i'm about to ask is possible, but here goes.

there are 2 tables CLIENT & LOAN which are linked by a junction table. A person can only appear once in the client table but any given number of times in the loan table.

i'm ultimatley trying to write a query which can be used for mail merges.

trying to use the Loan id (from the Loan table) to link clients together as there is nothing showing which clients have loans together. so i was thinking something about duplicate addresses, although that means that only the mr or mrs of the couple will have the letter not both. however, multiple people can have the one loan and may or may not live together.

i think the easiest way to explain it may be to draw it...so i'll attach a pic.

so can anyone think of a way that i can take two records in the client table which are linked by their related loan id's, and mash them together so i end up with a mailing address stating both names and only sending a letter out once?

 

[GumbyD]

I looked at your diagram and then created some tables to try an replicate your issue. When I tried to do it just using queries it became pretty bad, pretty fast. I think your best bet is to use VBA code to process the information. If you can create a query that lists all of the clients that you want to add to the mailing list and then sort it by loan number, name, and then address. You will need to make sure you only get one record for each real person - so if a customer has three loans only bring them in for the first loan number by using totals in the query. You should be able to then set up a loop with a nested loop or loops. First loop will be the loan number. For each loan number loop through the addresses - if they are the same loop through the names and then put them togeter - if they are different then get create seperate entries.

I hope that makes sense and that you write a little code. If not, if you provide me with some of the table names I will attempt to assist you in writing the code.

GumbyD

 

[anewor]

Thanks GumbyD

What you said kinda made sense to me, although i got a little confused by this whole looping thing. As you can possibly guess I've very little experience in VBA code......in fact none, but I'd really appreciate it if you could point me in the right direction.

I've attached a version of my database, which will hopefully answer any questions, and i thought might be easier that just naming tables in case i forgot something vital (as is often the way with me)

Again, thank you

 

[anewor]

my attachment isn't attaching


damn file size limits......need to put them in bigger font so blonde ppl like me don't take 2 hours to work out why their file isn't attaching..


ok here goes.....

 

[GumbyD]

anewor -

Please see the attached database for the code. You can run the code in the immediate window by typing CreateMailingList and then pressing return after it. When you run the code it will populate a table called MailingLabelList. I put comments in the code to help you make sense of it, but if you have questions post back and I will address them. The code does not handle 3 or more names on the same loan (I was not sure if you needed it to or not). I hope it all works for you!

GumbyD

 

[anewor]

Thank you soooooooo much GumbyD.

I've hit a couple of snags which I'm kinda hoping you could look into if I'm not pushing the friendship too far

We do have some loans which are associated with 3 or more names (4 is the most we've had to date).

When i run the code i get a msg box telling me that an error has occurred.

Runtime Error 3163 - the field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

And finally, the field Mailing Suburb is a look up from the table Client Mailing Suburb which also contains state and postcode.

In the mailinglabellist table the suburb is showing as a number (its id)..... do you know anyway i (or you) could change this so it appears in MailingLabelList as text for Suburb and then the state and postcode fields too?

Again, thank you so much for taking the time to sort all this out for me. I really appreciate all your help

Rowena

 

[anewor]

bumping question up the list

 

[GumbyD]

anewor -

Sorry I had sick child issues and have not been at my computer. As for the error message - I think it may be related to the field size of the MailingName field in the the MailingLabelList table. Try changing the Field Size to 255.

As for the other updates I will take a look at the code and adjust it, but I have to catch up on my "real work" and not the fun stuff today. I will hopefully get a post out tomorrow (12/17) sometime.

GumbyD

 

[anewor]

Absolutely no need to apologise. I hope your child is well now and that you didn't have too many piles of working waiting for you

field sizes....yes...a bit embarrassed i didn't work *that* one out....i changed it and that particular error is gone and has now been replaced with:

Run-time error '3021' No current record

No even pretending to know what that one means..... apparently it refers to "If strLoanNum = rstLoan!LoanNumber Then" but my computer has probably realised i have no clue and is playing with me (it gets kinda evil like that)

i hope you have a great day...

Row

 

[WayneRyan]

Row & Gumby,

Nice thread! I wasn't interested in the mailing, but liked the concept here.
The code that Gumby wrote is quite good. I like VBA code and probably use
it too much.

I think that the error you're getting is due to two sequential recordset reads
when the loan number is the same. After adding the record(s), the
code does not have to go to the next record because the top of the
While loop will do that (after saving the current record's data).

After it did the last pair, it skipped past EOF by reading two
records.

Also the code assumes that if the current record is the same loan
that it will write out a record no matter what. This means that you
can't mail to more than two people at an address.

I rewrote a section of the code that should handle this.

Existing Code:
=============

rstLoan.MoveFirst                      ' This loop assumes that you are at the
Do Until rstLoan.EOF                   ' first (of a possible pair).  It can not
   'set variables from this record     ' handle three customers on the same loan.
   strLoanNum = rstLoan!LoanNumber     ' 
   strAddress = rstLoan![Mailing Street Address]
   strName = rstLoan!FullName
   strSuburb = rstLoan![Mailing suburb]
   'Move to the next record
   rstLoan.MoveNext     <------------------------------------------+
   'Check to see if the loan numbers match                         |
   If strLoanNum = rstLoan!LoanNumber Then                         |
      'Check to see if the addresses match                         |
      If strAddress = rstLoan![Mailing Street Address] Then        |
         'loan - address match add the record to the label table   |
         strNametoWrite = strName & " and " & rstLoan!FullName     |
         GoSub addrecord                                           |
         'move to the next record                                  |
'Remove  rstLoan.MoveNext ' "Double Read" followed by this one ----+
      Else                                                         |
         'Loan numbers =, addresses don't match write two records  |
         strNametoWrite = strName                                  |
         GoSub addrecord                                           |
         strNametoWrite = rstLoan!FullName                         |
         strAddress = rstLoan![Mailing Street Address]             |
         strSuburb = rstLoan![Mailing suburb]                      |
         GoSub addrecord                                           |
         'Move to the next record                                  |
'Remove  rstLoan.MoveNext ' "Double Read" followed by this one ----+
     End If
   Else
     'Loan numbers are different
     strNametoWrite = strName
     GoSub addrecord
   End If
   Loop

New Code:
=========

LastLoan = ""
rstLoan.MoveFirst
strLoanNum = rstLoan!LoanNumber
strAddress = rstLoan![Mailing Street Address]
strNameToWrite = rstLoan!FullName
strSuburb = rstLOan![Mailing suburb]

Do Until rstLoan.EOF
   If rstLoan!LoanNumber = LastLoan Then
      If strAddress = rstLoan![Mailing Street Address] Then
         strNametoWrite = strName & " and " & rstLoan!FullName
      Else
         GoSub addrecord
      End If
   Else
      Gosub addrecord
      LastLoan = rstLoan!LoanNumber
   End If
   rstLoan.MoveNext
   strLoanNum = rstLoan!LoanNumber
   strAddress = rstLoan![Mailing Street Address]
   strNameToWrite = rstLoan!FullName
   strSuburb = rstLOan![Mailing suburb]
   Loop

That was a lot of work.

Wayne

 

[GumbyD]

Rowena -

OK - I think I finally got it all worked out. This turned out to be much more difficult than I originally imagined. I had to add 3 queries and I updated the table and the code. I hope it all works for you!

GumbyD

 

[anewor]

thank you thank you thank you thank you!!!



the whole thing works beautifully.

thank you sooooooooooo much for your help.

i hope you have a wonderful Christmas and a brilliant New Year!!

 

[GumbyD]

Your very welcome! Have great holidays down under!

GumbyD