Results 1 to 5 of 5
  1. #1
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102

    Update query to update one table using criteria from another

    Hi all, I have a dilemma, my database contains vehicles and servicing etc, one area is movement of those vehicles being placed on different sites etc. At the moment the status of each vehicle is done manually, but I would like it done automatically.
    For example when a vehicle is on a job a form is used to transfer it to that job with a onhiredate and when it returns a offhiredate, the status is active/on hire when it's on a job and idle/available when it returns, I would like the status of that vehicle to change on closing the transfer form, to change depending on the offhiredate but only effect that vehicle.
    So if offhiredate is empty the vehicle is active/onhire and so on, but only effects the current vehicle, the vehicle ID is present in the transfer form. I have been trying with IIf statements but fail to know how to affect the update of status using the vehicle id as a reference.

    Thanks in advance for your help.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    no you don't use iif statements, you use queries.
    join the tables on the common fields and set your criteria in an update query.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    from how you describe your tables, you don't need a status field, it can easily be calculated in a query based on the on/off hire dates

  4. #4
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Thanks Ajax but I'm not 100% sure what you mean, status is like a look up table that is related to the main table, the backend is on an sql server. I can reset the status with an update query if the offhiredate field is null but I can't seem to set the status field if the offhiredate is not null. I s this best done through vba rather than an update query?

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it was just an observation based on your comment 'when a vehicle is on a job a form is used to transfer it to that job with a onhiredate and when it returns a offhiredate, the status is active/on hire when it's on a job and idle/available when it returns'

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

Similar Threads

  1. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  2. Replies: 1
    Last Post: 05-17-2013, 12:53 PM
  3. Replies: 1
    Last Post: 10-29-2012, 08:15 AM
  4. Replies: 1
    Last Post: 02-08-2012, 04:50 PM
  5. Query Criteria Lost After Table Update
    By matt4003 in forum Queries
    Replies: 10
    Last Post: 12-29-2009, 10:50 PM

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