Display actions in text

[rc-alex]

Hello,

I have a query that calculates percentages, as well as other fields that are yes/no, etc. I need to output text on the reports based on these values.

For example, if a column is between 0 and 60% I might display "Performance was sub-standard. Recomending X,Y,Z. Steps taken included...."
but if performanc is between 60% and 80%, or 80% and 100%, different phrases would be output. What is the best way to put text phrases like these on a report based on values? The verbiage and field values displayed need to copy over well to MS Word.

I thought about calculated fields for earch record but since many paragraphs will be the same, seemed like redundant wording and not a good way to go.

Thanks.

 

[Beetle]

Do you only have three versions of the message depending on the percentage? If so you could put the values in a small table and retrieve them from there. If each version of the message can vary depending on other factors, then you may need to take a different approach.

Can you provide more details?

 

[rc-alex]

Sean,

I will have a handful of these fields each with a number of possible values, but I think I'll take your approach. Best to just display each as its own field?

I tried to combine them in a textbox to make a paragraph:

[performed]&" with "&[person]&" on "&[date]&"."

But didn't seem to work.
If I need to, I'll display each as its own field.

This is the other, bigger and more troublesome issue I have.... http://www.access-programmers.co.uk/forums/showthread.php?t=210049

Thanks!

 

[rc-alex]

Well, now I've come across an interesting stumbling block. I wonder how you can put these together into paragraphs? Each output phrase is different in length.

 

[Beetle]

I'm still not quite clear on what you're trying to do. Are there certain parts of each message that will always be the same, and then you are trying to insert other variable strings or field values into each message depending upon certain conditions?

 

[rc-alex]

I'm still not quite clear on what you're trying to do. Are there certain parts of each message that will always be the same, and then you are trying to insert other variable strings or field values into each message depending upon certain conditions?

Yes, exactly.

The text might say, for example, (where red is variable)

Our representative John Smith met with Sally Ford on 5/15/2011 to discuss quality concerns. They determined that quality is sufficient, and will continue to monitor quality.

or

Our representative Jack Boxer met with Leah Thompson on 4/1/2011 to discuss price. They determined that price is high, and will look for alternate suppliers.

I have the red text in tables, and can pull it based on the values. However, because names and other parts of the phrase are different length, I can't make the paragraph very fluent... large gaps in place.

Also - if the plan is to take two actions, or Jack met with 2 people (instead of just Leah) I can't figure out how to add additional values!

Thanks.
Alex

 

[Beetle]

Also - if the plan is to take two actions, or Jack met with 2 people (instead of just Leah) I can't figure out how to add additional values!

Well, presumably you have a child table that stores the details of each meeting (all reps involved, all clients involved, all topics discussed, etc.). You would probaly need to use some VBA code to build a string from the values in this table to store in a variable that could be inserted into your message. Hard to be more specific without knowing more about the structure of your app.

As far as having large gaps in your resulting messages, not sure why that woul be unless you have extra spaces in the string or in the field text. Would need to see exactly how you're builing your string to offer more advice.

 

[spikepl]

To merge data with arbitrary text you can do like this:

Have a memo field for each chunk of text. Inside the memo field, have normal text, and each variable text in square brackets. So, eg:

Our representative [RepName] met with [CustomerName] on [MeetingDate] .....

Then run though the text, find all bracketed text, and for each call a function which dives into a table or whatever, to fish out the appropriate data, and replace the bracket with the text. Continue until no more brackets are found.

 

[rc-alex]

To merge data with arbitrary text you can do like this:

Have a memo field for each chunk of text. Inside the memo field, have normal text, and each variable text in square brackets. So, eg:

Our representative [RepName] met with [CustomerName] on [MeetingDate] .....

Then run though the text, find all bracketed text, and for each call a function which dives into a table or whatever, to fish out the appropriate data, and replace the bracket with the text. Continue until no more brackets are found.

Regarding the last section above - would you do that with Visual Basic somehow? On the report I assume?

Also is there a way to support multiple values? I.e.:

Our representative [RepName]
Our representatives [RepName] and [RepName]
Our representatives [RepName], [RepName], and [RepName]

Thank you!

 

[rc-alex]

Hello,

Can anyone provide a little more guidance on how to combine all of the text?

Also, for the text that may have more than one selection (Our representatives [RepName], [RepName], and [RepName]) should I go with one field that allows multiple values, or a separate table with one RepName per record and several instances of memoID?

Have not figured out how to code/combine these, but I've got the rest of my issues worked out so I'm excited to get this one solved!

Thanks!

 

[spikepl]

#9 That's the fun of programming - you can do whatever you want. I have pointed you towards a possible methodology, and you can build it up to any level you wish. And you cannot have [Rep] and [Rep] together, for how will the code tell the difference between one or the other? You could have a Rep1 and Rep2 in your text.. and all this stuff reqauires quite some coding. Or you could drop the idea entirely and just live with your original solution.

Multivalued fields are a tool of Beelzebub, so stay away from those.

And how to organize your data is difficult to tell for an outsider - depends on what the desired end product is to be exactly.

 

[rc-alex]

So in my table, I would need fields [Rep1], [Rep2], [Rep3] for however many different Rep could be selected.

Then when I code, I guess I should maybe use a calculated field? Not sure what you meant by calling the parameter in a memo field. That's where i'm getting hung up.

Thanks,
Alex

 

[Beetle]

So in my table, I would need fields [Rep1], [Rep2], [Rep3] for however many different Rep could be selected

No! You should definetely not do that.

What you have is a many-to-many relationship here. A given Meeting can involve one or many Reps, one or many Clients, and one or many Topics. So as I stated in a previous post (#7), you need a junction (child) table to manage this. The junction table would define the relationship between those three main entities. Structure might look like;

tblMeetingDetails
*************
MeetingID (Foreign Key to tblMeetings)
RepID (FK to tblReps)
ClientID (Fk to tblClients)
TopicID (FK to tblTopics)

This way, a given meeting can involve as few, or as many, Reps, Clients and Topics as are necessary. If your application is not already structured this way, then this is the fundamental issue you need to resolve first. Otherwise, the design of the db is always going to be making it more difficult for you to get usable data output.

Once the proper structure is in place (if it's not already), then you can programmatcally build a concatenated list of Rep names (or Client names, etc.) for each Meeting based on the data in this junction table.

It's also possible, depending on your specific needs, that you may need more than one child table for each meeting. hard to say for sure without knowing more.