Need help creating reports

[rparker85]

Hello!
I'm pretty new to Access and I'm trying to convert some word form letters that were previously mail merged into access reports. I've got quite a few variables with the reports and I'm trying to figure out the best/easiest way to make it work.

Issue 1:
I have a few sets of reports that I need to combine based on different situations. I need each report to be it's own page.

Cover:
Report X

Response:
Report A
Report B

Form Letter:
Report 1 - 8

Report A/B will be responses and depend on which form letter report 1 - 8 is needed. Report A will need to be placed with Reports 1, 4 - 8. Report B will need to be placed with Reports 2 and 3. Report X will be the first page of every group. For example:

Group 1:
Report X
Report 1
Report A

Group 2:
Report X
REport 2
Report B

Issue 2:
The form letter reports have one static paragraph and a second paragraph with variable information that needs to be placed in it, names, dates, ect. With the mail merge I had to have 8 different reports because of the different situations, but I would like to try and bring it down to 2 reports, approve or deny, and the text in the reports would change based on other triggers in my database. I'm not sure the best way to go about doing this because it seems like i keep running into a situation where I am putting too much data into a text box and it's not able to store it all.

Issue 3:
The form letters I'm sending also include a variable logo on them. I initially tried merging this with INCLUDEPICURE but it doesn't seem too reliable especially when I'm trying to do this for mass mailings.

Would anyone be able to give me some pointers on how I should go about accomplishing all of this?

Thanks in advance!!

 

[rzw0wr]

Issue 1:
I have a few sets of reports that I need to combine based on different situations. I need each report to be it's own page.

Cover:
Report X

Response:
Report A
Report B

What situations?
Please, 1 post 1 question.
You will get better answers.

Please explain Issue 1 completely as to what you have and what you want to do.
Do you have variables or fields?
Variables are for code and fields are for records(data).
Dale

 

[rparker85]

Sorry about that, I get a little ambitious.

Basically each record I enter into my database will belong to 1 of 8 different “categories” that I have created. I have a table that contains the category titles and each has a unique ID. For example purposes we could say that we are dealing with appeals and each of the 8 categories would be a different appeal determination. Categories 1, 4, 5, 6, 7, and 8 would be denials and categories 2 and 3 would be approvals. With each appeal determination I will need to create 3 different letters that will all be sent out together. Letter 1 is a Cover page (and will always contain the same information). Letter 2 is a Letter of Explanation (and could be 1 of 8 letters depending on the category the appeal is assigned to) and letter three would be a Response Letter (which could be 1 of 2 different letters depending on if the appeal is approved or denied).

When I assign an appeal (database record) to a category I would need to produce the following group of reports:

Category 1:
Cover Page
Letter of Explanation (for category 1)
Response Letter (denial response)

Category 2:
Cover Page
Letter of Explanation (for category 2)
Response Letter (approval response)

Category 3:
Cover Page
Letter of Explanation (for category 3)
Response Letter (approval response)

Category 4:
Cover Page
Letter of Explanation (for category 4)
Response Letter (denial response)

…and so on through the 8 categories…

I was initially building this with 8 different reports and each report contained the information for the 3 different letters. While this works, I would have to produce the reports individually for each appeal and I would like to be able to do it in batches. So for example if I have 5 appeals (2 approved, 3 denied) I would like to produce a 15 page report that will contain all of the correct letters for each appeal. Since I have 8 different categories that an appeal could fall into even though 2 would be approved and 3 denied all 5 of the Explanation letters could be different for each.

I hope this makes a little more sense and thanks for the help!

 

[rzw0wr]

I think I have the idea.
Let's step back a little and break it down a little further.
We have a cover sheet. I assume you are not having any problems with that.
Do you have the reports created?
Let's start with cat 1.
What is need for that only.
I asked about variables and fields. Can you clear that up for me, please.

Dale

 

[rparker85]

Correct. I don't have any issues with the cover sheet report. It's pretty standard... date, name, address, and some static text.

I do not have the reports created in Access yet, this was my issue #2. The letters right now are form letters in individual Word docs. I started to create them in Access reports but ran into issues with needing to put more than 255 characters in a text box. Since the merged data won't alwasy be the same amount of characters I want the static text to gow and shrink as needed. In a text box I was using: "static text "&[Merge Field]&" static text". For example: "An appeal from "&[FirstName]&" "&[LastName]&" was received on "&[ReceivedDate]&" and deemed approved."

For all categories I will need the same thing. Cover letter, Letter of Explination, and Response Letter. It's the static text within the Letter of Explination and Response Letter that will change from category to category. So, with category 1 being an approval I would need the cover letter, category 1 Letter of Explination, and Approal Response Letter. The static text in the Letter of Explination would be something like "Your appeal has been deemed approved because..." and the variable text would be "...all sufficient documentation was received." If the appeal fell into category 3 the static text would be the same "Your appeal has been deemed approved because..." but the variable text would be something like "...of an administration error that occured." Design wise the third letter, Response Letter, is similar to the Letter of Explination. It has static text and and data that needs to be merged into it.

The variables would be 1) if its approved or denied and 2) why it was approved or denied. In my database I have an Approve/Deny comobo box and Determination combo box. If approved I would pick 1 of the 2 reasons for approal. If denied I would pick 1 of the 6 reasons for the denial. The reason is what I want to determine the variable text within the letters... or what letters should be sent.

 

[rzw0wr]

Can you strip out the personal stuff and post your data base?
If you can just remove the names and addresses.

Dale