Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pull value fron another table (Access 2000)

    I have an update query and I want to put in the WHERE clause also the number from the field called afid in the table customers1. I do not know how to do it, and is it possible at all ?
    The table Customer1 is sent by different users and each user has a separate afid number.In my example the table Customers1 is obtained by user Nr 2. Therefore I want my update query to be able to reccognie the afid of the customer and to update accordingly.

    WHERE (((orders.orderid)<DMax([orderid],[orders],[Audit]=True)) AND ((orders.audit)=True) AND ((orders.audit)=True));
    Actually i need table customers1 only to pull the value of the afid form this table and to build the where clause in my update query. Maybe wit h DLookup ?
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Pull value fron another table (Access 2000)

    The only query in the db you posted is a delete query, yet you say you want to run an update query?
    What field do you want to update? What do you want to update it to?
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pull value fron another table (Access 2000)

    I am so sorry, it is a dele]te query that i want to amend.My presend and running query is :

    '//Delete all previous Audits in the main table orders

    StrSQL = "DELETE orderid, England " & _
    "FROM orders " & _
    "WHERE orderid<DMax(""orderid"",""orders"",""Audit =True"") AND Audit =True"
    CurrentDb.Execute StrSQL

    i want a delete query a that deletes all previous Audits for the afid in question.i mean that is should not delete the other audits, belonging to different affids.To this end i intend to use the afid in the table customers1 which is one and the same for the whole table Customers1. The table orders contains all the records for all the customers with different afids.

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

    Re: Pull value fron another table (Access 2000)

    Your Orders table contains only records with CustomerID=0, which doesn't correspond to an existing customer.
    Please spend a little bit of time to clean up your database, and attach one that we can actually use.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pull value fron another table (Access 2000)

    I realy want to apologize. the reason for my unclear attachment was that i wanted to cut it down as much as possible,but obviously i bungled it. I am working on it and if i come acrros problems i will write to you then.
    Thank you for your attention

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pull value fron another table (Access 2000)

    I am not sure shall i send in a new thread or write it here.i presume the problem is now different and it is better to send another thread but please corrrect me if i am not right

Posting Permissions

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