Simple append & update queries

[ReetP]

Unfortunately coding is not my forte and I wondered if someone can assist me / hold my hand through this ? Have read all over the place but cannot see the wood for the trees.

I have two linked tables. One is ODBC to a MySQL Db and the other ODBC to Sage. I could probably do this directly between the two databases, but that would require some direct SQL coding, so I thought it might be easier to use Access to bridge. Simply, all I want to be able to do is :

1. Populate the MySQl Db from the Sage one - I seem to have managed this with a simple append query

2. Update any changes to field data from the Sage on to the MySQL one.
Two points with this one - the existing data may change so an update query is required, and new records may be added which would require an append. Not sure whether this can be combined so it does it all in one go.

I can view all the data I need quite happily from either table. The MySQL Db has a primary key set but the Sage one does not, and I cannot change that. I don't know whether that makes any difference

I have managed to get the update query to work once, but lost the settings and cannot recreate it. Also, I cannot get the append to add new records that appear.

Any help would be much appreciated as this has been driving me nuts for weeks !

Tables :

From Sage :

Table name : Stock
Fields : Stock Code (unique), Desciption, Web Description, Web Location, Web Pulish (numeric 0 or 1)

From MySQL :

Table name : SQL_STOCK
Fields : ID (Primary key), Stock Code (unique), Desciption, Web Description, Web Location, Web Pulish (numeric 0 or 1)

 

[ecniv]

1) you did it but it will probably give you dulicates.
2) as far as I know, you have to run two queries, both have to have linked tables so you can find all those that are in one and not in the other.

You said Sage didn't have a pk set, but there is a unique record?

Have a read up on Left joins (Outer joins) as you can link the source to the dest
-if the dest is not null, then update (which you should run first)
-if the dest is null then insert them.

Post up your Sql statement once you've investigated a little more.


Vince

 

[myber]

Simple update & append query - response

For the append - 1 - use MSAccess to create a 'find duplicates' query between your MYSql and Sage DBs, using the primarykey.field. 2- Modify this query in the SQL mode to show 'NOT EQUAL - <>' in the where statement. 3- Copy your original append to a new query and in the 'where' portion of this copy - insert (copy & paste) the query from #2, enclosed in parans. Resulting logic will be append mysql from sage where (query: mysql.primarykey <> sage.primarykey)