+ Reply to Thread
Results 1 to 3 of 3

Optimally tracking remaining inventory in Access DB dynamically from Excel front end

  1. #1
    Registered User
    Join Date
    07-29-2019
    Location
    NC, USA
    MS-Off Ver
    365 64bit
    Posts
    46

    Optimally tracking remaining inventory in Access DB dynamically from Excel front end

    I have an Excel sheet that handles data input and utilizes an Access database to update materials available to track what materials are used. The relational database allows me to track where materials originated (some materials are made of lots of other materials) and its going to solve a lot of issues I've had with tracking and determining sources that are causing issues. Each time material(s) are used in the front end, a project record is created in the database which includes the material used as well as other data related to the project.

    I'm trying to decide the best way to track that material has been used and can think of a couple of ways though I'm not sure which is best or if there is a better way.

    Method 1: I create a delimited list in a field of the material's record in which unique IDs generated from the Excel front end are listed as material is used and each unique ID can be counted towards material used, that way duplicates will be avoided when the excel files goes to update by checking its unique ID against the list. This is what I am thinking is the best solution, though would end up adding a lot of extra data to record a unique IDs to track each "1"

    Method 2: A query is run against all the records created by the front end and the materials listed in each record are tallied and their total subtracted from starting amount for each material (this seems like it will become inefficient when the total number of records gets large)

    I feel like there must be a better way to do this but I'm coming up blank.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,576

    Re: Optimally tracking remaining inventory in Access DB dynamically from Excel front end

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    07-29-2019
    Location
    NC, USA
    MS-Off Ver
    365 64bit
    Posts
    46

    Re: Optimally tracking remaining inventory in Access DB dynamically from Excel front end

    I'm not sure that my question necessitates an example workbook. It's really more of a conceptual question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 10-23-2018, 12:54 PM
  2. Using Excel as a Front End to Access
    By Newie2014 in forum Excel General
    Replies: 2
    Last Post: 08-28-2014, 08:56 AM
  3. Excel as front-end for Access or MYSQL
    By mbcebrian in forum Excel General
    Replies: 3
    Last Post: 02-07-2013, 04:32 PM
  4. [SOLVED] Need remaining average inventory price after partial inventory is sold
    By Akano in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2012, 04:12 AM
  5. Inventory Tracking - Excel or Access
    By OtterBoxing in forum Excel General
    Replies: 2
    Last Post: 06-23-2012, 03:30 PM
  6. Use Excel as front end to Access
    By gssstuff@hotmail.com in forum Excel General
    Replies: 1
    Last Post: 11-29-2005, 12:25 AM
  7. [SOLVED] excel front end to access
    By Steve Peterson in forum Excel General
    Replies: 1
    Last Post: 01-20-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1