Results 1 to 7 of 7
  1. #1
    mikej2009 is offline Novice
    Windows 7 Access 2007
    Join Date
    Apr 2010
    Posts
    5

    auto fill data from other datasheet for my business! HELP

    okay I don't know how to explain this well so bare with me and i am not to familiar at all with access so explain it to me like a 3rd grader!

    I originally thought this would be an excel problem but someone advised me "excel is a giant calculator, use access".

    I run a small business and i am trying to cut many hours per week. One thing i am trying to do is with inventory management, i run a website and sell about 400-500 unique products on there, the main distributor i go through has about 20,000+ items.

    They send out a excel workbook with all the info once a week. I use this to update my prices, descriptions, "instock", etc.

    every sunday i have to go into my sheet, look up the products in their sheet and manually update the prices.

    I need to idenity data by the product name and update the products fields according to the distributor sheet.



    for example, product A of my sheet is 1499.99, and the distributor marked it down to 1388.88, so i need my sheet to auto update from the distributors sheet.

    I also need to be able to save this as a ".csv" file to upload to my site.

    and if there is a solution for that, you don't know how happy that will make me, and if that is possible, i would like to make it on another field to where a distributor field that = "no" would fill in my filed a s "0", because dist. field of "no" means it is not in stock, but my website cart reads "0" for not in stock so right now i have to go in and match up products to see if they are in stock and manually type it in.

    hopefully this wasn't to confusing.

    better yet, if there is software/addon/anything that will make this easier on me i would love to use it instead. or i am willing to pay to get it set up if it is over my head on what i need to do.

    thanks

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    NOTE: backup your tables (make a copy) before running any update/delete queries in case stuff goes wrong. there is no going back from these.

    Step 1. have a table with your current price. we will call this tblInventory
    Step 2. import the excel file with the vendor's items weekly into another table. lets call it tblProducts
    Step 3. make sure there is a unique identifier (most likely ItemNo) that matches in both tables.
    Step 4. create a query. put both tables into the query. join them by their ItemNo.
    Step 5. double click on the ItemNo field from your tblInventory window.
    Step 6. run it to make sure it works
    Step 7. change the view back to design view
    Step 5. make it an update query right clicking in the table area, and selecting it from query type.
    Step 6. Add the Price field from your tblInventory into the query
    Step 7. you will notice a "update to" section in your query show up. in the "update to" for the Price from tblInventory, make it tblProducts.Price
    Step 8. Hit run and it should work
    Step 9. Open up the new table
    Step 10. Tools > Office Tools > analyze in Excel
    Step 11. (in the excel doc that opens) File > Save As > prices.csv

    I dont have Access open so this is all from memory. syntax may be off but I think its right.

    More info at http://www.databasedev.co.uk/update_query.html

    If that solves your issue, please mark thread solved.

    Thanks.

  3. #3
    mikej2009 is offline Novice
    Windows 7 Access 2007
    Join Date
    Apr 2010
    Posts
    5
    Quote Originally Posted by TheShabz View Post
    NOTE: backup your tables (make a copy) before running any update/delete queries in case stuff goes wrong. there is no going back from these.

    Step 1. have a table with your current price. we will call this tblInventory
    Step 2. import the excel file with the vendor's items weekly into another table. lets call it tblProducts
    Step 3. make sure there is a unique identifier (most likely ItemNo) that matches in both tables.
    Step 4. create a query. put both tables into the query. join them by their ItemNo.
    Step 5. double click on the ItemNo field from your tblInventory window.
    Step 6. run it to make sure it works
    Step 7. change the view back to design view
    Step 5. make it an update query right clicking in the table area, and selecting it from query type.
    Step 6. Add the Price field from your tblInventory into the query
    Step 7. you will notice a "update to" section in your query show up. in the "update to" for the Price from tblInventory, make it tblProducts.Price
    Step 8. Hit run and it should work
    Step 9. Open up the new table
    Step 10. Tools > Office Tools > analyze in Excel
    Step 11. (in the excel doc that opens) File > Save As > prices.csv

    I dont have Access open so this is all from memory. syntax may be off but I think its right.

    More info at http://www.databasedev.co.uk/update_query.html

    If that solves your issue, please mark thread solved.

    Thanks.
    okay thanks for reply, going to try it tomorrow, getting late now.

    couple questions. do i have to go in and match up each individual product OR can i have it match up everything in a certain column. it is going to match up by product name. which is column b, so can i have access go

    "match unique id in column b to dist. sheet' and auto update that row?"

    and can this be something to where i plug in the new data sheet from the dist. every week? same file name of course?

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    You will have your main tblInventory that is always in your access db. your tblProducts (again, name these whatever you want) will be the excel file that you will import weekly. If you keep the name the same, all you will have to do is run the query weekly to update your prices in tblInventory.

    You dont have to mess with each individual product. you are merely matching field names (column headers). ItemNo to ItemNo (or whatever you decide to call them)
    the query window should look like this example i pulled off the web. just instead of Dept you have ItemNo.


  5. #5
    mikej2009 is offline Novice
    Windows 7 Access 2007
    Join Date
    Apr 2010
    Posts
    5
    Quote Originally Posted by TheShabz View Post
    You will have your main tblInventory that is always in your access db. your tblProducts (again, name these whatever you want) will be the excel file that you will import weekly. If you keep the name the same, all you will have to do is run the query weekly to update your prices in tblInventory.

    You dont have to mess with each individual product. you are merely matching field names (column headers). ItemNo to ItemNo (or whatever you decide to call them)
    the query window should look like this example i pulled off the web. just instead of Dept you have ItemNo.

    okay one last question if you don't mind, and thanks also, but it might be right in front of me and already answered, but how would i have it only update certain cells on that row.

    lets say my data sheet has 10 columns, but only 4 need updated from distributor as the others either aren't on the distributor or they don't need updating, how would i set it to where those 4 fields are only updated by my unique product name?

    thanks again, i will reply back tomorrow with my success or failure!

    later

  6. #6
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    That is the purpose of the join in the query. It will only update records in your tblInventory which has ItemNo's that match the ItemNo's in tblProducts. It's not that it only updates the ones that are changed, but it updates everything. If there is a change it will update from 19.98 to 29.98. if there is no change, it will update from 19.98 to 19.98.

    basically the logic behind the query is... Take all the prices from tblInventory which have an ItemNo that matches the ones in tblProducts and replace them with the prices in tblProducts.

  7. #7
    mikej2009 is offline Novice
    Windows 7 Access 2007
    Join Date
    Apr 2010
    Posts
    5
    need help! i know you explained, but it isn't working correctly, can you break it down one more step?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Auto fill a field from multiple lookup
    By shahemaan in forum Forms
    Replies: 4
    Last Post: 06-13-2014, 04:00 PM
  2. Auto-fill in datasheet
    By Terence in forum Database Design
    Replies: 2
    Last Post: 03-18-2010, 03:42 PM
  3. Auto Fill-In Capability?
    By bbarrene in forum Access
    Replies: 3
    Last Post: 01-15-2010, 08:35 AM
  4. Auto-fill field not saving to table
    By aaid in forum Forms
    Replies: 1
    Last Post: 12-18-2009, 05:34 PM
  5. Auto fill a table?
    By newtoAccess in forum Access
    Replies: 3
    Last Post: 11-21-2009, 08:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums