Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Unanswered: Using table in query

    I have a table of 'updates' i would like to do. This table has been sent by another company to update their information in my database.

    The table has: A Product ID, Product Sale Price, Product Amount;

    The company sents this update to refresh our records with the right sales price and amounts for each unique product ID. Sometimes, the table might have 3 entries; other times, insanely huge.

    How do you automate this so that it will update my database for those specific product IDs? Will it require code modules or could i use update queries?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Using table in query

    Originally posted by Jerrie
    I have a table of 'updates' i would like to do. This table has been sent by another company to update their information in my database.

    The table has: A Product ID, Product Sale Price, Product Amount;

    The company sents this update to refresh our records with the right sales price and amounts for each unique product ID. Sometimes, the table might have 3 entries; other times, insanely huge.

    How do you automate this so that it will update my database for those specific product IDs? Will it require code modules or could i use update queries?
    You could do it either way ... I would use code myself (rather obvious) ... I don't know your comfort level with code ...

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Re: Using table in query

    Is there any way to do it with queries? I know C++; i dont know VB though but code is ok with me. Does this require the CLookUp function?

    Unless queries cannot absolutely be done, then could u show me the code or guide me in the right direction?

  4. #4
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    You should be able to do it with an update query. You can run your query from design view (hit the view button on toolbar) without actually updating the records while you test your results.

    Select what records you want (such as product ids which occur the update table), then based on this fill in the update table and record for those items to update (Price).

    As long as it is a one-to-one join and as long as you are not looking to archive old prices, this should be very easy (comparatively). Many Access tuturials in fact use this as an example for update queries.

    if the new table gives a wholesale price you can also recalc the retail if it is a set markup (or some formula). This should be easy from an update query, unless your formula to determiine the markup is complex.

    if you don't have an access tutorial or manual try a web search on access update queries . .. or search the archives here.

    one possibility comes to mind: http://www.helenfeddema.com/access.htm


    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •