Update problem

[Joan]

Can anyone tell me how to update a field using an update query where the
Update To value is a field in another table and there is limiting criteria
on the Update To value? I will try to explain what I am needing. I want to
update a field for certain records in the dogs table where the Returned
field (from Dogs) Is Not Null. I want to update Dogs.Store1 to a value from
Invoices.Store. However, a single dog could have more than 1 invoice record
if it is sold, returned and then resold. For these returned dogs, I want
the Invoice.Store value from Invoices where the Invoice Number is the
minimum for that dog. In other words, the first store that the dog was sold
to. Invoice Numbers are in chronological consecutive order.

The problem enters because Dogs is not directly linked to Invoices, but is
to Sales and Sales is directly linked to Invoices. How do I tell my query
how to find the correct Invoices.Store value to use as an Update To value?

I've tried using the following expression as the Update To value, but it
did not work.

DLookUp("[Store]","Invoices"," DMin([Sales].[Invoice Number], Sales, [Dog
Number]= & [Dog Number])")

The SQL I tried:
UPDATE Dogs SET Dogs.Store1 = DLookUp("[Store]","Invoices","
DMin([Sales].[Invoice Number], Sales, [Dog Number]= & [Dog Number])")
WHERE (((Dogs.Returned) Is Not Null));


Relationship is:
INVOICES < SALES > DOGS

An INVOICE can have many dogs per invoice
A single DOG can be sold many times and therefore have more than one
invoice. As in sold, returned and resold.

Tables & Primary Keys:
INVOICES
Invoice Number (PK)

SALES
Invoice Number (PK)
Dog Number (PK)

DOGS
Dog Number (PK)

Any assistance in getting this update to work would be so appreciated!

Joan

 

[John Viescas]

Try this:

UPDATE Dogs
SET Dogs.Store1 =
(SELECT Invoices.Store
FROM Invoices
WHERE Invoices.[Invoice Number] =
(SELECT Min([Invoice Number]
FROM Sales
WHERE Sales.[Dog Number] = Dogs.[Dog Number]))
WHERE Dogs.Returned Is Not Null;

I don't guarantee that it will work because JET sometimes doesn't understand
a correlated reference (Dogs.[Dog Number]) buried two levels deep.

Don't you wish you had designed the field names without spaces so you
wouldn't have to use all the annoying brackets?

--
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/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Joan]

Hi John,
Thanks for answering my post.
I tried the SQL that you suggested and I get a runtime error (3073),
"Operation must use an updateable query". What should I try next? Are
there any work arounds?

Joan

Try this:

UPDATE Dogs
SET Dogs.Store1 =
(SELECT Invoices.Store
FROM Invoices
WHERE Invoices.[Invoice Number] =
(SELECT Min([Invoice Number]
FROM Sales
WHERE Sales.[Dog Number] = Dogs.[Dog Number]))
WHERE Dogs.Returned Is Not Null;

I don't guarantee that it will work because JET sometimes doesn't understand
a correlated reference (Dogs.[Dog Number]) buried two levels deep.

Don't you wish you had designed the field names without spaces so you
wouldn't have to use all the annoying brackets?

--
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/site/TR?pg=personal&fr_id=1090&px=1434411

Can anyone tell me how to update a field using an update query where the
Update To value is a field in another table and there is limiting criteria
on the Update To value? I will try to explain what I am needing. I

want

to
update a field for certain records in the dogs table where the Returned
field (from Dogs) Is Not Null. I want to update Dogs.Store1 to a value from
Invoices.Store. However, a single dog could have more than 1 invoice record
if it is sold, returned and then resold. For these returned dogs, I want
the Invoice.Store value from Invoices where the Invoice Number is the
minimum for that dog. In other words, the first store that the dog was sold
to. Invoice Numbers are in chronological consecutive order.

The problem enters because Dogs is not directly linked to Invoices, but is
to Sales and Sales is directly linked to Invoices. How do I tell my query
how to find the correct Invoices.Store value to use as an Update To value?

I've tried using the following expression as the Update To value, but it
did not work.

DLookUp("[Store]","Invoices"," DMin([Sales].[Invoice Number], Sales, [Dog
Number]= & [Dog Number])")

The SQL I tried:
UPDATE Dogs SET Dogs.Store1 = DLookUp("[Store]","Invoices","
DMin([Sales].[Invoice Number], Sales, [Dog Number]= & [Dog Number])")
WHERE (((Dogs.Returned) Is Not Null));


Relationship is:
INVOICES < SALES > DOGS

An INVOICE can have many dogs per invoice
A single DOG can be sold many times and therefore have more than one
invoice. As in sold, returned and resold.

Tables & Primary Keys:
INVOICES
Invoice Number (PK)

SALES
Invoice Number (PK)
Dog Number (PK)

DOGS
Dog Number (PK)

Any assistance in getting this update to work would be so appreciated!

Joan

 

[John Viescas]

I was afraid of that - JET sees the aggregate function (even tho it's buried
in a subquery) and declares the whole thing not updatable.

You can create a working table from something like:

SELECT Sales.[Dog Number], Invoices.Store
FROM Sales INNER JOIN Invoices
ON Sales.[Invoice Number] = Invoices.[Invoice Number]
WHERE Invoices.[Invoice Number] =
(SELECT Min([Invoice Number]
FROM Sales As S2
WHERE S2.[Dog Number] = Sales.[Dog Number])


Now turn that into a Make Table query, and set the Primary Key of the
resulting table to Dog Number. You can then use the temp table in an INNER
JOIN with Dogs to do the update.

But I question why you're doing this in the first place. The Store ID in
Dogs from the first sale of a returned dog is basically a calculated field -
something you should avoid in a good relational design. But then maybe
you're trying to avoid the overhead of having to do a Select Min every time
you need this value in a report. Just be aware that you'll need to
periodically run your final update query or take other steps in code to keep
this value maintained correctly.

--
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 John,
Thanks for answering my post.
I tried the SQL that you suggested and I get a runtime error (3073),
"Operation must use an updateable query". What should I try next? Are
there any work arounds?

Joan

Try this:

UPDATE Dogs
SET Dogs.Store1 =
(SELECT Invoices.Store
FROM Invoices
WHERE Invoices.[Invoice Number] =
(SELECT Min([Invoice Number]
FROM Sales
WHERE Sales.[Dog Number] = Dogs.[Dog Number]))
WHERE Dogs.Returned Is Not Null;

I don't guarantee that it will work because JET sometimes doesn't understand
a correlated reference (Dogs.[Dog Number]) buried two levels deep.

Don't you wish you had designed the field names without spaces so you
wouldn't have to use all the annoying brackets?

--
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/site/TR?pg=personal&fr_id=1090&px=1434411

Can anyone tell me how to update a field using an update query where the
Update To value is a field in another table and there is limiting criteria
on the Update To value? I will try to explain what I am needing. I

want

to
update a field for certain records in the dogs table where the Returned
field (from Dogs) Is Not Null. I want to update Dogs.Store1 to a

value
from

Invoices.Store. However, a single dog could have more than 1 invoice record
if it is sold, returned and then resold. For these returned dogs, I want
the Invoice.Store value from Invoices where the Invoice Number is the
minimum for that dog. In other words, the first store that the dog was sold
to. Invoice Numbers are in chronological consecutive order.

The problem enters because Dogs is not directly linked to Invoices,

but

is

to Sales and Sales is directly linked to Invoices. How do I tell my query
how to find the correct Invoices.Store value to use as an Update To value?

I've tried using the following expression as the Update To value, but it
did not work.

DLookUp("[Store]","Invoices"," DMin([Sales].[Invoice Number], Sales, [Dog
Number]= & [Dog Number])")

The SQL I tried:
UPDATE Dogs SET Dogs.Store1 = DLookUp("[Store]","Invoices","
DMin([Sales].[Invoice Number], Sales, [Dog Number]= & [Dog Number])")
WHERE (((Dogs.Returned) Is Not Null));


Relationship is:
INVOICES < SALES > DOGS

An INVOICE can have many dogs per invoice
A single DOG can be sold many times and therefore have more than one
invoice. As in sold, returned and resold.

Tables & Primary Keys:
INVOICES
Invoice Number (PK)

SALES
Invoice Number (PK)
Dog Number (PK)

DOGS
Dog Number (PK)

Any assistance in getting this update to work would be so appreciated!

Joan

 

[Joan]

John,
It's working !
I created the Make Table query like you suggested and used it in my
Update query along with the Dogs table and it does what I wanted!
I needed this update query because I had made some changes to the DOGS and
SALES table structure and needed to
get the current data in the tables into the correct fields. These are
one-time only updates. Dogs.Store is a field where the salesmen indicate
what store the dogs should be invoiced to.
Thank you for all of your help! I had never created Make Table queries
before, so with this I learned something new.

Joan

I was afraid of that - JET sees the aggregate function (even tho it's buried
in a subquery) and declares the whole thing not updatable.

You can create a working table from something like:

SELECT Sales.[Dog Number], Invoices.Store
FROM Sales INNER JOIN Invoices
ON Sales.[Invoice Number] = Invoices.[Invoice Number]
WHERE Invoices.[Invoice Number] =
(SELECT Min([Invoice Number]
FROM Sales As S2
WHERE S2.[Dog Number] = Sales.[Dog Number])


Now turn that into a Make Table query, and set the Primary Key of the
resulting table to Dog Number. You can then use the temp table in an INNER
JOIN with Dogs to do the update.

But I question why you're doing this in the first place. The Store ID in
Dogs from the first sale of a returned dog is basically a calculated field -
something you should avoid in a good relational design. But then maybe
you're trying to avoid the overhead of having to do a Select Min every time
you need this value in a report. Just be aware that you'll need to
periodically run your final update query or take other steps in code to keep
this value maintained correctly.

--
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 John,
Thanks for answering my post.
I tried the SQL that you suggested and I get a runtime error (3073),
"Operation must use an updateable query". What should I try next? Are
there any work arounds?

Joan

Try this:

UPDATE Dogs
SET Dogs.Store1 =
(SELECT Invoices.Store
FROM Invoices
WHERE Invoices.[Invoice Number] =
(SELECT Min([Invoice Number]
FROM Sales
WHERE Sales.[Dog Number] = Dogs.[Dog Number]))
WHERE Dogs.Returned Is Not Null;

I don't guarantee that it will work because JET sometimes doesn't understand
a correlated reference (Dogs.[Dog Number]) buried two levels deep.

Don't you wish you had designed the field names without spaces so you
wouldn't have to use all the annoying brackets?

--
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/site/TR?pg=personal&fr_id=1090&px=1434411
Can anyone tell me how to update a field using an update query where the
Update To value is a field in another table and there is limiting criteria
on the Update To value? I will try to explain what I am needing. I want
to
update a field for certain records in the dogs table where the Returned
field (from Dogs) Is Not Null. I want to update Dogs.Store1 to a value
from
Invoices.Store. However, a single dog could have more than 1 invoice
record
if it is sold, returned and then resold. For these returned dogs, I want
the Invoice.Store value from Invoices where the Invoice Number is the
minimum for that dog. In other words, the first store that the dog was
sold
to. Invoice Numbers are in chronological consecutive order.

The problem enters because Dogs is not directly linked to Invoices,

but

is
to Sales and Sales is directly linked to Invoices. How do I tell my query
how to find the correct Invoices.Store value to use as an Update To value?

I've tried using the following expression as the Update To value,

but
it

did not work.

DLookUp("[Store]","Invoices"," DMin([Sales].[Invoice Number], Sales, [Dog
Number]= & [Dog Number])")

The SQL I tried:
UPDATE Dogs SET Dogs.Store1 = DLookUp("[Store]","Invoices","
DMin([Sales].[Invoice Number], Sales, [Dog Number]= & [Dog Number])")
WHERE (((Dogs.Returned) Is Not Null));


Relationship is:
INVOICES < SALES > DOGS

An INVOICE can have many dogs per invoice
A single DOG can be sold many times and therefore have more than one
invoice. As in sold, returned and resold.

Tables & Primary Keys:
INVOICES
Invoice Number (PK)

SALES
Invoice Number (PK)
Dog Number (PK)

DOGS
Dog Number (PK)

Any assistance in getting this update to work would be so appreciated!

Joan