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.

Access problems

Discussion in 'Business Applications' started by BetaGrl, Jul 17, 2006.

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

    BetaGrl Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    11
    I'm creating a DB for work to track product serial numbers and everything that goes along with them.

    So far I have one main table "Build Orders" and a "Serial Number" table.

    In Build Orders I have the following fields: Build Order (key); Amt; Product; and others that aren't related to my question.

    The Serial Number table I have the following fields: Serial Number(key); Product; Build Order; and others that aren't related to my question.

    I have been asked to make it so that when my boss fills out a Build Order record (or gives me a build order to input) the Serial Number table will auto create Amt (the number in Amt) records.

    The most preferable outcome would automatically fill in the Serial Numbers, and copy the Build Order and Product field information.

    I appreciate any help, I've been trying to find out how to do something like this through books and google... and I havn't found anything too helpful.

    Edited because my boss decided one big sn table would be a better idea rather than (ultimately) 12 product tables.

    Edited again because we can't decide what do do with the serial numbers...
    My original post, unedited had one table for each product (12 products at the moment) and I wanted to have access create the records in the table named the same as product. ..

    Since we keep waivering back and forth as to whether to use a total of 2 tables, or 13, i figured i'd just post this at the end...
    The reason we would want 12 product tables is to autonumber the serial numbers, since each product has its own set of serials, we wouldn't be able to autonumber them in one table.
     
  2. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Betagrl, I recommed one table for the Product. The seperate numbering for each product can be arranged using VBA code, I have done this for others. I hope the "Product" in the Build Order Table is only it's ID and not the Product name as this is not efficient.
     
  3. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    My recommendation is to sit down with everyone involved and finalize your business rules before beginning development. You need to decide how your system should work before you try to implement tracking and record-keeping.

    Sorry if that sounds like an impertinent answer; solid business rules are absolutely essential to successful database function. Make a list of exactly how the system--the serial numbers, the product categories, etc.--should behave, including any relationships between component members. Your database design will naturally stem from that.

    HTH

    chris.
     
  4. BetaGrl

    BetaGrl Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    11
    If I can do it all with one table, that'd be great. The Product name is actually the SKU, 3 letters and 3 numbers to identify each product (2 numbers in one case)... the Sku is my only identifier for the products since that's what everyone around here calls them anyway, so to me (and everyone here) the SKU is the only identifier we need.

    They already are record keeping. There's years of serial numbers in an excel spreadsheet. (Luckily, they get to stay there, we're starting the db 'from now on.')
    The DB is practically ready for data entry... We just want it as automated as possible and Erik insists it can be done. I'm sure it can, but it's beyond my capabilities. =\ Which is why we keep waivering from one SN table, to one for each product. I don't know how to code in access.
     
  5. BetaGrl

    BetaGrl Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    11
    OK..
    I created a table for the Products. I now can select what product is being built in the build order...

    The question remains: when a new Build Order is created, how do I have access create the records in Traveler/SN/whatever table and preferably create the serial numbers (a different set for each product/sku) as well?

    I don't know VBA but after this I'm looking into buying a book for it...

    (OBP suggested i post a zip of my DB. I will be able to do so later this evening after I get an OK from Erik and I am home. I don't have internet access on my laptop here, which is where I'm making the DB because this computer has old software.)

    Edited because run-on sentences aren't as fun as the name suggests...
     
  6. BetaGrl

    BetaGrl Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    11
    Ok, here it is.
    I don't really want it to be torn apart by people that know better. I know there are probably better ways to do this.


    At the request of my boss.. the real product SKUs have been replaced with random letters/numbers. They are in the format the real ones are in though.
     

    Attached Files:

  7. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    I hope I never tear apart, but constructive critiscism should be allowed, it is part of the learning process.
    Do you want the Consecutive Number (SN) to include the SKU number?
    One thing you should decide at an early stage is which is the "Master" table the SN table or the Build Order table, as at the moment you are duplicating Product in both tables.
    Is the "Ordered By" in the Build table the Customer in the SN table?
    Is the relationship between the Build Order and SN tables a One to One relationship, i.e. one sn for one build?
     
  8. BetaGrl

    BetaGrl Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    11
    The serial numbers already have a certain format. Each one slightly different for each SKU.

    I would like some sort of indication of what product is for each SN...
    but they are 7 numbers, two of the products start with '7' the rest start with 08, 07, 06, etc...
    so.. 0812345 for one product 0712345 for another...
    If we have to have the serial number include the product, we might be able to live with it... Like ABC081234 .. DEF0712345 .. but i'd rather not.
     
  9. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Do the products have a name that could be abbreviated, just something to add at the front of each one's count, or shallI just use A,B,C etc, which could be stored in the Product table so the whole system knows what an "A" product is?
     
  10. BetaGrl

    BetaGrl Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    11
    Well... uhm..
    I suppose if you need a unique identifier for each, even though each SKU is unique, i guess individual letters would be best.
    The info stored in the product table is the abbreviated name.. Or at least it will be.
    Lets see.. like DEF-100, the full name is something like "Delivering Efficient Fullness 100", or something along those lines. It's just that no one uses the full names, we just all call it "DEF" or if there's a DEF-50 and a DEF-100 (which i didn't put on the table though there is ONE case of that), we'll call them DEF-50 and DEF-100... otherwise I'd want to just use the letters.
     
  11. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Beta, I have taken the liberty of re-arranging your Tables and relationships to the way that I think you need them to make what you want to do work. For instance you want one Build, but more than one SN with different Products related to it.
    I have created a subform for the SN which means that you do not need to choose a build, it is automatially related to it. I am currently writing your code to use a "Product Code" letter, which is in the Products table, to add to the incrementing SN ID number.
    This can be changed at any time to something that you and da boss agree on. I will post the revisd database as soon as te code is finished.
     
  12. BetaGrl

    BetaGrl Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    11
    OK...
    thanks.
    can't wait to take a look at it.
     
  13. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Well I hope it was worth the wait.
    I have also taken te liberty of including a "Blank Main Menu", an Autoexec macro and a Splash Screen from one of my databases that you can modify with your Company name.
     

    Attached Files:

  14. BetaGrl

    BetaGrl Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    11
    Ok, uhm. I don't mean to be rude because I appreciate what you've done, and it gives me a few ideas to use... but it doesn't do what I want. (or i'm i just doing something wrong in the form?)

    Is there a way that the 10 or 20 or whatever I enter into "AMT" records can be auto-created in the serial number table?
    There has to be a way, right?
     
  15. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Sorry, I missed that, what do you mean by autocreated. Do you mean that you want to create the number of records in the SN table that matches the value in the AMT field?
    I thought we were talking about getting Serial # that were Product coded.
    I obviously misunderstood.
    There are no values in the AMT field in your database.
    So you want all the records in the SN table auto created with the same Product and the Seral # witht the same Product orientated code?
     
  16. 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/484070

  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