Update query won't work

J

Joan

Hi,

I am trying to run an update query based on three tables in which there is a
many-to-one-to-many relationship and it won't work. Here is the SQL of the
update query I am trying to run:

UPDATE Invoices INNER JOIN (Dogs INNER JOIN Sales ON Dogs.[Dog Number] =
Sales.[Dog Number]) ON Invoices.[Invoice Number] = Sales.[Invoice Number]
SET Dogs.ReturnedInvoice2 = DMin("[Sales].[Invoice
Number]","Sales","[Dogs].[Returned] Is Not Null"), Dogs.ReturnedSaleDate2 =
DMin("[Invoices].[DateSold]","Invoices","Dogs.[Returned] Is Not Null");


I had to add some new fields and change some field names in my Dogs table.
Consequently, since the database is already being used, I need to run some
update queries to get the correct data in the correct fields. There can be
multiple sales for a single dog, because the dog can be returned and resold.
Also there can be multiple dogs listed as sold per invoice. The DateSold
field is in the Invoices table and the Invoice Number and Dog Number are
compound primary keys in the Sales table. A partial table structure is
below:

INVOICES
Invoice Number (Primary Key)
DateSold

SALES
Invoice Number (Primary Key)
Dog Number (Primary Key)

DOGS
Dog Number (Primary Key)
Returned
ReturnedInvoice2
ReturnedSaleDate2


For dogs that are returned, I want to put the minimum [DateSold] value in
the ReturnedSaleDate2 column and the minimum [Invoice Number] value in the
ReturnedInvoice2 column for that particular dog record. How do I do this?
What is the solution if my query is not updateable?

Joan
 
C

chris

As a first guess, I would say you have a problem in your
two DMIN expressions.
The criteria expressions are taken from a table DOGS while
the return value is taken from table SALES.

Try basing the DMIN expressions on a query which links
these two tables.

Even then yuo may have problems as Access is very fussy
about mixing update queries with totals queries, but it is
worth a try.
 
J

John Vinson

Hi,

I am trying to run an update query based on three tables in which there is a
many-to-one-to-many relationship and it won't work. Here is the SQL of the
update query I am trying to run:

UPDATE Invoices INNER JOIN (Dogs INNER JOIN Sales ON Dogs.[Dog Number] =
Sales.[Dog Number]) ON Invoices.[Invoice Number] = Sales.[Invoice Number]
SET Dogs.ReturnedInvoice2 = DMin("[Sales].[Invoice
Number]","Sales","[Dogs].[Returned] Is Not Null"), Dogs.ReturnedSaleDate2 =
DMin("[Invoices].[DateSold]","Invoices","Dogs.[Returned] Is Not Null");
Click to expand...

Since you're updating different records in two different tables, I
suggest that you run this as two queries - one updating Sales and the
other updating Dogs. I believe the problem is that Access cannot
unambiguously link the two "many" side tables.
 
J

Joan

chris said:
As a first guess, I would say you have a problem in your
two DMIN expressions.
The criteria expressions are taken from a table DOGS while
the return value is taken from table SALES.

Try basing the DMIN expressions on a query which links
these two tables.

Even then yuo may have problems as Access is very fussy
about mixing update queries with totals queries, but it is
worth a try.
-----Original Message-----
Hi,

I am trying to run an update query based on three tables in which there is a
many-to-one-to-many relationship and it won't work. Here is the SQL of the
update query I am trying to run:

UPDATE Invoices INNER JOIN (Dogs INNER JOIN Sales ON Dogs. [Dog Number] =
Sales.[Dog Number]) ON Invoices.[Invoice Number] = Sales. [Invoice Number]
SET Dogs.ReturnedInvoice2 = DMin("[Sales].[Invoice
Number]","Sales","[Dogs].[Returned] Is Not Null"), Dogs.ReturnedSaleDate2 =
DMin("[Invoices].[DateSold]","Invoices","Dogs.[Returned] Is Not Null");


