Synchronising Tables

[Michael Thomas]

Hi everyone

Having a slight problem synchronising tables in MS Access 2002.

I have two tables:

Products (StockCode*, Description, CostPrice, SellingPrice)

and

ProductsWorkTable(StockCode*, Description, CostPrice, SellingPrice)

Which contain a list of products kept by our company.

ProductsWorkTable is imported from a CSV file and contains the (most) up to
date list of products which may include new products, may have had products
removed (records added or deleted) and may have had the cost price and/or
selling price fields of certain records changed.

What I need to do is make Products add any new records found in
ProductsWorkTable and remove any products that are NOT in ProductsWorkTable
but still in Products. This I can do, it is running smoothly.

I also, however, need the CostPrice and SellingPrice of all records in
Products to match the CostPRice and SellingPrice fields of the appropriate
(changed) records in ProductsWorkTable.

As I said, the adding and removing is fine, but the changing of fields is a
mystery to me.

The basic idea is:

IF
Products.StockCode == ProductsWorkTable.StockCode
THEN {
IF
(Products.CostPrice NOTEQUALTO ProductsWorkTable.CostPrice) THEN
(Products.CostPrice = ProductsWorkTable.CostPrice)
AND/OR
IF
(Products.SellingPrice NOTEQUALTO ProductsWorkTable.SellingPrice) THEN
(Products.SellingPrice = ProductsWorkTable.SellingPrice)
}
Basically if the fields in products don't match those in ProductsWOrkTable
set them to match.

Is there any way for me to do this with an SQL query or even with VBA? If
VBA needed is there a quick and easy way? The pseudocode isn't that much
and for me to learn VBA in it's entirety may take me a while...

Sorry for the long post and thank you in advance.

Michael Thomas

[Salad]

Michael Thomas wrote:

Hi everyone

Having a slight problem synchronising tables in MS Access 2002.

I have two tables:

Products (StockCode*, Description, CostPrice, SellingPrice)

and

ProductsWorkTable(StockCode*, Description, CostPrice, SellingPrice)

Which contain a list of products kept by our company.

ProductsWorkTable is imported from a CSV file and contains the (most) up to
date list of products which may include new products, may have had products
removed (records added or deleted) and may have had the cost price and/or
selling price fields of certain records changed.

What I need to do is make Products add any new records found in
ProductsWorkTable and remove any products that are NOT in ProductsWorkTable
but still in Products. This I can do, it is running smoothly.

I also, however, need the CostPrice and SellingPrice of all records in
Products to match the CostPRice and SellingPrice fields of the appropriate
(changed) records in ProductsWorkTable.

As I said, the adding and removing is fine, but the changing of fields is a
mystery to me.

The basic idea is:

IF
Products.StockCode == ProductsWorkTable.StockCode
THEN {
IF
(Products.CostPrice NOTEQUALTO ProductsWorkTable.CostPrice) THEN
(Products.CostPrice = ProductsWorkTable.CostPrice)
AND/OR
IF
(Products.SellingPrice NOTEQUALTO ProductsWorkTable.SellingPrice) THEN
(Products.SellingPrice = ProductsWorkTable.SellingPrice)
}
Basically if the fields in products don't match those in ProductsWOrkTable
set them to match.

Is there any way for me to do this with an SQL query or even with VBA? If
VBA needed is there a quick and easy way? The pseudocode isn't that much
and for me to learn VBA in it's entirety may take me a while...

Sorry for the long post and thank you in advance.

Michael Thomas

I would create an Unmatched record query...shows all records from Work
not in Products. Then create an append query to append all records
found in the unMatched query. This will synch the products.

Next create a query that links Work to Products. Drag the Products
price to the column. In the criteria, enter <>Work!Price. Then set the
query to Update. And enter Work!Price in the update row. (Please
substitute with your table/field names)