fill-in-the-blank problem

[Seth Schrock]

I'm working on making a fill in the blank form for police reports. Basically, I need to be able to select a crime, fill in the appropriate names, amounts, etc., and then be able to print a report that has the information as if it was all typed out with no lines that are very long making it obvious that you just filled in the blank. I hope this makes sense.

My problem is that I don't know the best way to do this. I had thought of splitting the sections of text up between the "blanks" and then using a query to concatenate them together along with the names from the form. I think that this is how I should do it, but I don't know exactly how to do it. To be normalized, I think I would have to have a table of crimes and a table of text groups related to the table of crimes (a text group being the text between the "blanks"). So I could then make a query for the text groups for the selected crime. This produces two problems for me: 1. There can be multiple crimes listed and I don't know how to pull all of the text groups for all of the crimes. 2. I don't know how to concatenate records together (as opposed to fields which I can do). Is there a better way?

[NeoPa]

I can only speak for myself, but I have no idea what these "blank"s are that you're referring to. Without that understanding none of your post makes the slightest sense to me. Perhaps you could explain what you mean.

[Seth Schrock]

Here is a sample of what is being used. Each crime listed has blanks that need filled in for the report. For example, the very top crime listed on the sample could be filled out like this (words in bold are what go in the blanks):

John Doe did recklessly, knowingly, or intentionally damage a car which is the property of Jim Bob without his/her consent and the damage resulted in a pecuniary loss in the amount of $1000.00 contrary to section 35-43-1-2(A)(1)(I) of the Indiana Code.

I'm calling the each part between the Bold text a text group. So the Text Group 1 is " did recklessly, knowingly, or intentionally damage ", text group 2 is " which is the property of ", etc.

[NeoPa]

So, essentially, you want to be able to print a report (somewhat similar to the PDF attached) for all the various crimes and misdemeanours stored in your database, such that they come out looking as smooth and as little disjointed as possible.

Please confirm this is your starting point, as heading off in that direction can be done, but needs some careful thought to do in a sustainable and maintainable way. I don't fancy heading off anywhere until I'm sure we're both facing the same direction.

I'm also assuming that there may be sections of the report which must be less free-flowing than others. Certain paragraphs that fit in a certain place regardless of what's in any of the blanks.

[Seth Schrock]

Paragraph 1 is completely accurate except that I don't want every crime in the database; just the ones that were committed. Paragraph 3: The only separation would be between crimes. Each crime is described in only one paragraph.

[NeoPa]

Seth:
Paragraph 3: The only separation would be between crimes. Each crime is described in only one paragraph.

