help with append query...

[Guest]

good day all,


I have an append query that I have set up, but I want it to append records
based on a criteria... so if ordnum from tlb1 = ordnum appendquery then
update that record with the corresponding data? how do I code this in my
append query to match the criteria before the append?

Thanks in advance..

Brook

 

[tina]

you can't *update* a record in a table using an Append query. Append queries
*add* one or more new records to a table. Update queries modify data in one
or more existing records in a table. from your post, it's not clear whether
you want to add a record or modify an existing record.

hth

 

[Guest]

Hello Tina,

Thanks for the response.. here is what I have:

I have in a tbl with new data (tblnewdata):

ordnum colour1 colour 2 - 14 colour 14 - 19


in an existing table called tblinventory I have the same fields as above
along with about 20 other fields,

What I want to do is perform an update from my new table to my old table if
the ordnum match between tblnewdata & tblinventory, then update colour 1 from
tblnewdata with colour1 in tblinventory, update colour2-14 from tblnewdata
with colour 2 - 14 in tblinventory, and update colour 14 - 19 from tblnewdata
with colour 14 - 19 in tblinventory.

hope this clears things a little..

Brook

 

[tina]

ok, it sounds like you want to update existing records in tblinventory,
using data from tblnewdata. FIRST, back up your database. next, open a new
query in design view and add both tables. link the two tables on the ordnum
fields. add the fields you want to update to the design grid, pulling them
from tblinventory. on the menu bar, click Query | Update Query to change the
query to an Update query. on the Update To: line, for each field in the
query grid, enter the table name and the corresponding field, such as

[tblnewdata].[colour1]

in the colour1 column in the grid, etc.

after you've set the Update To: line for each column in the query grid, run
the query by clicking the exclamation point (!) button on the toolbar.

hth

 

[Guest]

Hello Tina,

Thank you very much... that was what I wasn't doing... I wasn't linking the
tables, I was trying to put this in the criteria field:

tblnewdata.ordnum = tblinventory.ordnum

but your suggestion worked great..

Brook

ok, it sounds like you want to update existing records in tblinventory,
using data from tblnewdata. FIRST, back up your database. next, open a new
query in design view and add both tables. link the two tables on the ordnum
fields. add the fields you want to update to the design grid, pulling them
from tblinventory. on the menu bar, click Query | Update Query to change the
query to an Update query. on the Update To: line, for each field in the
query grid, enter the table name and the corresponding field, such as

[tblnewdata].[colour1]

in the colour1 column in the grid, etc.

after you've set the Update To: line for each column in the query grid, run
the query by clicking the exclamation point (!) button on the toolbar.

hth

Hello Tina,

Thanks for the response.. here is what I have:

I have in a tbl with new data (tblnewdata):

ordnum colour1 colour 2 - 14 colour 14 - 19


in an existing table called tblinventory I have the same fields as above
along with about 20 other fields,

What I want to do is perform an update from my new table to my old table if
the ordnum match between tblnewdata & tblinventory, then update colour 1 from
tblnewdata with colour1 in tblinventory, update colour2-14 from tblnewdata
with colour 2 - 14 in tblinventory, and update colour 14 - 19 from tblnewdata
with colour 14 - 19 in tblinventory.

hope this clears things a little..

Brook

 

[tina]

you're welcome

Hello Tina,

Thank you very much... that was what I wasn't doing... I wasn't linking the
tables, I was trying to put this in the criteria field:

tblnewdata.ordnum = tblinventory.ordnum

but your suggestion worked great..

Brook

ok, it sounds like you want to update existing records in tblinventory,
using data from tblnewdata. FIRST, back up your database. next, open a new
query in design view and add both tables. link the two tables on the ordnum
fields. add the fields you want to update to the design grid, pulling them
from tblinventory. on the menu bar, click Query | Update Query to change the
query to an Update query. on the Update To: line, for each field in the
query grid, enter the table name and the corresponding field, such as

[tblnewdata].[colour1]

in the colour1 column in the grid, etc.

after you've set the Update To: line for each column in the query grid, run
the query by clicking the exclamation point (!) button on the toolbar.

hth

Hello Tina,

Thanks for the response.. here is what I have:

I have in a tbl with new data (tblnewdata):

ordnum colour1 colour 2 - 14 colour 14 - 19


in an existing table called tblinventory I have the same fields as above
along with about 20 other fields,

What I want to do is perform an update from my new table to my old

table
if

the ordnum match between tblnewdata & tblinventory, then update colour

1
from

tblnewdata with colour1 in tblinventory, update colour2-14 from tblnewdata
with colour 2 - 14 in tblinventory, and update colour 14 - 19 from tblnewdata
with colour 14 - 19 in tblinventory.

hope this clears things a little..

Brook

:

you can't *update* a record in a table using an Append query. Append queries
*add* one or more new records to a table. Update queries modify data

in
one

or more existing records in a table. from your post, it's not clear whether
you want to add a record or modify an existing record.

hth


good day all,


I have an append query that I have set up, but I want it to append
records
based on a criteria... so if ordnum from tlb1 = ordnum appendquery then
update that record with the corresponding data? how do I code this

in
my

append query to match the criteria before the append?

Thanks in advance..

Brook