Update query

[Gus Chuch]

Are you aloud to qualify criteria’s in an UPDATE Query?
I’m trying do a UPDATE Query to import information from one table to another
but I’m having trouble getting it to work. If I run the query as followed it
works find.

UPDATE tblIncomeDetail SET tblIncomeDetail.IncomeDetailID = "1893"
WHERE (((tblIncomeDetail.LocationID)=6) AND
((tblIncomeDetail.Date)=#1/4/2008#));

But when I qualify my criteria’s and SET , it doesn’t

UPDATE tblIncomeDetail SET tblIncomeDetail.IncomeDetailID =
[Tables]![tblIncomeSum]![IncomeID]
WHERE (((tblIncomeDetail.LocationID)=[Tables]![tblIncomeSum]![LocationID])
AND ((tblIncomeDetail.Date)=[Tables]![tblIncomeSum]![Date]));

Any ideas?

 

[John Spencer]

Normally, I would expect the syntax to be more like

UPDATE tblIncomeDetail INNER JOIN tblIncomeSum
ON tblIncomeDetail.SOMEFIELD = tblIncomeSum.SOMEFIELD
SET tblIncomeDetail.IncomeDetailID = [tblIncomeSum].[IncomeID]
WHERE tblIncomeDetail.LocationID=[tblIncomeSum].[LocationID]
AND tblIncomeDetail.Date=[tblIncomeSum].[Date]

WHAT are
-- [Tables]![tblIncomeSum]![IncomeID]
-- [Tables]![tblIncomeSum]![LocationID]
-- [Tables]![tblIncomeSum]![Date]
referring to?

Are you trying to refer to a field in a table named tblIncomeSum? If so
what is the relationship between tblIncomeSum and tblIncomeDetails?
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Are you aloud to qualify criteria’s in an UPDATE Query?
I’m trying do a UPDATE Query to import information from one table to another
but I’m having trouble getting it to work. If I run the query as followed it
works find.

UPDATE tblIncomeDetail SET tblIncomeDetail.IncomeDetailID = "1893"
WHERE (((tblIncomeDetail.LocationID)=6) AND
((tblIncomeDetail.Date)=#1/4/2008#));

But when I qualify my criteria’s and SET , it doesn’t

UPDATE tblIncomeDetail SET tblIncomeDetail.IncomeDetailID =
[Tables]![tblIncomeSum]![IncomeID]
WHERE (((tblIncomeDetail.LocationID)=[Tables]![tblIncomeSum]![LocationID])
AND ((tblIncomeDetail.Date)=[Tables]![tblIncomeSum]![Date]));

Any ideas?

There is no such thing as a [Tables]! collection in SQL, and you must include
both tables in a JOIN in your query. Try

UPDATE tblIncomeDetail
INNER JOIN tblIncomeSum
ON tblIncomeDetail.LocationID = tblIncomeSum.LocationID
AND tblIncomeDetail.[Date] = tblIncomeSum.[Date]
SET tblIncomeDetail.IncomeDetailID = tblIncomeSum.IncomeID;

You may need a unique Index on the combination of LocationID and Date for this
to work; and do be aware that Date is a reserved word (for the builtin Date()
function), and Access can and will get confused. Change the fieldname to
IncomeDate, say.

John W. Vinson [MVP]

 

[Gus Chuch]

FYI
I also found that you can use a SubQuery in the where clause

UPDATE tblIncomeDetail INNER JOIN tblIncomeSum
ON (tblIncomeDetail.LocationID = tblIncomeSum.LocationID)
AND (tblIncomeDetail.IncomeDetailDate = tblIncomeSum.IncomeSumDate)
SET tblIncomeDetail.IncomeDetailID = [tblIncomeSum]![IncomeSumID]
WHERE
((((SELECT tblIncomeSum.IncomeSumID
FROM tblIncomeSum
WHERE (((tblIncomeSum.LocationID)=[Forms]![frmRouteIncome]![txtLocationID])
AND
((tblIncomeSum.IncomeSumDate)=[Forms]![frmRouteIncome]![txtDate]))))<>False));

--
thank You

Are you aloud to qualify criteria’s in an UPDATE Query?
I’m trying do a UPDATE Query to import information from one table to another
but I’m having trouble getting it to work. If I run the query as followed it
works find.

UPDATE tblIncomeDetail SET tblIncomeDetail.IncomeDetailID = "1893"
WHERE (((tblIncomeDetail.LocationID)=6) AND
((tblIncomeDetail.Date)=#1/4/2008#));

But when I qualify my criteria’s and SET , it doesn’t

UPDATE tblIncomeDetail SET tblIncomeDetail.IncomeDetailID =
[Tables]![tblIncomeSum]![IncomeID]
WHERE (((tblIncomeDetail.LocationID)=[Tables]![tblIncomeSum]![LocationID])
AND ((tblIncomeDetail.Date)=[Tables]![tblIncomeSum]![Date]));

Any ideas?

There is no such thing as a [Tables]! collection in SQL, and you must include
both tables in a JOIN in your query. Try

UPDATE tblIncomeDetail
INNER JOIN tblIncomeSum
ON tblIncomeDetail.LocationID = tblIncomeSum.LocationID
AND tblIncomeDetail.[Date] = tblIncomeSum.[Date]
SET tblIncomeDetail.IncomeDetailID = tblIncomeSum.IncomeID;

You may need a unique Index on the combination of LocationID and Date for this
to work; and do be aware that Date is a reserved word (for the builtin Date()
function), and Access can and will get confused. Change the fieldname to
IncomeDate, say.

John W. Vinson [MVP]