Update problem

J

Joan

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.)
 
J

John Viescas

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 said:
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.)
Click to expand...
 
J

Joan

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 said:
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 said:
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.)
Click to expand...
Click to expand...
 
J

John Viescas

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 said:
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 said:
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 said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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.)
Click to expand...
Click to expand...
Click to expand...
 
J

Joan

Thanks, John.
I will give it a try.

Joan

John Viescas said:
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 said:
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 said:
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
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
Click to expand...
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
Click to expand...
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.)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update query won't work 10
Update problem 4
Not getting results I need 2
Returning the correct records 4
VBA works in orignal but fails in copy of database 1
Invoicing problem 0
Append Crosstab Query to Existing Table 0
Pls Help - "between" Criterea returning no results 11

Top