Results 1 to 2 of 2
  1. #1
    BobLoveland is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2014
    Posts
    1

    Question Inventory Back Order Totals - Access + Inventory.xls & BackOrders.xls

    Hello everyone,

    I am creating a inventory database solely for the purpose of creating custom reports for our office and need help calculating how many of an item is on backorder. It should not be so difficult to do, but my problem is we download our inventory and backorder lists as two separate excel files.

    DETAILS:

    Files Involved:
    1. Inventory-Reports.accdb (Currently Access 2007 file) This is the file I created that links to the inventory.xls file and backorders.xls. Because I download these files separately from the server whatever method we use to calculate totals, must link to these external files to get the data.
    2. Inventory.xls - this file contains all the data on our inventory including ItemNumber and a variety of other fields. This file is downloaded and overwritten each time we need updated reports.
    3. Backorders.xls- this file is a list of items we have currently on back order in our system. This file also contains ItemNumber which should be common relationship between the two files.


    Here is the problem, the data in Backorders.xls does not have the quantities totaled for each ItemNumber. Instead, backorders.xls lists each part number, who ordered it, and how many they wanted, so for PartNumber 0202-072P Paper Mache ART PASTE 2 OZ I have 6 separate customers listed with varying quantities that need to be totaled for item 0202-072.



    What I need to do and don't know how, is to extract all of 0202-072 and get a total and supply that information to access for reports. So I guess the basic question is, how do i take backorders.xls, group the items by part number, and total each one of the different items back-ordered in inventory? If we have to create a third excel file to calculate the totals and link to it, that is fine too.


    Thank you so much for your help,

    Bob Loveland

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Attach (link) both excel sheets as tables. (use the same generic name everytime)

    inventory.xlsx
    backorder.xlsx

    Attach them once. (then just overwrite new files over these existing generic names for import)
    then you can ,build a query to sum total query, then import THAT query to where it goes.
    or just run a query that imports the data straight in, no summs.

    So the method would be
    1. save both sheets to the import names
    2. run import macro.
    done.

    in the macro would be your queries that either sum or not sum before appending to the data tables.

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

Similar Threads

  1. Updating Inventory Totals
    By Bridget in forum Access
    Replies: 1
    Last Post: 10-10-2013, 04:01 PM
  2. Replies: 1
    Last Post: 02-21-2013, 12:18 AM
  3. Products Inventory Dilema,Add To Inventory
    By burrina in forum Forms
    Replies: 3
    Last Post: 12-02-2012, 12:10 PM
  4. Managing Inventory in Access
    By bushkanaka86 in forum Access
    Replies: 1
    Last Post: 11-25-2011, 08:31 PM
  5. maintaining inventory in access
    By mmarwaha in forum Access
    Replies: 1
    Last Post: 01-16-2011, 06:23 AM

Tags for this Thread

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