Quite different from the PDF then. That seems to handle one crime per page, with Header bits (Perp ID, Officer's name, etc.) at the top and Footer bits (Signature, Date, Approved on and by, etc.) at the bottom.

How would you expect the report to look if not like the PDF attached?

[NeoPa]

So as I'm not just asking you questions for ever, let's look at something we can look at with the information already available.

Somewhere (yet to be determined) in your report you will have a control - probably a TextBox - which will be where most of your text is managed. Assuming you have something simple to say like :
On 1 December 2012 I arrested John Smith for the crime of walking naked down the high street.
But, the date (arDate), the name of the perpetrator (arName) and the crime (arCrime) are all variables taken from the underlying query. That would mean a formula in your main control something like :

="On " & [arDate] & " I arrested " & [arName] & " for the crime of " [arCrime] & "."

I hope that gives you a preliminary view of what we're expecting to be doing to ensure the text is free-flowing.

[Seth Schrock]

The PDF is actually showing multiple crimes (five to be exact). They are just all listed because they are commonly committed at the same time. Currently, the police officers just put a check mark in the check box that precedes each crime listed and then fills in the blanks for that crime and leaves the others blank. The PDF previously posted is for criminal mischief. Here is one for criminal trespass. Notice how the information needed in the blanks is different (which is part of my problem). My goal is to have it so that if one crime is committed, then only one crime is listed. If ten crimes were committed, ten crimes would be listed. The PDF attached in this post lists six crimes.

[Seth Schrock]

Just saw your second post...

Your example is how I initially thought I would do it, but I ran into a problem. Is it possible to store in a table the different options for wording based on the crime committed? For example, for a criminal mischief class A misdemeanor, data source would be

=person & " did recklessly, knowingly, or intentionally damage "
& crime 
& " which is the property of "
& complainant
& " without his/her consent and the damage resulted in a pecuniary loss in the amount of "
& cost
& " contrary to section 35-43-1-2(A)(1)(I)

(separate lines for readability)

A criminal trespass would be like:

=person
& " did knowingly or intentionally enter the real property of " 
& complainant
& " located at "
& address
& " after being denied entry by "
& complainant
& " that said "
& speech
& " not having a contractual interest in the said real property, contrary to section 35-43-2-2(A)(1) of the Indiana code."

Does this make my problem more understandable? Also, wording might need to change in the future so storing solely in a textboxes data source would be awkward.

[TheSmileyCoder]

Its certainly a very interesting discussion, and a nice challenge. I liked the challenge of having variable amount of fields, with different names, depending on which penal code is selected. Obviously one cannot make a form and a report for each single penal code, the work would never end, and access would probably run out of item ids around the same time you lost the last strand of hair.

Well, challenge accepted. I spent roughly 1½ hour coming up with this Proof of Concept solution. I estimate it will take a good deal more time to implement it and adapt it.

Basicly my proposed solution consists of two tables. A table of config that contains a few "fixed" fields such as Penal Code and Penal Title which I guess would be common for all penal offenses (Not that they have the SAME penal code but they all have A penal code and title.)

Now depending on what type of offense, you might need X blank fields. In my example I just made 5 fields calling them tx_FieldName1, tx_FieldName2. Now the idea behind this is that the manager writes in the penalcode, and decides the fieldsnames, so that they match the individual offense. Further more he writes the standard text with placeholders in which to place the values of tx_FieldName1 and so on.
This configuration is demonstrated in frm_Config of the attachement.

Now the dataentry is another form, where once the appropriate penal code has been selected by dropdown, the caption of the controls is determined by what was entered into the config table, and example text is loaded and displayed for the user.

Now its about an hour since I should have gone off the dream, and I just spent alot of time retyping this since some idiot at Lenovo thought it would be a good idea to place a BrowsePrevious button night to the left button, so I lost my first post.
Therefore I am going to just add the example without as much explanation as I had planned to write.

Start out by opening the db (Written in 2010 but saved in 2003 format, so I hope you can open it ok). Then open frm_DataEntry and try playing around a bit. Once you understand how it works, try opening hte frm_Config and create your own Penal Code setup. Hope you like it

[NeoPa]

It seems clear this is far more complicated a problem than was even hinted at in the question. I'm not sure what Smiley's suggesting, as his logic seems to be in the attachment rather than in the post, but I see a way of handling this, quite non-trivial, situation :

Each Crime record would need an associated Crime Type. In the Crime Type table would be fields containing the fixed text, as well as fields indicating which, from a known set of fields in the Crime table, would be required to fill in the blanks and which order they appear in. The query that provided the data for the report in question would need to include the Crime table data, as well as the linked Crime Type data. It would also need to provide the blank fields by taking the values of any one of the other fields depending on the Crime Type information regarding which of the Crime fields is required for each blank.

As Smiley has already gone to a deal of trouble providing an example database though, I suggest you look through that first. It may give you a head-start. It may even work on lines similar to what I'm suggesting. Remember though, the most important step is the conceptual design. The queries will never do a good job if they're built on inappropriate foundations.

[Seth Schrock]

Absolutely incredible Smiley! There is no way that I would have figured that out on my own. I have yet to try to duplicate what you have done, but after having entered my own penal code into the database and filling it out on the data entry form, I have a pretty good idea of what is going on if not the exact code. Thanks so much for accepting the challenge.

@NeoPa, I'm not totally sure, but I think that what Smiley did was along the same lines as what you described. As it turns out, this is so complicated it is hard to visualize exactly what you are describing with my limited knowledge.

I must admit, when my brother (who is the police officer requesting this) asked me about it, I thought it would be easy. Then I started to plan it...

[TheSmileyCoder]

I am glad you liked it. My setup is similar but not exactly the same as NeoPa described. I choose that the only known fields to be filled in for each record was Penal Code and Perpetrator Name.
I left the rest of the fields (demo has 5) to be entirely customisable. How many fields would be required for the final application I don't really know, but it should be easy enough to expand upon.