Letter Database: How to manage predefined text in a report.

[lostblue7w]

I'm seeking assistance in the design of a fairly basic database.



This database will be used to produce on-demand letters to clients at the time of service. The letters are to be customizeable with predefined text to cover a range of scenarios.

Tables:

Transaction: Who, What, When, and Where, lookup field to the emloyee table, rejection reasons?
Employee: Employee names
Rejection Reason: a table with the predefined text to cover a range of situations. The primary key is something like this: A: Insufficient Payment. And the full text of this rejection reason is a memo field as the full explanation may exceed the max # of characters for a text data type field.


My biggest problem at the moment is the relationship between the rejection reason table and the transaction table. I understand it truly is a many to many relationship as any one transaction can have more than one reason for a rejection, and any one rejection reason will have multiple transactions.

I'm not a big fan of join tables, so I would normally opt for a multi value lookup field in the transaction table. The problem with this choice is that when a transaction has more than one rejection reason, the text for the memo fields run together in a long strong of text on the report. I'm wanting a way to select the rejection reasons on a form for the transaction, and the corresponding rejection reasons to appear on the report in blocks of text or paragraph form. They text needs to be self-contained not running together.


Any suggestions would be great on the design of this database.

[pbaldy]

With the join table, each reason would naturally be separated like you describe, since each would be a record.

[lostblue7w]

I've come up with a workaround. I've created a individual table for each reason. It will make the form rather large, but I will avoid all the text running together on the report/letter.

I can't ever get join tables to behave the way they should.

Please feel free to point out problems with this workaround.

[pbaldy]

I probably don't need to point the problems out; you'll run into them soon enough.

The simple event of having a new reason will cause you to basically redesign the database (new table, modify forms and reports, etc)? Personally, I'd learn how to use properly normalized join tables, but it's your baby.