Results 1 to 5 of 5
  1. #1
    nailm1405 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    3

    Update Query Expression through table?

    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.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Build a lookup table, that have the events and their point value.
    join this to your data table,and bring the point values into the query.

  3. #3
    nailm1405 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    3
    I will try this. Thanks.

  4. #4
    nailm1405 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    3
    Ranman256, I gave this a try and discovered that the data in question is saved in the table as a memo. I am getting an error trying to join the tables in the query, and therefore can't link the lookup table. Any other thoughts?

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Your lookup table will have the following fields (go into design mode for the table and make any changes required):

    Event - this will be the exact same name, data type and length as it is on the source table - this will be your primary key
    Number of points - this will be a Number data type with an Integer Field Size

    In your query you will then join the two event fields - the source table to your lookup table. As they are the same size and shape the join will work fine. Use the points value from the lookup table.

    One question - what happens if there is an event on your source table which does not exist on your lookup table - what do you want to happen? With a direct link between the two tables the data will not show up. Make sure this is what you want to happen.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-10-2016, 11:26 AM
  2. Replies: 9
    Last Post: 01-14-2015, 05:10 PM
  3. expression builder: update query
    By JMac in forum Access
    Replies: 3
    Last Post: 05-04-2012, 03:19 AM
  4. Replies: 1
    Last Post: 02-08-2012, 04:50 PM
  5. Replies: 1
    Last Post: 03-23-2010, 04:01 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums