Inverting table data for a report

[Hediru]

I had trouble coming up with a title for this post because my question is complicated. I am a children's pastor at a church, and I am creating a database from scratch to manage my volunteers. Here's what I am trying to do. I have a table that lists my acolytes. I would like to create a table or form that allows me to assign each acolyte to a date and then print out a report that shows who is serving on what date. For example:



Report:


Date - Acolyte Name
January 4, 2015 - Susie Acolyte
January 11, 2015 - Joey Acolyte

Is this possible? I already have a table like what is shown above with yes/no check boxes for all of the possible dates if that's any help. Is there a way to invert the data for the report?

[June7]

You have a yes/no field for each date? This is not a normalized data structure. Normalizing would mean another table with a record for each Acolyte name and date combination and a form/subform arrangement for data entry.

Generating that output with current structure could be done with a UNION query. There is no wizard or builder for UNION query, must type into SQL View of query designer. There is a limit of 50 SELECT lines in UNION.

[Hediru]

I had had the yes/no field for each date so that I could create a form that allowed me to access each acolyte and check the boxes for the dates that they are serving without having to sit there and think "ok, what are the dates of the Sundays in January?" if you have a better idea of how I can do this with a more normalized data structure, I am all ears!

I'm also not familiar with UNION queries. Where can I find a tutorial? I'm self taught on the program and have been able to do a lot with it, just can't figure out how to assign dates to the students and then create the report that I want. Thanks for your help!

[June7]

A good start for learning SQL http://www.w3schools.com/SQl/sql_union.asp

The examples show UNION of two tables, but the same technique can be used for fields from same table. The first SELECT line sets the field names, so can use an alias to reassign a field name.

SELECT AcolyteID, [First Name], [Last Name], #1/4/2015# AS DateWork FROM Acolytes WHERE [January 4, 2015]=True
UNI ON SELECT AcolyteID, [First Name], [Last Name], #1/11/2015# AS DateWork FROM Acolytes WHERE [January 11, 2015]=True
... ;

Does this give you some idea of why non-normalized structure is fraught with problems? Better design would be the related tables I described with a combobox listing the Sunday dates to choose from. The combobox RowSource can come from a table or can be generated by VBA code.


Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[Hediru]

Ironically, I had done the combo box row source originally. Leave it to me to make things more complicated. Ok. So once I convert this back to the combo box, I can use the UNION table to make a query to base my report on? And will it parse out the separate dates? (ex: Susie Acolyte serves on Jan 4 and Mar 8. Would the resulting report list her both on Jan 4 and Mar 8?) Thanks for helping me with what may seem like dumb questions.

[June7]

How does combobox help if you still have the same table structure?

Yes, UNION query will show both records.

[Hediru]

Ok. I think I get it! Thanks for your prompt help! I will mark this thread as resolved. I'll post a new thread if I have additional questions.