0

I have an old database that is used to keep track of employee attendance at training events. Each record is an employee and following the basic first name/last name phone number ect... there are about 150 fields representing past events, each with a yes/no checkbox indicating whether that employee attended that event.

I have a form that allows employee records to be easily viewed and edited. I currently have a massive list of the events with checkboxes that are tied to the corresponding fields in the master table.

I need to create a list of events that the employee currently being viewed has attended, so a display of all the field names that are checked for the specified employee.

I'm aware that the design of the database I'm working with is obviously imperfect, but is there any way that I can do this?

Thanks in advance!

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
User999
  • 1
  • 1
  • Is there any consistency on how these checkbox fields are named? There are ways to iterate through he fields of a table, so if getting a list of the field names of the checkboxes that are checked for each employee does what you need, then there is a way. – SunKnight0 Jun 10 '16 at 13:50
  • This question looks almost the same as [Is there a way to conditionally list field names on an access form?](http://stackoverflow.com/q/37737283/77335). Does the answer to that question give you what you need? If not, how is this question different? – HansUp Jun 10 '16 at 13:52
  • Yes the names are fine, getting a raw list of field names would be acceptable if that's what you mean. – User999 Jun 10 '16 at 13:53
  • @HansUp yes I saw that and I tried that solution but it didn't work for me. I'm no expert on VBA so it's possible that I put the code in the wrong place. I also need to print this out on a form, so I don't know how I would get the result of that code onto a form – User999 Jun 10 '16 at 13:55
  • 1
    If you can get that function working, you could populate a scratch table with with the employee and the workshops that employee attended. Then base your form on the scratch table. – HansUp Jun 10 '16 at 14:13
  • The Problem is I need the form to be dynamic, so that changes to the master DB would be reflected in the list. – User999 Jun 10 '16 at 14:25

1 Answers1

2

You will essentially need to "unpivot" the ~150 columns for each event into a temporary table with 

EmployeeID  EventID
----------  -------
         1  event1
         1  event3
         1  event7

The most common approach to "unpivot" in Access is to UNION ALL a series of individual SELECTs. However, that is only good for about a dozen UNIONs (after which Access throws a "query is too complex" error), so you'll have to break it up into smaller groups of "event" fields and store the results in a temporary table.

To avoid having to do that messy "unpivoting" over and over again, you should just fix your database structure to make that "unpivoted" table permanent and get rid of the ~150 fields in the other table. It's a classic case of "short-term pain, long-term gain".

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418