I had to add some new fields and change some field names in my Dogs table.
Consequently, since the database is already being used, I need to run some
update queries to get the correct data in the correct fields. There can be
multiple sales for a single dog, because the dog can be returned and resold.
Also there can be multiple dogs listed as sold per invoice. The DateSold
field is in the Invoices table and the Invoice Number and Dog Number are
compound primary keys in the Sales table. A partial table structure is
below:

INVOICES
Invoice Number (Primary Key)
DateSold

SALES
Invoice Number (Primary Key)
Dog Number (Primary Key)

DOGS
Dog Number (Primary Key)
Returned
ReturnedInvoice2
ReturnedSaleDate2


For dogs that are returned, I want to put the minimum [DateSold] value in
the ReturnedSaleDate2 column and the minimum [Invoice Number] value in the
ReturnedInvoice2 column for that particular dog record. How do I do this?
What is the solution if my query is not updateable?

Joan


.
Click to expand...
Click to expand...
 
J

Joan

Hi Chris,
As you suggested, I tried basing the DMIN expressions on a query which
links these two tables but I still get a runtime error: "Operation must use
an updateable query." Below are the two SQLs. First I tried putting all
three tables into one query which is the frist SQL. Then I used this
query(UpdateRetInvoiceSaleDate3) in my Update query, the 2nd SQL below:



SELECT Sales.[Dog Number], Invoices.DateSold, Invoices.[Invoice Number] AS
InvoiceNumber, Dogs.ReturnedInvoice2, Dogs.ReturnedSaleDate2,
Dogs.Returned
FROM Invoices INNER JOIN (Dogs INNER JOIN Sales ON Dogs.[Dog Number] =
Sales.[Dog Number]) ON Invoices.[Invoice Number] = Sales.[Invoice Number]
GROUP BY Sales.[Dog Number], Invoices.DateSold, Invoices.[Invoice Number],
Dogs.ReturnedInvoice2, Dogs.ReturnedSaleDate2, Dogs.Returned;

UPDATE UpdateRetInvoiceSaleDate SET
UpdateRetInvoiceSaleDate.ReturnedInvoice2 =
DMin(UpdateRetInvoiceSaleDate!InvoiceNumber,"UpdateRetInvoiceSaleDate",Updat
eRetInvoiceSaleDate!Returned Is Not Null),
UpdateRetInvoiceSaleDate.ReturnedSaleDate2 =
DMin(UpdateRetInvoiceSaleDate!DateSold,"UpdateRetInvoiceSaleDate",UpdateRetI
nvoiceSaleDate.Returned Is Not Null);


Any other thoughts?
Joan


chris said:
As a first guess, I would say you have a problem in your
two DMIN expressions.
The criteria expressions are taken from a table DOGS while
the return value is taken from table SALES.

Try basing the DMIN expressions on a query which links
these two tables.

Even then yuo may have problems as Access is very fussy
about mixing update queries with totals queries, but it is
worth a try.
-----Original Message-----
Hi,

I am trying to run an update query based on three tables in which there is a
many-to-one-to-many relationship and it won't work. Here is the SQL of the
update query I am trying to run:

UPDATE Invoices INNER JOIN (Dogs INNER JOIN Sales ON Dogs. [Dog Number] =
Sales.[Dog Number]) ON Invoices.[Invoice Number] = Sales. [Invoice Number]
SET Dogs.ReturnedInvoice2 = DMin("[Sales].[Invoice
Number]","Sales","[Dogs].[Returned] Is Not Null"), Dogs.ReturnedSaleDate2 =
DMin("[Invoices].[DateSold]","Invoices","Dogs.[Returned] Is Not Null");


I had to add some new fields and change some field names in my Dogs table.
Consequently, since the database is already being used, I need to run some
update queries to get the correct data in the correct fields. There can be
multiple sales for a single dog, because the dog can be returned and resold.
Also there can be multiple dogs listed as sold per invoice. The DateSold
field is in the Invoices table and the Invoice Number and Dog Number are
compound primary keys in the Sales table. A partial table structure is
below:

