Need Help With Append Query

[dvannoy]

I have a table that is linked to a DB file. The DB file gets updated from an AS400 query that I built. In my table I have a key field so when I run my append query I donot get duplicate records. I also have a field called QTY.
One problem I will run into is, they may go back to the AS400 and correct the QTY we Received. the linked DB file would have the correct QTY. But if I already ran the append query, the table I append to could have the wrong QTY.

Is there a way to have the query look at the field "QTY" and if its different just update that field?

Thanks

 

[kathryn]

Yes, as long as the two tables have a unique identifier in common.

If they do, create a new query, and add both tables. Join them on the unique identifier. Change the query to an update query by choosing Query->Update from the menu

Add the QTY field from your table to the query grid. In the criteria field add

<>[dbtablename]![QTY]

in the Update to field enter

[dbtablename]![QTY]


and run the query. Of course, do this on test data first, maybe by making copies of both tables and running the query on them first. I am giving you this off the top of my head, but this is the gist of how to do it.

Hope this helps.
Kathryn

 

[dvannoy]

The table That is linked to the DB Fil does not have a key field. what the DB file has in it is, every single line item on a doc# that was received..

so What i had to do in my append query is, string the doc# and page# (eg..12345-1A) and then I assigned that field my key field so I would not get duplicate records. Also in my append query I did a count on QTY...

so it would look like this : doc# 12345-1a qty: 10

do you think you suggestion would still work???

Thanks

 

[kathryn]

So does the DB table have both the doc # and Page # for each record? if so, you can use the combination of the two for a unique id.

Let me know. Kathryn