Help! append multiple data with update Query

[diaz_proces]

Hello everybody Im a newbie and I have a database Issue..I tried to find information to solve for days but I didnt manage to have good results.

In access Im working with two tables, this is my setup

tableA.documentnr
tableA.revison

tableB.documentnr
tableB.revision

Both tables are filled with data, Table B contains the same kind of data as table A, But tableA has documentnumbers with different revisions (for example revision a,b,c, for each revision a seperate row). Table B might have an identical document, but just one revision (like revision a).

Now I like to append the data of tableA to tableB, except if a revision is similiar to a revision in table A. (There is more metadata involved, but I will do it step by step)

Im not working with primarykey data, becayse in the end result table B will also have multiple (identical)document numbers with different revisions on different rows.

I tried to use the update query but it doenst append the documentnumbers where the revision is not present in table B I attached a image of the tables..I hope somebody can help me!!

 

[michaeljryan78]

I see that "Is not Null" is in quotes. Remove the quotes for a result. in the Revision Criteria Put <> [PMDL_MR757].[revMR]

 

[diaz_proces]

Doesnt work..It shows everything what I have in TableB what is related to table A..

I dont want the docnr's with corresponding rev numbers between table A and B in the results..

The SQL code looks like this now

SELECT export_MR_nodoubles.Name, export_MR_nodoubles.Revision, PMDL_MR757.[Document number], PMDL_MR757.revMR
FROM export_MR_nodoubles LEFT JOIN PMDL_MR757 ON (export_MR_nodoubles.Revision = PMDL_MR757.revMR) AND (export_MR_nodoubles.Name = PMDL_MR757.[Document number])
WHERE (((export_MR_nodoubles.Revision)=[PMDL_MR757].[revMR]) AND ((PMDL_MR757.revMR) Is Not Null));

 

[michaeljryan78]

Sorry. I read the post again. Just to be clear you want to add ALL records from Table A to Table B except where the revision numbers are the same?

 

[diaz_proces]

Sorry. I read the post again. Just to be clear you want to add ALL records from Table A to Table B except where the revision numbers are the same?

Yes exactly!

note that table A have multiple doc nr, but different revision. Table B might already have a docnr with a revision which is present is TableA.

Maybe my explanation isnt that clear

 

[diaz_proces]

I have managed to create a column with unique key numbers (document+rev).

But the update query still doesnt append the records which are not in "table B yet"

 

[JHB]

But the update query still doesnt append the records which are not in "table B yet"

An update query can't append records, it can "only" change the values in fields.
To append records use an append query! Show your query.

 

[JANR]

An update query can't append records, it can "only" change the values in fields.

Not true, you can use an update query with an outer join to append records.

ex 1: (this will append new records only)

UPDATE Tbl1 LEFT JOIN Tbl2 ON Tbl1.ProductName = Tbl2.ProductName SET Tbl2.ProductName = [Tbl1].[ProductName], Tbl2.Price = [Tbl1].[Price]
WHERE (((Tbl2.ProductName) Is Null));

ex2: (this will update old records and append new records)

UPDATE Tbl1 LEFT JOIN Tbl2 ON Tbl1.ProductName = Tbl2.ProductName SET Tbl2.ProductName = [Tbl1].[ProductName], Tbl2.Price = [Tbl1].[Price];

see attached mdb


JR

 

[diaz_proces]

Hello everybody,

I have solved the problem.

What was missing in the query was the column with unqique values (whis is necessery to make this function work)

I used the unique values (document+rev nr) to make the outer join, but didnt select it to show in the results. In that case, the query won't append the values which are not in the destination table yet.

After adding the unique values, the query does append the one which are missing.

In the end, the sql looks like this now:

UPDATE tableA LEFT tableB ON tableA.UniqueValues = tableB.UniqueValues SET tableB.UniqueValues= tableA![UniqueValues];

Thanks for the replys and help! This threat can be closed now.

 

[JHB]

Not true, you can use an update query with an outer join to append records.

You're right!