Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Other Records (Access 97 sr2)

    I'm kind of new at doing anything fancy with Access97 so welcome me onboard and then HELP! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    I have been doing this in Excel97 but it is getting to large to be managable. In Excel I have a series of worksheets each representing a single product inventory item (the sheetname IS the product id name).

    Each record includes (among other things) two very important fields called STARTNUM and STOPNUM. These represent start and stop sequential serial numbers for inventory that has been used. Using these 2 fields I need to calculate 2 new fields.

    The first calculated field is called QTY_USED which is simple to do in both Excel and Access:
    QTY_USED = ( STOPNUM - STARTNUM + 1 )

    The second calculated field is call QTY_SKIPPED and it is my problem...

    In Excel this was easy:
    QTY_SKIPPED = ( currentrecord.STARTNUM - previousrecord.STOPNUM - 1 )

    But how do you refer to the field of a previous record for such a calculation?

    In Excel I have a little macro that clears the QTY_SKIPPED column, sorts the data on the spreadsheet, and then fills in the QTY_SKIPPED formula for every active row. This is necessary because the product is not always used in sequence between orders (but is always used in sequence as part of a single order).

    Any help on how to do this would be appreciated. Thanks.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Other Records (Access 97 sr2)

    You really can't do what you want in Access (or any relational database for that matter), because there is really no such thing as the "previous record"! That concept is for spreadsheets. In Access, you have data written anywhere in a table, which you can then view in any sequence you want.

    The only way you might be able to calc this "missing" number is to include a subquery that returns the value of this ending number for all stock items with an ID < the ID of the record you are working on. ORDER BY Descending, using SELECT TOP1.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Other Records (Access 97 sr2)

    I saw in another post where there was a way to do this in a report but part of what the spreadsheet does is flag when a QTY_SKIPPED appears as a negative number (ie: data error).

    Maybe I am getting in over my head here, but could a table be "exported" (not sure if this is the right terminology) to a "temporary worksheet" and the Excel formulas used there as part of the data entry process?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Other Records (Access 97 sr2)

    I think you might be able to do something similar with a query and using self-joins to your table. To actually calculate the skipped quantity would be a bit of a challenge - you would probably have to write some VBA to calculate the quantity and update each record with the quantity skipped. There are some fundamental questions that need to be answered however, before you move this to Access.
    <UL><LI>What does each record in Access represent - is it a shipment, an order, a partial ship, or simply a withdrawl from inventory of a certain number of parts?
    <LI>What is your objective in calculating QTY_SKIPPED - are you using it subsequently to calculate the total quantity on hand, or is it used for some other purpose?
    <LI>How do you handle the addition of new stock - in otherwords you need some sort of indication of the highest serial number available in order to calculate the quantity on hand?
    <LI>How do you reconcile the results of a physical inventory with the Excel inventory?[/list]Inventory system in general are quite tricky. Most end up being implemented as a master record which contains info like the description of the item, who makes it, and that kind of thing, and then a transaction record which either removes or adds inventory from the stock on hand. You may want to think about that kind of thing and the possibilities that a tool like Access offers. First it is multi-user, second forms are easier to build and use in general (some of my Excel compatriots may grouse about that, but my opinion), and if you need to have 500K or 1M entries, you can easily do it. Hope this helps.
    Wendell

  5. #5
    Lounger
    Join Date
    Mar 2002
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Other Records (Access 97 sr2)

    To answer your questions:

    The purpose of this database is to inventory SERIAL NUMBERS, not actual product. We need to know which client has possession of certain serial numbers and when they got them.

    Each entry represents a shipment/allocation of a contiguous range of serial numbers.

    QTY_SKIPPED is used to identify unallocated serial numbers (or numbers which may have been used by another department and need to be reconciled).

    New stock is irrelevant in this application. Serial numbers are always allocated to new manufacturing runs starting where the last run ended +1.

    Again, physical inventory is irrelevant in this application. We need this database to tell us when a client calls in that the serial number they are refering to is indeed a valid one for THAT client.

    One of the roles the spreadsheet has been doing is acting as a QC trap for typos on shipment logs. If a range is overlapping any previous logged range the spreadsheet goes redflag and we research both shipment logs to reconcile. Part of that involves the QTY_SKIPPED which must never be negative. Other similar calculations are done on other fields to monitor overlapping ranges but I figured if someone could show me how to do the simplest one then I could figure out the rest.

    I have a feeling this may need to be a redesigned excel system after all but I hate to give up on having the speed and filesharing that Access offers vs an over-loaded Excel database.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Other Records (Access 97 sr2)

    Are you recording these blocks as "from" "to" fields or as individual records?
    If you are entering individual serial numbers Access will automatically prevent people inputting non-unique data for you if the record is set to index - Yes (No Duplicates).

    If you want to know how many numbers are unused you could use a record count against last serial number to find out.

    If you are putting the numbers in as blocks you could probably use code to validate the numbers and add them to a table as individual records for you


    HTH

    Peter

Posting Permissions

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