Re: Update problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Viescas (JohnV_at_nomail.please)
Date: 02/10/04 

Date: Mon, 9 Feb 2004 18:29:52 -0600

If MaxInvoiceDog table already contains the data from the highest invoice
number, why are you using a WHERE clause at all?

UPDATE Sales INNER JOIN MaxInvoiceDog ON Sales.[Invoice Number] =
MaxInvoiceDog.[Invoice Number] SET Sales.InvSalePrice =
MaxInvoiceDog.SalesPrice

If you want to update *all* sales records with the price from the max
invoice for all dogs, then you need to join on Dog Number. 

-- 
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
"Joan" <joanlueger@galaxycable.net> wrote in message
news:ukYPJe27DHA.1040@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> I am having problems setting my criteria in an update query.  My update
> query uses MaxInvoiceDog table which is made from a Make-Table query. This
> table contains dog sale information from the last time the dog was sold.
One
> dog can be sold more than one time if it is returned.  I am attempting to
> update a field called InvSalePrice in the Sales table with the value of
> SalesPrice in the Dogs table.  This is a one time update in order to get
the
> correct values into InvSalePrice which is a new field in the table.
>
> Below is the SQL for my Update query.  When I run it the same value
appears
> in the InvSalePrice for both instances of a dog's sales instead of just
the
> last sale instance(max of Invoice Number).
>
>
> UPDATE Sales INNER JOIN MaxInvoiceDog ON Sales.[Invoice Number] =
> MaxInvoiceDog.[Invoice Number] SET Sales.InvSalePrice =
> MaxInvoiceDog.SalesPrice
> WHERE (((MaxInvoiceDog.Returned) Is Not Null) AND ((Sales.[Invoice
Number])
> In (SELECT [Invoice Number] FROM MaxInvoiceDog )));
>
>
> Here is my Make-Table query :
>
> SELECT Sales.[Dog Number], Invoices.Store, Invoices.[Invoice Number],
> Invoices.DateSold, Invoices.Type, Sales.InvSalePrice, Dogs.SalesPrice,
> Dogs.Returned INTO MaxInvoiceDog
> FROM Invoices INNER JOIN (Dogs INNER JOIN Sales ON Dogs.[Dog Number] =
> Sales.[Dog Number]) ON Invoices.[Invoice Number] = Sales.[Invoice Number]
> WHERE (((Invoices.[Invoice Number])=(SELECT Max([Invoice Number]) FROM
Sales
> As S2 WHERE S2.[Dog Number] = Sales.[Dog Number])))
> ORDER BY Sales.[Dog Number];
>
> How do I correct the WHERE statement in my update query to put the
> SalesPrice value from the Dogs table into the Sales record which is the
last
> one (Max of Invoice Number) for all  dogs that have been returned?
>
> Thanks ahead of time for any assistance with this. It is most appreciated.
>
> Joan
>
>
> Other relevant info:
> Invoices < Sales > Dogs
>        1  :      M     :     1
>
> INVOICES
>   [Invoice Number]  (Primary Key)
>   DateSold
>   Store
>   Type
>
> SALES
>    [Invoice Number]  (Primary Key)
>    [Dog Number]      (Primary Key)
>    InvSalePrice
>
> DOGS
>    [Dog Number]          (Primary Key)
>    Returned
>    Salesprice      (Field where salesmen indicate the price that the dog
is
> sold for before invoicing. Latest price)
>    SalesPrice1    (If dog is returned and then resold, the saleprice from
> the first sale is stored here.)
>
>
>

Relevant Pages

  • Re: Excluding a month of records ONLY after all fields are updated
    ... So I need to build a new query or is there a way to use my existing query and ... Orders.CustomerID) INNER JOIN (Products INNER JOIN ON ... NOT IN FROM SALES WHERE ... are entered AND all the [invoice] checkboxes are check for that month? ...
    (microsoft.public.access.queries)
  • Re: tracking inventory
    ... Just to expand on the last note about the sales sheet; ... adjusting inventory levels to improve cash flow. ... after filling in the invoice other things happen in the background. ... I have all the products on one sheet with all information and a blank column ...
    (microsoft.public.excel.misc)
  • Re: Subform problem
    ... UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore ... > txtstore value in the FinalStore field for the dog on the Invoice subform ... The store field on the main form ... > dog record in the subform has Dogs.Store as it's ...
    (microsoft.public.access.formscoding)
  • Re: tracking inventory
    ... As you learned, to set up the customer table effectively you should have one row per customer, with the various details in columns. ... Since you want the invoice to drive the sales table we will break tradition somewhat, and end up putting more information in the sales table than we might if we were building this in a proper database*. ... as well", by that I mean, initally I just wanted to keep track of inventory because our office is not in the same location as the warehouse, and sometimes the manual entries are not done after picking an order. ...
    (microsoft.public.excel.misc)
  • Re: Update problem
    ... Invoice Number. ... >> UPDATE Sales INNER JOIN MaxInvoiceDog ON Sales.= ... >> invoice for all dogs, then you need to join on Dog Number. ... >>> I am having problems setting my criteria in an update query. ...
    (microsoft.public.access.queries)