Beginning Design Help

[GenericHbomb]

I still have a lot of tweaking to do but I am currently working on getting mail merges up and running. The name, address and all other basic information specific to each letter is all set. The problem I have now is that every letter is slightly unique. In the past before this database, people were copying and pasting these policies in to word. Within each letter there are certain guidelines that must be included in their next submission to me. For this purpose say there is policy 1, 2, 3 ,4 ,5. Each of these policies is anywhere from one line to half a page(over 255 characters). Each letter that I mail merge can have anywhere from only one policy included to having every policy included. I would like to automate inserting these much in the same way address etc. So for example in Access next to the company I could check box 1,2,4 in a field and then in my mail merge it would insert paragraphs 1,2,4 in my letter. If it does not work exactly like that I am okay with that. I am looking for a way to speed up this process and have it be semi-automated in the same way that address, name is.

I am just not sure if it is possible?

[jzwp11]

In short, it is possible; I'm just a little unsure about how to handle the large policy text (>255 characters). I think I read somewhere that SQL will truncate memo field text at the 255 character mark, so if you use a query you may encounter this issue. I will have to do more research on that aspect. Also, I don't think a simple mail merge can handle it. I have generally used Access to Word automation (VBA code) that dumps Access data into bookmarks in a Word template. But before we get into that, you will have to incorporate the policies and which policies you send to a company into your table structure. Have you done that?

[GenericHbomb]

I started reading up on Word automation yesterday while I was searching for an answer. I will continue to read about that so I can learn more.
I have not added the policies to my table structure. I was not sure exactly how to incorporate it in to my current structure.
Originally I was thinking of doing a combo-drop down box and just adding a field my CAP table but it would not allow me to enter the amount of text.
The other option I was considering was using a seperate table for policies and then a field for each policy but that seemed to be a little disconnected from the rest of the database.
Table wise here is what it is:

Tbl:Carrier
Pk CarrierID
Txt Carrier
Num CarrierNum
Txt Address
Txt City
Txt State
Num ZipCode

Tbl: CAP(the name for the letters)
Pk CapID
Fk CarrierID
Date Received
Date Scanned
Num 45or60
Date Revised
Date EMailed
Txt Note
Txt Status
Checkbox Mailed

[jzwp11]

I found this site that discusses when a memo field will get truncated. Since a letter can have many policies, there are a couple ways to handle a memo field. There can be a bookmark for each potential policy in the Word template, but this might cause large gaps in your document if the letter requires policies like 1,2, 5. It would also be limiting if you add additional policies in the future (more than the total number you have now).

An alternative is to combine the text of each policy into a variable in the code and just push that out to Word. I think if this last option is done strictly in code, we should avoid the truncation issue, but it would have to be tested to know for sure.

[jzwp11]

I would have a table to hold the policies

tblPolicies
-pkPolicyID primary key, autonumber
-longPolicyNumber (since you referred to them by number)
-memPolicy (memo field)


Then you would link the specific policies to a letter that is tied to the company to which it applies. Since you can have many policies per letter, you need a junction table

Tbl: CAP(the name for the letters)
Pk CapID
Fk CarrierID

tblCAPPolicies
-pkCAPPOLID primary key, autonumber
-fkCapID foreign key to CAP table
-fkPolicyID foreign key to tblPolicies

[GenericHbomb]

I followed your outline for the policy table design first. It was a little confusing, still is, with all the relationships now but it works. The truncation problem has worked out and is no longer an issue. In addition, I reset my mail merges and it merged perfectly. I do have two problems though.
The first problem is that my query, which is a combination of all the tables for the mail merge, only displays carrier results for which I have entered Policy data. Since I am working off data that previously did not have all this data about policies almost no results show up in the query except for the couple I entered information for as a test. I would say it could be overlooked but we do have one letter that has no policies in it because it is letter telling a carrier that they were acceptable. These carrier would then not show up in this query. Would it be best to make a seperate mail merge query for those that do not need this data?
The second problem I have is that in my mail merge it is creating a seperate letter that should be one letter. For instance, I have carrier A. Carrier A needs to have policy 1 and 2 in their letter. My mail merge/query is creating a seperate letter so there is
Letter-Carrier A Policy 1...Letter-Carrier A Policy 2 and so on.
I will keep playing with it and see what happens though.

[GenericHbomb]

If I have to go in and add all the policy data in order to make the query work then I will. The query must be doing that because of the new relationships.
So that just leave the problem of merging and the query creating seperate entries. I thought one of them should be a drop down so one Cap could have many policies but I don't see any field that would be able to work that way since they are all ID fields.

[jzwp11]

You will have to modify the join type. As you probably have it now it is probably an INNER JOIN which only shows records that have policy records. You will need to change the join to a left join (between the CAP table and the CAPPolicies table--i.e. show all records of the CAP table).

The second problem I have is that in my mail merge it is creating a seperate letter that should be one letter. For instance, I have carrier A. Carrier A needs to have policy 1 and 2 in their letter.

You have to concatenate the policies--this is what I was discussing earlier. This has to be done with code by looping through the policies associated with a letter and putting the text of each into 1 variable and then pushing that out to a bookmark in the Word template.

[GenericHbomb]

