1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help with Access 97 database

Discussion in 'Business Applications' started by prideouts, Dec 3, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. prideouts

    prideouts Thread Starter

    Joined:
    Nov 9, 2001
    Messages:
    55
    What I have done is combine the Order Entry, Inventory Control and Customer Management databases together in Access 97 and I’m trying to get them to work together to be able to update when inventory comes in and goes out of my business.

    I’m trying to figure out how to complete the actual receipt of merchandise on a purchase order in the database. I want to put in the current date or earlier (I may use yesterday's date when I finally get around to entering data) as my variables for the timing to update the inventory transaction table (units received and units on hand fields).

    Can you give me the operators and operands in the expression builder and the complete proper syntax that I would need to tell an event has happened and to update the table.

    Thank you in advance - you guys are so good!!
     
  2. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Whew. That's a lot of information, and none too clear, I'm afraid. Please give us exact details of (1) what you have already, and (2) what, exactly, you want to do, and please remember that the more specific and limited your question(s) is/are in scope, the more likely you are to get a helpful response.
     
  3. prideouts

    prideouts Thread Starter

    Joined:
    Nov 9, 2001
    Messages:
    55
    Thanks, that explains why I didn't get any more responses.

    Okay, in Access there are three template databases that I created and then pulled tables, queries, forms and reports into one main database called Order Entry. I'm trying to combine the Inventory aspects, Customer aspects and Order aspects all into on database. The problem I'm having is trying to keep a running total of inventory because I can't figure out how to create relationships between some of the tables to make them work together. For instance, the inventory table and products table have a form to put inventory into a business and the order details table has a form for taking inventory out of a business. I can't get them to work together and wondered if anyone out there might have some prior knowledge of this type of manipulation of tables.

    Thanks again for any help you could give.

    If it would help, I could email the database and let you look at it.
     
  4. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Sorry, but you're still not very clear...are you trying to automate the the subtraction of inventory when an order is processed? In that case you would probably need to resort to coding and it could get complicated.

    The two forms/tables are linked via the inventory item's name or other unique key (possibly part number). If you are doing this once a week, then you would need code to add up the specific parts for a given time period from the order details table, take that total and subtract it from the latest inventory record in the inventory table.

    If you process every time an order is entered, the relationship remains the same, as does the code (for the most part) and the inventory would be more accurate, but you would end up with a lot more inventory records and I'd worry about an Access database getting very big, very fast.

    Either way, you are looking at a somewhat complicated process. If you're new at Access, this may be a bit too complex.

    Hopefully someone else out there has some good ideas.

    ***Just got an idea: if the above seems too complicated, a bit more manual process would be to create a query for your orders table that totals the different parts and prompts for a period of time and then the fields for the inventory table allowing you to create a new record for the new inventory numbers.

    Hope this helps.
     
  5. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Shooting from the hip, here's my thoughts:

    1. Products tbl should always just be a lookup, something that links a code/ID number to a name, description, characteristics, those sorts of things. Should never contain inventory figures.

    2. I believe that incoming/outcoming orders should go into the same table. Not everyone agrees with me on this, but I believe that they involve substantially the same kinds of ideas, i.e. the same kind of data, and are a case of +/- only. This makes tracking inventory phenomenally easy.

    3. Regardless of whether you put them in one table or two, each order should be split into a master table--one record per order--and a line-item table--one record per product, with a quantity. So if you're separating the two, you should have four tables, and your current inventory comes from summing product quantities in inventory and subtracting orders; at least three queries to put that together.

    I don't know Access templates, as I don't use them, but I'd agree that I'd need more details before having anything further to say. What's wrong with running inventory totals? And what relationships would you like to create that you cannot create now?
     
  6. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    Downwitchyobadself (god, I love that name!), on number two it would all depend on what sort of history you need. If you need to keep track of any inventory and take a "snapshot" of it, a separate table would probably be the way to do. If no such history is required, then just updating a qty field in the product record is all you would probably need.

    The big issue here is that all these tables already exist, so it's hard to speculate how everything would/could fit together.
     
  7. prideouts

    prideouts Thread Starter

    Joined:
    Nov 9, 2001
    Messages:
    55
    MustBNuts and Dowwitchyobadself, you have both been a big help, but I still have some questions.

    1. In answer to MustBNuts question about trying to automate the subtraction of inventory when an order is processed, the answer is YES! That is what I'm trying to do. I don't necessarily need to keep a lot of history, but that would be helpful if possible.

    2. Do you know of any examples of syntax to put into an update query to automatically update the table when an order is placed on the customer order form. I'm trying to put a command button that would run the macro for an update query to the inventory table. I realized from your information that I was trying to use the products table to do this and am now working on changing that. Please help with syntax, if you can.

    I know it's probably a more complicated process than I orginally thought, but as this database has progressed the person I'm doing it for has changed more and more - thinking it's easy to just change things. And probably they are if I knew more about Access.

    Thanks again - if you could lead me to some examples of syntax for the query to update inventory table that would be great.
     
  8. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    You might want to check what MS site offers in the way of templates, etc., to get ideas.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/184439

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice