Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating A Table (Access 2003)

    I am building a complaint tracking database. I've set up the initial tables with lots of drop down lists etc and I have a qry to select complaints by date range and district range. The initial info is input using a form. I want to be able to add information to this table to capture the response to the complaint (what was done, when, who did it etc.). I was hoping to do this through an update qry, but it looks like I need to set up separate tables for the response data, connect to these tables (we will have 12 tables daily) to the input table to create a new table.
    Is there a simpler way to set this up? Can I use an update qry to fill empty fields in an existing table?
    The idea here is to avoid hard-copy steps in the process and keep everything electronically for obvious reasons.

    Thanks in advance for any advice offered.

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

    Re: Updating A Table (Access 2003)

    Welcome to Woody's Lounge!

    You can use an update query to fill empty fields in an existing table, but where do the response data come from? Are they available in table form, or ...?

    Also, if there can be multiple responses, it would be better to use a separate response table.

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating A Table (Access 2003)

    Wow.
    Thanks for your quick response!
    The idea is to have the DM's (complaint responders) fill in the blanks manually as they deal with the issue. These are date fields, a memo field for the situation etc.
    This will allow me to mine the database for repeat complaints, unresolved complaints etc.
    I created a qry that gave me all the info I needed and thought I was home free, but it was a select qry and the blank fields could not be modified.
    If I use separate tables for each DM, I have to connect 12 tables to the original input table and create a new table daily. The records are autonumbered and I could easily join the tables using the autonumber, but this seems very complicated. If the DM's could manually update the table things would be much simpler.

    Thanks

    Thanks again Hans

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

    Re: Updating A Table (Access 2003)

    I'm afraid the situation is not clear to me yet. Where do the DMs fill in the blanks? On a paper form, or in the database?

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating A Table (Access 2003)

    Hans
    I have a second input form that is built using the qry that produces the complaints by date range and DM. The first form only includes the fields necessary to capture the complaint information. The second form picks up the details of the complaint and includes the blank fields to be completed by the DM. Ideally the DM's would use this second input form to update the table. The qry produces the selected records in the form format.

    I hope this makes some sense.

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

    Re: Updating A Table (Access 2003)

    Why don't you base the second form on the same table as the first one? Then the data would end up in the correct place immediately.

  7. #7
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating A Table (Access 2003)

    Hans
    Thats what I originally did using a Select qry. It gave me the data I requested, but the blank fields will not accept data. I've tried using an update qry with the same result. I can't enter data into the blank fields.
    I've searched help and Access for Dummies and I can't figure out what I'm doing wrong.

    thanks again.

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

    Re: Updating A Table (Access 2003)

    > but the blank fields will not accept data

    That probably means that the query is not updatable. It should be possible to create a query that will let the user fill in the blank fields. Perhaps you could provide some information about the query.

  9. #9
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating A Table (Access 2003)

    The first qry I tried was a select qry using all the fields in the table, including the input fields and the blank fields. Criteria for the date field was BETWEEN [Enter First Date] and [Enter Last Date]. Criteria for the District field was BETWEEN [Enter First Set of DM Initials] AND [Enter Last Set of DM Initials] to produce a set of records by date range and DM. The first and last DM initials allows us to pull records from multiple districts.

    This qry listed the appropriate records and I created a form that included all the fields in the qry. The blank fields could not accept data.

    I then used an update qry with the same date range and district range parameters using the table name as the destination for all the fields. Again I got all the records I would expect to get but the blank fields will not accept data.

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

    Re: Updating A Table (Access 2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  11. #11
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating A Table (Access 2003)

    Here's the pared down version of the database. I've included all the tables that support the input form and my initial qry. All data is non-real.

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

    Re: Updating A Table (Access 2003)

    When I open the query (tblComplaintInput Query) and enter appropriate values for the parameters, I can enter data in the records. The same holds if I create a form based on the query.

  13. #13
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating A Table (Access 2003)

    Hans
    Thanks for your help.
    I still don't know why the table would not update. I closed the database last night.
    Openned it today and now it updates.

    Go figure.

Posting Permissions

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