Results 1 to 1 of 1
  1. #1
    fspswen is offline Novice
    Windows Vista Access 2007
    Join Date
    Nov 2009

    Update query for ID #s to link records

    I have been struggling with this query forever and would appreciate any insight.

    I am building a database for datasheets that have header info that applies to every record on the datasheet, then a series of records (sometimes in more than one category). For simplicity in entering data and to avoid redundancy in the database, I have separate tables for the header info and the records themselves, with a "datasheet ID" field in every record that links it to the header information (date, who collected the data, etc.).

    I've set up a series of forms for entering the data, with a command button that users click when finished with a datasheet. The idea is that this button runs an update query - adding the "ID" value for the datasheet they entered into the "datasheet ID" field for all records entered since the last time the button was clicked. Simple, right?

    I set the default value for "datasheet ID" to 0 then use that as the criteria for the update query. If i simply use [tablename]![ID] in the update field it works sometimes and not others - it will usually work for a while then start "hanging up" on a particular ID such as 40 or 80 rather than moving on to the next value. In other words, the records from datasheets 41 and up would all be linked to datasheet 40's info. Sometimes this query doesn't work at all, and the datasheet ID value remains zero.

    I've tried using max or last functions to isolate the correct ID value, but if I use that in the "update to" query field I get an aggregate function error, and if use a separate query to obtain perform the function then I get "you must use an updateable query" when i run the update query.

    Sorry this is so long - any ideas?

    PS - If I delete the joins between all of these fields the queries work fine, but then the "datasheet ID' field doesn't actually link the record to the datasheet info.
    Last edited by fspswen; 11-20-2009 at 02:09 PM. Reason: forgot something

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

Similar Threads

  1. Update Query
    By Najeeb in forum Queries
    Replies: 1
    Last Post: 11-19-2015, 04:02 AM
  2. Update Query- selective update?
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-29-2009, 11:15 AM
  3. Help with update query
    By weirdg in forum Queries
    Replies: 0
    Last Post: 09-03-2009, 04:21 AM
  4. How to Update 70,000++ Records
    By UCBFireCenter in forum Queries
    Replies: 54
    Last Post: 06-19-2009, 12:43 PM
  5. Return records not updated by update query
    By ars80 in forum Queries
    Replies: 2
    Last Post: 05-01-2006, 09:23 AM

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