INVOICES
Invoice Number (Primary Key)
DateSold

SALES
Invoice Number (Primary Key)
Dog Number (Primary Key)

DOGS
Dog Number (Primary Key)
Returned
ReturnedInvoice2
ReturnedSaleDate2


For dogs that are returned, I want to put the minimum [DateSold] value in
the ReturnedSaleDate2 column and the minimum [Invoice Number] value in the
ReturnedInvoice2 column for that particular dog record. How do I do this?
What is the solution if my query is not updateable?

Joan


.
Click to expand...
Click to expand...
 
J

Joan

Hi John,

Thanks for your reply.

Actually both fields ( [ReturnedInvoice2] ,[ReturnedSaleDate2] ) that I am
trying to update are in the Dogs table. The values that I want to update
these fields to are the Min of the DateSold field for that dog in the
Invoices table and the Min of the InvoiceNumber field for that dog which is
in both the Sales and the Invoices tables. The Sales table is the M(many)
side of the join between the Invoices and Dogs tables. The Sales table has
both Dog Number and InvoiceNumber as its compound primary keys. I hope I am
looking at the relationship right. One dog can be sold many times if it is
returned and One invoice can have many different dogs' sales listed on it.

I tried breaking this up and running each update separately by trying first
just to update the ReturnedInvoice2 field. Below are the SQL's for the two
queries I tried. The first query (UpdateRetInvoiceSaleDate2 ) shows the
minimum [Invoice Number] for each dog where [Returned] Is Not Null. The
second query is supposed to update the [ReturnedSaleDate2] field to the
minimum [Invoice Number] found in the first query. However when I try to
run this query I get the message "Operation must use an updateable query".

SELECT Dogs.[Dog Number], Min(Sales.[Invoice Number]) AS InvoiceNumber,
Dogs.ReturnedInvoice2, Dogs.Returned
FROM Dogs INNER JOIN Sales ON Dogs.[Dog Number] = Sales.[Dog Number]
WHERE (((Dogs.Returned) Is Not Null))
GROUP BY Dogs.[Dog Number], Dogs.ReturnedInvoice2, Dogs.Returned;


UPDATE UpdateRetInvoiceSaleDate2 SET
UpdateRetInvoiceSaleDate2.ReturnedInvoice2 =
UpdateRetInvoiceSaleDate2.InvoiceNumber;


I have cascading updates enabled for the join between Dogs and Sales. I've
also tried using Sales.[Dog Number] instead of Dogs.[Dog Number] in my first
query with the same result. Does the aggregate function (Min() ) in the
first query cause it not to be updateable? If so, how do I work around
this?

Thanks for you ahead for your help.

Joan

John Vinson said:
Hi,

I am trying to run an update query based on three tables in which there is a
many-to-one-to-many relationship and it won't work. Here is the SQL of the
update query I am trying to run:

UPDATE Invoices INNER JOIN (Dogs INNER JOIN Sales ON Dogs.[Dog Number] =
Sales.[Dog Number]) ON Invoices.[Invoice Number] = Sales.[Invoice Number]
SET Dogs.ReturnedInvoice2 = DMin("[Sales].[Invoice
Number]","Sales","[Dogs].[Returned] Is Not Null"), Dogs.ReturnedSaleDate2 =
DMin("[Invoices].[DateSold]","Invoices","Dogs.[Returned] Is Not Null");
Click to expand...

Since you're updating different records in two different tables, I
suggest that you run this as two queries - one updating Sales and the
other updating Dogs. I believe the problem is that Access cannot
unambiguously link the two "many" side tables.
Click to expand...
 
F

fofa

You are telling it you are updating Invoices, but the columns you ar
trying to change are not in Invoices
 
J

John Vinson

