Muliple Fields/Single Textbox

[cabcree]

Hello! I am using a database currently where I enter the information of a person and then select what type of letter they will be receiving. This all is working well. What I want to do now is be able to create a report for each of our different centers that shows the person(s) who received a letter for that center and what letter they received. Just a quick, one page report.

For some reason I cannot figure out how to make this one page report with the multiple fields. Hopefully you can make sense of this next part.

In order to print different letters I created different fields that were a yes/no type. A couple examples:

GradCompletionStandardsWarning
GradCompletionStandardsContConcern
GradCompletionStandardsContWarning
(I have 33 of these total)

Now I want to create something like this in a report

Center
Name, ID, Grad Completion Standards Warning Letter
Name, ID, Grad Completion Standards Cont Concern Letter
Name, ID, Grad Completion Standards Cont Warning Letter

Does that make sense? Can you help me? Thanks

 

[HiTechCoach]

You might not like the answer. The problem is that you are violating the rules of data normalization. When you said "(I have 33 of these total)". That was a huge read flag that your your table has a serious design issue.

Another way to look at it is that you have design a spreadsheet in Access, not a relational database. IMHO, since your data is not properly normalized, you can not do what should be simple.

Your field names should not be data (ex. GradCompletionStandardsWarning). What is you need to add or remove letters, which should be data in a table, not fields.. That would require a table design change and update every query/form/report. If properly design, you can add a letter to the list (data) without any design changes required to the database.

You probably should have a child table where each letter is a record and a lookup table for the list of letters.

A record would look something like:

GradID, Link to parent table
CenterID
LetterID
DatePrinted

Or something like that. This way you only create a record when a letter is actually needed, You could even track resending letters this way!

... or ...

If you want to use your current design, it will probably take at least 34 additional queries to normalized the data correctly to get what you want. 33 queries, one for each check box filtered to with data,. The 34th query would be a "UNION ALL" query to combine the 33 queries into a single recordset.

Every time you add or remove a letter, you would have to update the queries, not just add data like I am suggesting above.

Hope this helps ...

 

[cabcree]

Thank you! Unfortunately there are 33 different types of letters so I'm not sure I can do the first option. I will certainly look at the second option!!

 

[cubquodo]

This way you only create a record when a letter is actually needed, You could even track resending letters this way!

 

[cabcree]

hmmm...let me think more about the first option...I might be back to ask more questions. Thanks again!

 

[HiTechCoach]

Thank you! Unfortunately there are 33 different types of letters so I'm not sure I can do the first option. I will certainly look at the second option!!

Because there are 33 different types of letters is exactly why do NOT do it the way you did.

 

[cabcree]

After the caffeine started kicking in I started understanding option 1. I'm working on it!! It will be easier/better! Thanks again!

 

[cabcree]

Can't express how grateful I am for your help on this! Option 1 is working beautifully!

 

[HiTechCoach]

You're welcome!

Great to hear to have it working! Great Job!