Append Querries

[Dustin B]

I would like to have me append query take a number in a field from one table
and add it to another number in a field in a different table. Would I change
the append to for that field and do the field + something? Here is the SQL
for this query. The field I would like to have added is ARAMOUNT to
TotalDeposits.


INSERT INTO tblArReport ( [Client#], [Invoice#], TotalDeposits )
SELECT Depotregister.[Client#], Depotregister.[Invoice#],
Depotregister.ARAmount
FROM Depotregister INNER JOIN tblArReport ON
Depotregister.[Invoice#]=tblArReport.[Invoice#]
WHERE (((Depotregister.[Client#]) Is Not Null) AND
((Depotregister.[Invoice#]) Is Not Null) AND ((Depotregister.ARAmount) Is Not
Null));

 

[John Spencer]

Try The following AFTER you back up your data.

INSERT INTO tblArReport ( [Client#], [Invoice#], TotalDeposits )
SELECT Depotregister.[Client#], Depotregister.[Invoice#],
Depotregister.ARAmount + NZ(tblArReport.TotalDeposits,0)
FROM Depotregister INNER JOIN tblArReport ON
Depotregister.[Invoice#]=tblArReport.[Invoice#]
WHERE (((Depotregister.[Client#]) Is Not Null) AND
AND ((Depotregister.ARAmount) Is Not Null));

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Dustin B]

I see what you are doing now be researching the commands you used. For some
reason there is a mistake in the prgraming though. I get a compile error
when I run it.

Compile error in query expression
'DepotRegister.ARAmount+NZ(tblArReport.TotalDeposits,0)'.

Try The following AFTER you back up your data.

INSERT INTO tblArReport ( [Client#], [Invoice#], TotalDeposits )
SELECT Depotregister.[Client#], Depotregister.[Invoice#],
Depotregister.ARAmount + NZ(tblArReport.TotalDeposits,0)
FROM Depotregister INNER JOIN tblArReport ON
Depotregister.[Invoice#]=tblArReport.[Invoice#]
WHERE (((Depotregister.[Client#]) Is Not Null) AND
AND ((Depotregister.ARAmount) Is Not Null));

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I would like to have me append query take a number in a field from one
table
and add it to another number in a field in a different table. Would I
change
the append to for that field and do the field + something? Here is the
SQL
for this query. The field I would like to have added is ARAMOUNT to
TotalDeposits.


INSERT INTO tblArReport ( [Client#], [Invoice#], TotalDeposits )
SELECT Depotregister.[Client#], Depotregister.[Invoice#],
Depotregister.ARAmount
FROM Depotregister INNER JOIN tblArReport ON
Depotregister.[Invoice#]=tblArReport.[Invoice#]
WHERE (((Depotregister.[Client#]) Is Not Null) AND
((Depotregister.[Invoice#]) Is Not Null) AND ((Depotregister.ARAmount) Is
Not
Null));

 

[Dustin B]

I took the NZ function out and took the (,0) off and there is not compile
error. THe only problem is now it just doesn't actually do anything. It
updates zero rows, although I have put data in the source that matches for
sure.

Try The following AFTER you back up your data.

INSERT INTO tblArReport ( [Client#], [Invoice#], TotalDeposits )
SELECT Depotregister.[Client#], Depotregister.[Invoice#],
Depotregister.ARAmount + NZ(tblArReport.TotalDeposits,0)
FROM Depotregister INNER JOIN tblArReport ON
Depotregister.[Invoice#]=tblArReport.[Invoice#]
WHERE (((Depotregister.[Client#]) Is Not Null) AND
AND ((Depotregister.ARAmount) Is Not Null));

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I would like to have me append query take a number in a field from one
table
and add it to another number in a field in a different table. Would I
change
the append to for that field and do the field + something? Here is the
SQL
for this query. The field I would like to have added is ARAMOUNT to
TotalDeposits.


INSERT INTO tblArReport ( [Client#], [Invoice#], TotalDeposits )
SELECT Depotregister.[Client#], Depotregister.[Invoice#],
Depotregister.ARAmount
FROM Depotregister INNER JOIN tblArReport ON
Depotregister.[Invoice#]=tblArReport.[Invoice#]
WHERE (((Depotregister.[Client#]) Is Not Null) AND
((Depotregister.[Invoice#]) Is Not Null) AND ((Depotregister.ARAmount) Is
Not
Null));

 

[tina]

Append queries *add* new records to a table. Update queries *change* data in
existing records in a table. if you're trying to change data in existing
records in a table (doesn't matter if you're using data from another table,
or something else), then use an Update query.

hth