Access 2000, having problem with update

[ecupstid]

Table1
CycleEndDate ZipCode PiecesMailed PiecesScanned
11072002 39653 1000 0
11072002 71373 600 0
11072002 71334 500 0
11112002 39653 1200 0
11112002 71334 2000 0

Table2
CycleEndDate ZipCode ScanDate PiecesScanned
11072002 39653 11/08/2002 50
11072002 71373 11/08/2002 60
11072002 71373 11/09/2002 100
11112002 39653 11/12/2002 500
11072002 71334 11/12/2002 100
11112002 71334 11/13/2002 40
11112002 71334 11/14/2002 200

I have failed to find a way to write an Access Query or
SQL statement that will read the information in these two tables and update the accumulated values in table2.piecesscanned to table1.piecesscanned.

The query or SQL statement needs to match up table2.CycleEndDate to table1.CycleEndDate and table2.zipcode to table1.zipcode and accumulate the values in table2.piecesscanned for each match and update the accumulated table2.piecesscanned to the matching table1.piecesscanned resulting in the following for table1.

Table1
CycleEndDate ZipCode PiecesMailed PiecesScanned
11072002 39653 1000 50
11072002 71373 600 160
11072002 71334 500 100
11112002 39653 1200 500
11112002 71334 2000 240

I need to update table1 each time records are imported to
table2. I would greatly appreciate any help in solving this problem. I have written an SQL statement that will
show the accumulated values, but have not been able to get the values to update to table1.


HAVE TRIED THE FOLLOWING, BUT GET
Syntax error(missing operator) in query expression 'table1.piecesscanned + table2.piecesscanned From Table2'


Update Table1
Set Table1.PiecesScanned = Table1.PiecesScanned + Table2.PiecesScanned
FROM Table2
Where Table1.CycleEndDate = Table2.CycleEndDate
AND Table1.ZipCode = Table2.ZipCode

 

[Bariloche]

ecupstid,

My approach would be to create a make table query that does your math and then use that table (I call it tblTemp) to update table1.

Here's the query that does the cumulative calculation:<pre>
SELECT table1.CycleEndDate, table1.ZipCode, Sum([table1].[PiecesScanned]+[table2].[PiecesScanned]) AS CumlPiecesScanned INTO tblTemp
FROM table1 INNER JOIN table2 ON (table1.ZipCode = table2.ZipCode) AND (table1.CycleEndDate = table2.CycleEndDate)
GROUP BY table1.CycleEndDate, table1.ZipCode;</pre>


Then run your update query:<pre>
UPDATE table1 INNER JOIN tblTemp ON (table1.ZipCode = tblTemp.ZipCode) AND (table1.CycleEndDate = tblTemp.CycleEndDate) SET table1.PiecesScanned = [CumlPiecesScanned];</pre>

Don't know if this is what someone who knows a heck of alot more about SQL and databases than I do would come up with, but it does have the advantage of working.



enjoy


PS: Trying to do the update directly using the "Cumulative" query (as a Select query) gives me an "Operation must use an updateable query" message. The above is the approach I've taken when getting that message in the past. I'm sure there's some good reason why doing it directly doesn't work, but I don't know what it is, hence the "work around."
This message was edited by Bariloche on 2002-12-31 18:48

 

[ecupstid]

I appreicate your help, when I try running the first statement you suggested, an input box comes up for each columun asking for a parameter value, this is not what I need, I need the statement to get the value from the table. I tried entering a valid value for each columun, but the statement still created a blank table. What am I doing wrong?

Thanks,
Ed

 

[Bariloche]

Ed,

Its probably best if you recreate the query that I posted. That way the field names will be correct. I used the data you posted so it should be ok as is. But obviously something's not quite right. The idea behind the first query is to create a table that has the CycleEndDate, ZipCode, and a field that adds the PiecesScanned fields. At the very least, check your field names. If there's a name that Access doesn't recognize it will pop up a Parameter input box. Since that's not the intent of this query some field name needs to be changed.

Like I said though, its best to use the idea presented rather than the specific query.

Give it another look.

have fun

 

[ecupstid]

You were right, once I got all the field names spelled correctly the SQL statement worked just like you said....I really appreciate your expert advice and timely replies.

Thanks,
Ed