Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Oct 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run action query from table or recordset (2003 SP2)

    This should be so simple I hesitate to ask, but no one at work has been able to help:
    I have an action query that I want to run for each value in another table.
    The gory details: My append query runs from a parameter requiring the user to input a project number. The append query populates a table with specific
    information. It works great. The problem is I now need to update several hundred projects. I think there must be a simple way to pull the first record from
    a table of projects, run the action query, step to the next record, etc.

    Our Access guru suggested this site, and I hunted through it, but didn't find anything. I tried DLookup, but it apparently works OK for select queries but not the action query. I'm hoping you quys will be able to help me with this, and if it's a little elementary, please forgive me.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run action query from table or recordset (2003 SP2)

    Welcome to Woody's Lounge!

    Perhaps this can be done with a single append query, based on the original table and the projects table, linked on the appropriate field.
    If not, we'd need to have more information about the tables and the query.

  3. #3
    New Lounger
    Join Date
    Oct 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run action query from table or recordset (2003

    Thank you, Hans!

    I have difficulty trying to explain without pictures, but here goes: I use Access and ODBC to manage a database I access through the internet. I did not create and I can't modify this database - it is provided from on high. It tracks projects and has multiple tables for funding, status, milestones, managers, etc. As a concession to us users, there is a table called Uniques that allows the user to define their own field so they can track data they are specifically interested in.

    I have 15 user defined fields that I put into the Uniques table for each project. Unfortunately, some projects may already have two or three of them already put in by someone else. As a workaround, I have a query that inputs a project number, checks the Uniques table for that project, and adds any of the 15 user-defined fields that are not pre-existing. The query takes anywhere from 15 to 30 seconds to query the database and update the Uniques table. Note this query enters the field into the Uniques table, not a value for the field, which is entered later. I've been doing this process for several years, project by project, until now.

    Now I need to add a 16th or 17th user defined field to the Uniques table. This means that I have several hundred projects to update. I am hoping I won't have to input each project individually and wait for it to prompt me so I can put in the next one. This would take weeks of "spare" time.

    I tried linking a the project number table to my update query, but it just doesn't seem to work. My next thought was that perhaps I could do something in VB or a macro to feed the update query a project number, let it run, and then cycle to the next record. I'm pretty good with the queries, but it has been so long since I've had any need for VB I am almost having to learn it over from scratch.

    Any ideas you have will be greatly appreciated. I've outrun my support at home, and don't even know if I'm approaching this right.

    Cheers,
    -- Mike

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run action query from table or recordset (2003

    Can you tell us more about the structure of the tables involved? I don't understand how you can use an append query to add fields to a table - an append query is used to add records, not fields.

  5. #5
    New Lounger
    Join Date
    Oct 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run action query from table or recordset (2003

    Unique table structure is:

    sequence number 1 ---- unique name 1 (user defined) ---- unique value
    sequence number 1 ---- unique name 2 (user defined) ---- unique value
    sequence number 1 ---- unique name n (user defined) ---- unique value

    sequence number 2 ---- unique name 1 (user defined) ---- unique value
    sequence number 2 ---- unique name 2 (user defined) ---- unique value
    sequence number 2 ---- unique name n (user defined) ---- unique value

    sequence number n ---- (etc)

    The sequence number relates to a sequence number in the master projects table. The Uniques table names and values are text fields. This allows me to link a name (such as "category") and a value to that name (such as "concrete") to every project in the master projects table. My append query appends my 15 user defined names to the Unique table for a specific project. Does this help?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run action query from table or recordset (2003

    I think you can create a query based on two tables:
    - The table listing the projects (sequence numbers), and
    - The table listing the new user-defined names.
    Don't join the tables.
    Add the sequence number field from the first table and the name field from the second table to the query grid.
    Select Query | Append Query, and specify the Uniques table as target.
    Hopefully, Access fills in the correct target field names. If not, enter or select them manually.
    When you run the query (test it on a copy of the database first), a new record should be created for each project and each new name.

    See the attached sample database (Access 2000 format, zipped)
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Oct 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run action query from table or recordset (2003

    Thank you again, Hans! We are closing in on it. In order to update the Uniques table, my query is very similar to what you show; so far we are thinking alike. The difference between what you sent & what I have is that my append query checks two other queries, and not two tables.
    -- One of the queries is the checks for the Uniques table for existing fields and returns only the required fields that are not already entered
    -- The other query takes the project number and links it to the Uniques table that I want to update.
    The append query itself is set up like what you sent me, with no links between them. At the moment, the append query inputs the project as a [parameter] and then runs. Where I am stuck is how to remove the [input project] parameter and instead have it run from a list. I tried adding a projects table to the query, giving me three unlinked items in the query, but it won't run, even when I put only a single record in the projects table. Here's some screen shots, I hope they are readable.
    Attached Images Attached Images

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run action query from table or recordset (2003

    Does the method used in the query in the attached version help?
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Oct 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run action query from table or recordset (2003

    I'm still working on this - I think it'll work, though. Thanks.

  10. #10
    New Lounger
    Join Date
    Oct 2007
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run action query from table or recordset (2003

    Hans - it took me a while, but I figured out what is driving me nuts. The database I am pinging thru ODBC requires more than one field entered to create a new unique item. When I run the query as you suggested it works to the point the database returns error messages. So far I am again only able to update a single record at a time. Thanks for all your time, though. I learned much through this exercise.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run action query from table or recordset (2003

    If you tell us more about the table structure and the required fields, someone may be able to come up with a suggestion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •