Hi, new to the forum but not really new to Access. I can stumble my way through VBA, but I mostly use the built-ins in Access.
First, I need to clarify that the database structure I am working with is very limiting. My organization contracts with a third party to collect data. That data is delivered via an access database. There are over a hundred tables with thousands of fields delivered with each update, and the relationships will make your head spin. I have created a database to import the tables and then manipulate them to produce the results I need. Unfortunately, most of my use of the data is dependent upon data, fields names and terms used by the third party company.
I have a series of queries that combine data from many different tables. One such query shows a record of events that a person has participated in, and then assigns a certain point value to each. For example, the query contains fields, among others, called Name, Date and EventType. These fields are all populated from various tables. There are also a number of fields that assign a point value to each event via an expression. For example, one field may be called 5 Points. The expression to assign the value to this field is as follows:
5 Points: IIf([Event] Like "Driver*",5,0)"
This expression will either assign the numbers 0 or 5 to that field. This is a simple example, as it only has one criteria. Unfortunately, other fields have many more possible criteria.
4 Points: IIf([Event] Like "Building*" Or [Event] Like "*Auditing*" Or [Event] Like "*Visiting*" Or [Event] Like "*Publicity*" Or [Event] Like "*Nominating*" Or [Event] Like "*Good*" Or [Event] Like "*Fund*" Or [Event] Like "*Grievance*" Or [Event] Like "*Bylaws*" Or [Event] Like "*Sergeant*" Or [Event] Like "*Tri County*" Or [Event] Like "*Communications*" Or [Event] Like "*Equipment*" Or [Event] Like "*Recognition*" Or [Event] Like "*New Engine*",4,0)
Another expression field then calculates the total points for each name. This calculated number is then used later in other queries. The criteria are text strings because that is how the data is stored in the tables as they come down from the third party.
What I would like to do is be able to update the expression to edit the criteria. For instance, suppose the organization decides that somebody who participates in an event called "Birthday" be awarded 4 points. Right now I, as the database administrator, have to open the expression in design view and make the change to the expression. The point values will never change, only the possibility of adding or removing events.
I would like to be able to have the users update a table, which is then used to populate the expression with the various criteria.
I hope this makes sense! Any help would be appreciated.
Thanks.