Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update query help (2003)

    Hi All

    I wonder if one of you clever people out there could help me with a query problem please. I have attached a basic database which illustrates what I am trying to do.

    The table tblSalesData contains a list of invoice numbers and their relevant customer numbers, but in the actual table I am using I sometimes don't have the customer number. These numbers I can find in a different table, in this case tblSalesOrders.

    The query I have done will link the two tables and show me the customer numbers that were missing before.

    What I want to do is to have this query result updated back into the tblSalesData again. So that my sales data will show all the invoice numbers and their respective customer numbers

    How can I do this?

    Thanks in advance
    Hayden
    Attached Files Attached Files

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

    Re: Update query help (2003)

    The following query will do what you want:

    UPDATE tblSalesData INNER JOIN tblSalesOrders ON tblSalesData.InvoiceNumber = tblSalesOrders.SalesOrderNumber SET tblSalesData.Customer = tblSalesOrders.Customer
    WHERE tblSalesData.Customer Is Null

    You can create a blank new query, switch to SQL view and paste the above SQL into the window, then switch back to design view.
    Attached Images Attached Images
    • File Type: png x.PNG (9.2 KB, 0 views)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query help (2003)

    Great, thanks for the help Hans.

Posting Permissions

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