Synchronising Tables

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Michael Thomas
    #1

    Synchronising Tables

    Hi everyone

    Having a slight problem synchronising tables in MS Access 2002.

    I have two tables:

    Products (StockCode*, Description, CostPrice, SellingPrice)

    and

    ProductsWorkTab le(StockCode*, Description, CostPrice, SellingPrice)

    Which contain a list of products kept by our company.

    ProductsWorkTab le 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
    ProductsWorkTab le and remove any products that are NOT in ProductsWorkTab le
    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 ProductsWorkTab le.

    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.StockC ode == ProductsWorkTab le.StockCode
    THEN {
    IF
    (Products.CostP rice NOTEQUALTO ProductsWorkTab le.CostPrice) THEN
    (Products.CostP rice = ProductsWorkTab le.CostPrice)
    AND/OR
    IF
    (Products.Selli ngPrice NOTEQUALTO ProductsWorkTab le.SellingPrice ) THEN
    (Products.Selli ngPrice = ProductsWorkTab le.SellingPrice )
    }
    Basically if the fields in products don't match those in ProductsWOrkTab le
    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





    Tags: None
    • Salad
      #2
      Re: Synchronising Tables

      Michael Thomas wrote:
      [color=blue]
      > Hi everyone
      >
      > Having a slight problem synchronising tables in MS Access 2002.
      >
      > I have two tables:
      >
      > Products (StockCode*, Description, CostPrice, SellingPrice)
      >
      > and
      >
      > ProductsWorkTab le(StockCode*, Description, CostPrice, SellingPrice)
      >
      > Which contain a list of products kept by our company.
      >
      > ProductsWorkTab le 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
      > ProductsWorkTab le and remove any products that are NOT in ProductsWorkTab le
      > 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 ProductsWorkTab le.
      >
      > 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.StockC ode == ProductsWorkTab le.StockCode
      > THEN {
      > IF
      > (Products.CostP rice NOTEQUALTO ProductsWorkTab le.CostPrice) THEN
      > (Products.CostP rice = ProductsWorkTab le.CostPrice)
      > AND/OR
      > IF
      > (Products.Selli ngPrice NOTEQUALTO ProductsWorkTab le.SellingPrice ) THEN
      > (Products.Selli ngPrice = ProductsWorkTab le.SellingPrice )
      > }
      > Basically if the fields in products don't match those in ProductsWOrkTab le
      > 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[/color]

      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)

        Comment

        Previous template Next
        Working...