Option info to report 2

[mpmoore]

I am baffled. I have a form with several option groups such as:
Option 1
1 Correct
2 Signature missing
3 Document missing

I would like to print a report that displays a sentence which explains the selection from the option group. I have tried everything I know (granted, that's limited) to do this with no avail. Hopefully, this question is clear enough to understand. If not, please ask for more information. Thanks for any help possible.

 

[JoeMiller]

Is this option group data only on the report or is it stored in a field within the database and called up on the form?

Joe Miller
joe.miller@flotech.net

 

[mpmoore]

The number is stored in a table. I guess I am trying to get the report to print a caption based on the number stored in the report. Thanks for helping me out.
Mike

 

[JoeMiller]

Ok, now the million dollar question, since it is a stored field you can get the value from the query (this is a good thing!). Are the options in the option group a list that you typed in via the wizard or are they stored in a table with their respective descriptions? Sorry for the questions, but the answers to these questions change the answer! =)
Joe Miller
joe.miller@flotech.net

 

[mpmoore]

All the option groups were created with the assistance of the wizard. I have several option buttons that this will apply to as well. They, of course, will be yes/no,true/false answers. I have created a query and retrieved the numerical values that far. It was at that point that I got lost.

Thanks for the help,
Michael

 

[JoeMiller]

What you have to do is in your query make an expression that evaluates your OptionField. In the query, you can copy/paste the line below and it will make a new column in your query with the fieldname "OptionDesc," which you can then include on your reports. Make sure that you change OptionField to the actual name of your field that is storing the Option 1 value.

OptionDesc: IIf([OptionField]=1,"Correct",IIf([OptionField]=2,"Signature Missing","Document Missing&quot)

What this function does is look at OptionField and see if it's equal to 1, if it is then it puts "Correct." If it is not one, then it could be 2 or 3 so it it need to test between 2 and 3. If it's not 1, but it is 2, it puts "Signature Missing" otherwise it's not 1 and it's not 2 so it must be 3, "Document Missing."

To make this easier in the future, you can use a table rather than type a list in the wizard. Then rather than this testing for value with the iif statement you can link your table to the table holding the values and then drag and drop the description. That's part 2 of your lesson. ;-)

Joe Miller
joe.miller@flotech.net

 

[mpmoore]

Thank you very much for the assistance. Your patience and persistence is greatly appreciated.
Michael

 

[mpmoore]

Wanted to check back on this. Would it be possible to create a table that included all of the option groups and buttons and give them all unique numbers to return when selected into one table? Say a table called SourceCodes. Then I could base a query off that table and my LeaseTable (where the numbers are stored) and build my reports from that query. I am pretty sure it would be fine for the option groups, but what about the buttons, aren't they either -1 or 0?

On the right track?

 

[JoeMiller]

It's fine for Option Groups yes, and would work the way you describe. As for the -1 and 0, those would not be as good a candidate for this table. The -1 and 0 are used for True/False, Yes/No, On/Off normally. So I wouldn't recommending using a checkbox and changing it's definition to something other than the T/F, Y/N etc...

Joe

PS - Sorry took so long to respond, been out of town. Joe Miller
joe.miller@flotech.net

 

[mpmoore]

Okay, next problem, on the query. How do I bound the two tables? How do I set up the QBE? This is the first attempt at this type of query. Have done others whose relationships were easier to identify.

Mike

 

[JoeMiller]

The table that stores the description should have at least two fields, one for the OptionValueID and another for the OptionValueDesc (description). The table that stores your document data has a field now that holds the OptionField, the one discussed in examples above. That field should have values that match your OptionValueID in your descriptions table. The data in your descriptions table should be like so:

[tt]
OptionValueID OptionValueDesc
1 Correct
2 Signature Missing
3 Document Missing
[/tt]

Now you should link the field containing OptionValueID in your descriptions table to the OptionField in your documents table. Do this in the relationship window and make the relationship 1-to-Many. I would also edit the relationship and set "Enforce Referential Integrity." If Access won't let you set referential integrity it's most likely because one or more of your documents doesn't have a value for the OptionField. Add a value for the missing ones and try again. Once the tables are linked you can add the descriptions table to your query and drag the OptionValueDesc down to the QBE and it will show you the appropriate description as long as OptionValueID = OptionField.
Joe Miller
joe.miller@flotech.net

 

[mpmoore]

Do I have to do a separate table for each option group? I have six option groups on the form and have set up a common table with the descriptions for all. Do I just join each field to the common table? I am really close and you have been most helpful.

Mike

 

[JoeMiller]

You have two options here. You can create six different tables, that is one way to do it but a little cumbersome. Another way to do it involves modifying some more stuff but it's much slicker and keeps all your stuff in one table.

Modify the OptionGroups on your form so that each Option within all the OptionGroups stores a different OptionValue. There should NOT be a single Option that stores a value the same as another. Then you can place all the different Option Values in your descriptions table and link that table to your documents table multiple times. One time for each field that is associated with an option group.

To make things a little easier for you when you are managing the database, add a new text field named OptionGroup and within that field put a description to help you remember which OptionValues go with which OptionGroup. Here's an example:

[tt]
OptionValueID OptionValueDesc OptionGroup
1 Correct DocStats
2 Signature Missing DocStats
3 Document Missing DocStats
4 Option #4 SampleGroup2
5 Option #5 SampleGroup2
6 Option #6 SampleGroup2
[/tt]

Now you have a cue for you (the developer!) to see what Option goes with what option group on your forms.

WARNING!!! If you implement this you will have to change all your old data in the other Option Group fields of your documents table to reflect the new values you place on the form. Some update queries targeted with criteria on the Option Group fields will do this for you. Joe Miller
joe.miller@flotech.net

 

[JoeMiller]

My table didn't come out correctly because of the word wrap, copy/paste it into notepad to see how the data format goes.
Sorry!! Joe Miller
joe.miller@flotech.net

 

[mpmoore]

Joe, you have been so helpful. I hope to be able to return the favor to another user one day (after gaining much more knowledge) There are so many details to Access that books just can't cover. The willingness of professionals such as yourself is greatly appreciated.

Thanks again,
Michael Moore