I tried joining both through SQL and through relationship window in database tools but was unsuccessful. In SQL I received an error about the join type.
SELECT Carrier.Carrier, Carrier.USDOT, Carrier.Address, Carrier.City, Carrier.State, Carrier.[Zip Code], Cap.[Received Date], Cap.[Date Scanned], Cap.[45/60], Cap.[Rev Date], Cap.[CAP Letter Mailed], Cap.Note, Cap.Status, Cap.Mailed, Policies.PolicyNumber, Policies.Policies
FROM (Carrier INNER JOIN Cap ON Carrier.[CarrierID] = Cap.[CarrierID]) INNER JOIN (Policies INNER JOIN CAPPolicies ON Policies.[PolicyID] = CAPPolicies.[PolicyID]) ON Cap.[CapID] = CAPPolicies.[CapID];
I was under the impression that I would just have to change the inner joins to left joins?
-----------
I am workin on concatenating the policies. I understand what it is now but have no experience in SQL. I think I am making progress though.

[jzwp11]

You might have to break your one query into two queries and then create a third query using the first two and make the join there (or if Access creates the join, alter it). Here is my best guess (not tested)

query name:qryCarrierCap
SELECT Carrier.Carrier, Carrier.USDOT, Carrier.Address, Carrier.City, Carrier.State, Carrier.[Zip Code], Cap.[Received Date], Cap.[Date Scanned], Cap.[45/60], Cap.[Rev Date], Cap.[CAP Letter Mailed], Cap.Note, Cap.Status, Cap.Mailed, Cap.CapID
FROM Carrier INNER JOIN Cap ON Carrier.[CarrierID] = Cap.[CarrierID]


query name: qryCapPolicies
SELECT CAPPolicies.[CapID],Policies.PolicyNumber, Policies.Policies
FROM Policies INNER JOIN CAPPolicies ON Policies.[PolicyID] = CAPPolicies

new query

SELECT fieldsyouwant
FROM qryCarrierCap LEFT JOIN qryCapPolicies on qryCarrierCap.CapID =qryCapPolicies.capID

I am workin on concatenating the policies. I understand what it is now but have no experience in SQL. I think I am making progress though.

In addition to queries (SQL), you will also have to use recordsets and loops to concatenate the policies applicable to each letter.

[GenericHbomb]

Thank you. I changed the names in the code slightly to but it worked perfectly.
Everything I have been reading about Concatenating says I will have to code a Concatenate function. I found the following. If I adapted it for my database would that work?

Public Function fConcatenateRecords(strField As String, strRecordset As String, strFieldSeparator As String) As String

'USAGE:
' fContatenateRecords(FieldName to concatenate, Table; Query; or SQL SELECT recordset, Separator character)
'NOTE:
' DAO recordset is being used to provide backward compatability with Access 97
' Make sure you enable the DAO reference

On Error Resume Next 'To prevent query from hanging no error trapping involved
'If no records are return, you should look for the problem with your SQL SELECT statement

Dim curDB As DAO.Database
Dim rst As DAO.Recordset
Dim strTemp As String

Set curDB = CurrentDb

Set rst = curDB.OpenRecordset(strRecordset)

With rst

If .EOF And .BOF Then
fConcatenateRecords = "" 'no records returned
Exit Function
End If

.MoveFirst

While Not .EOF
strTemp = strTemp & .Fields(strField) & strFieldSeparator & " "
.MoveNext
Wend
.Close
End With


strTemp = Left(strTemp, Len(strTemp) - (Len(strFieldSeparator) + 1))
fConcatenateRecords = strTemp

End Function

[jzwp11]

In general, the basic premise of the code should work, but adapting it to your application will take much more work. It sounds like you are catching on to a lot of this stuff pretty quickly, and you are willing to learn, so it might be time to jump into VBA coding. I taught myself VBA code with the help of a book (Access VBA Programming for Dummies by Alan Simpson) a few years back. The book was based on an older version of Access; I assume that there are similar, newer versions available. There was a good example of how to automate Access with Word in the book which will make you adaption a little easier.

[GenericHbomb]

I will look in to that book. Other than trying to figure out how to concatenate I think the database is done...at least until it starts being implemented. Thanks!

[jzwp11]

You're welcome.

Keep us posted on your progress relative to the Access to Word automation.

[GenericHbomb]

Edit: Fixed the problem

So I finished the rest of the work on the database, such as reports and so on and have been out of the office on travel for a couple weeks. Now I wanted to go back and give the policy mail merge a shot.
I was making the queries, the first query was fine but then I went to make this one and it keeps telling me the join type is not supported this time but I do not see why it would not be.

SELECT CAPPolicies.[CapID],Policies.PolicyNumber, Policies.Policies
FROM Policies INNER JOIN CAPPolicies ON Policies.[PolicyID] = CAPPolicies

Possible causes:

• Your SQL statement contains multiple joins in which the results of the query can differ, depending on the order in which the joins are performed. You may want to create a separate query to perform the first join, and then include that query in your SQL statement.
• The ON statement in your JOIN operation is incomplete or contains too many tables. You may want to put your ON expression in a WHERE clause.

I would imagine the end would have to be CAPPolicies._____ but not sure what the blank should be?
EDIT: Fixed the problem