Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update question (Access 2000)

    I have to update 10 queries differing only in one figure from the where Clause.
    This is the orders.customerid which is 119,0r 120,or 401 or 960 etc
    I have separated my sql into two parts, bas and StrCustomer.
    It is working but i want to find out how can i make the query update all the 10 customers with the help perhaps of a variable withing the Where clause.
    Any suggestions ?

    Below is my code, which is working, but not flexible to involve all the possible order.customerid situations

    strBas = " UPDATE ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid SET [order details].cartons = [quantity]/[pack]" & _
    " WHERE (((orders.orderid)>=(SELECT Max(orders.orderid) FROM orders WHERE orders.receipt=True And orders.customerid=119))" & _
    "AND ((customers.afid)=1)"

    Dim StrBl As String
    StrCustomer = " AND ((orders.customerid)=119))"
    sql = strBas & StrCustomer
    CurrentDb.Execute sql

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Update question (Access 2000)

    Introduce a long variable (called lngCustomerID then change:

    strBas = " UPDATE ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid SET [order details].cartons = [quantity]/[pack]" & _
    " WHERE (((orders.orderid)>=(SELECT Max(orders.orderid) FROM orders WHERE orders.receipt=True And orders.customerid=119))" & _
    "AND ((customers.afid)=1)"

    Dim StrBl As String
    StrCustomer = " AND ((orders.customerid)=119))"
    sql = strBas & StrCustomer
    CurrentDb.Execute sql

    to:

    strBas = " UPDATE ((Customers INNER JOIN orders ON Customers.Customerid = orders.customerid) INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid SET [order details].cartons = [quantity]/[pack]" & _
    " WHERE (((orders.orderid)>=(SELECT Max(orders.orderid) FROM orders WHERE orders.receipt=True And orders.customerid=" & lngCustomerID & "))" & _
    "AND ((customers.afid)=1)"

    Dim StrBl As String
    StrCustomer = " AND ((orders.customerid)=" & lngCustomerID & "))"
    sql = strBas & StrCustomer
    CurrentDb.Execute sql

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

    Re: Update question (Access 2000)

    Try this:
    <code>
    Sub UpdateOneCustomer(lngID As Long)
    Dim strSQL As String
    strSQL = "UPDATE Products INNER JOIN (Customers INNER JOIN " & _
    "(Orders INNER JOIN [Order Details] ON Orders.OrderID = " & _
    "[Order Details].OrderID) ON Customers.CustomerID = " & _
    "Orders.CustomerID) ON Products.ProductID = " & _
    "[Order Details].ProductID SET [Order Details].Cartons = " & _
    "[Quantity]/[Pack] WHERE Orders.OrderID >= " & _
    "(SELECT Max(Orders.OrderID) FROM Orders WHERE " & _
    "Orders.Receipt = True And Orders.CustomerID = " & lngID & _
    ") AND [Customers].[AfID] = 1 AND Orders.CustomerID = " & lngID
    CurrentDb.Execute strSQL
    End Sub

    Sub UpdateCustomers
    UpdateOneCustomer 119
    UpdateOneCustomer 120
    UpdateOneCustomer 401
    ...
    End Sub
    </code>
    Replace ... with UpdateOneCustomer lines as needed.

  4. #4
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update question (Access 2000)

    Thank you Hans.I tested it sucessfully and it saves a lot of spadework.

Posting Permissions

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