Actually both fields ( [ReturnedInvoice2] ,[ReturnedSaleDate2] ) that I am
trying to update are in the Dogs table.
Click to expand...

Ok - then you only need to include the Dogs table in your Update
statement! your join to the other tables is interfering with
updatability.

Try

UPDATE Dogs
SET Dogs.ReturnedInvoice2 =
DMin("[Sales].[Invoice Number]","Sales","[Dog Number] =" & [Dog
Number]), Dogs.ReturnedSaleDate2 =
DMin("[Invoices].[DateSold]","Invoices",[Dog Number] =" & [Dog
Number])
WHERE Dogs.[Returned] Is Not Null;

The DMin() functions will find the Invoice Number and Date from their
respective tables; the criterion will look up each dog with a non-null
RETURNED value and find the minimum value in the other table.
 
J

Joan

Hi John,

I created the update query using the SQL you recommended and ran the query.
The first field, ReturnedInvoice2 was updated but the second field, Returned
SaleDate2 was not. Could it be because of the 'where' argument of the DMin
function for ReturnedSaleDate2? Here, a Dog Number field is in not in the
Invoices table? A particular dog is linked to the Invoices table by way of
the Sales table.

Joan



John Vinson said:
Actually both fields ( [ReturnedInvoice2] ,[ReturnedSaleDate2] ) that I am
trying to update are in the Dogs table.
Click to expand...

Ok - then you only need to include the Dogs table in your Update
statement! your join to the other tables is interfering with
updatability.

Try

UPDATE Dogs
SET Dogs.ReturnedInvoice2 =
DMin("[Sales].[Invoice Number]","Sales","[Dog Number] =" & [Dog
Number]), Dogs.ReturnedSaleDate2 =
DMin("[Invoices].[DateSold]","Invoices",[Dog Number] =" & [Dog
Number])
WHERE Dogs.[Returned] Is Not Null;

The DMin() functions will find the Invoice Number and Date from their
respective tables; the criterion will look up each dog with a non-null
RETURNED value and find the minimum value in the other table.
Click to expand...
 
J

John Vinson

I created the update query using the SQL you recommended and ran the query.
The first field, ReturnedInvoice2 was updated but the second field, Returned
SaleDate2 was not. Could it be because of the 'where' argument of the DMin
function for ReturnedSaleDate2? Here, a Dog Number field is in not in the
Invoices table? A particular dog is linked to the Invoices table by way of
the Sales table.
Click to expand...

ok... sorry! Being too hasty again.

You probably need to pick up the data from the Sales table, not the
Invoices table. Since I'm not sure I understand the data relationships
correctly I'm guessing - the principle is that you base the Update
query on the table you want to update, and (where possible) use
information in that table as a criterion in the domain function to
locate the value.
 
J

Joan

"You probably need to pick up the data from the Sales table, not the
Invoices table."
Click to expand...

John,
I'm not sure what you mean by 'pick up the data from the Sales table'.
[DateSold] in the DMin statement:
DMin("[Invoices].[DateSold]","Invoices","[Dog Number] = " & [Dog Number])
is only in the Invoices table and is not in the Sales table. Dog Number
however is in just the Sales and Dogs tables and not in the Invoices table.
I now understand that I need to base the Update query on the Dogs table
because that is where the fields I want to update are. But how do I use
information in the Dogs table as criterion in the domain function when this
table is not directly linked to the table that contains the value I want to
Update To? Invoices.[Invoice Number] = Sales.[Invoice Number]
And Sales.[Dog Number] = Dogs.[Dog Number]
How do I say this in the criterion portion of the domain function to locate
the value? Will this work? If I am on the right track, I may need help with
the syntax .

Thanks for helping me with this.

Joan
 

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 problem 4
Returning the correct records 4
Update problem 4
Append Crosstab Query to Existing Table 0
Not getting results I need 2
Invoicing problem 0
Query problem 0
manual deletion of records in datasheet based on joined tables 5

Top