Re: Update problem

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

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

Date: Tue, 10 Feb 2004 18:20:10 -0600

Joan-

You still didn't need the entire WHERE clause. The MaxInvoiceDog.Returned
for not null is good, but the ON clause takes care of the comparison on
Invoice 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:OCPWTJ57DHA.2404@TK2MSFTNGP11.phx.gbl...
> John,
>
> I used the following  update query and it worked.  At least it did what I
> wanted.
>
> 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])=[MaxInvoiceDog].[Invoice Number]));
>
> Thanks for your reply to my post.
>
> Joan
>
>
>
> "John Viescas" <JohnV@nomail.please> wrote in message
> news:uNsXB027DHA.2572@TK2MSFTNGP09.phx.gbl...
> > 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

  • Update problem
    ... I am having problems setting my criteria in an update query. ... table contains dog sale information from the last time the dog was sold. ... in the InvSalePrice for both instances of a dog's sales instead of just the ... In (SELECT [Invoice Number] FROM MaxInvoiceDog))); ...
    (microsoft.public.access.queries)
  • Update query after an append query?+
    ... particular when I first run an append query followed by an update query. ... Invoice Number of this invoice and the SalesPrice for each dog on the _ ... The query, UpdateFinalStoreRetDog, is supposed to put the Store that the dog ...
    (microsoft.public.access.formscoding)
  • 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: 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: Subform problem
    ... txtstore value in the FinalStore field for the dog on the Invoice subform ... but it also puts this value in every dog in the query not just the ones on ... The store field on the main form ... dog record in the subform has Dogs.Store as it's ...
    (microsoft.public.access.formscoding)