Results 1 to 11 of 11
  1. #1
    james is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    2

    Equipment database update new information and keep old information

    I have created a equipment database with simple enough information: manufacturer, model, S#, calibration performed date, calibration due date
    I have 100 items of unique equipment. Items are calibrated on a quarterly basis. How do I update information from a new calibration and keep a record of the old calibration at the same time?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Equipment in 1 table, calibrations in another, so no calibration types/dates in the equipment table. It sounds like you were going to put all in one table.
    tblCalibrations would have CalID (primary key), EquipID (PK from equipment table) and date fields - maybe even inspector ID from tblInspectors.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    james,

    Here is a link to various articles on Database Planning and Design. I recommend you work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the link. You have to appreciate and experience some of the concepts of database before jumping into a physical database application. Each of the tutorials starts with a clear description of the requirement and leads you through a process to identify tables and attributes/fields. Each will take 30 -60 min to work through, but you will learn about tables and normalization and relationships. And what you learn can be used with any database.
    There are other materials at the link that may be helpful to you as well.
    Good luck with your project.

  4. #4
    james is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    2

    Follow up question.

    If I create the Calibrations table, record 1 might be the next calibration date of equipment 1, record 2 equipment 2 and so on. For simplicity, if all equipment was calibrated in order, record 100 would be for equipment 100 and record 101 would be for the subsequent calibration of equipment 1. Correct?

    Quote Originally Posted by Micron View Post
    Equipment in 1 table, calibrations in another, so no calibration types/dates in the equipment table. It sounds like you were going to put all in one table.
    tblCalibrations would have CalID (primary key), EquipID (PK from equipment table) and date fields - maybe even inspector ID from tblInspectors.

  5. #5
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    Quote Originally Posted by james View Post
    If I create the Calibrations table, record 1 might be the next calibration date of equipment 1, record 2 equipment 2 and so on. For simplicity, if all equipment was calibrated in order, record 100 would be for equipment 100 and record 101 would be for the subsequent calibration of equipment 1. Correct?
    It would, but that is not the purpose of the ID primary key field. The ID is just a unique identifier for each row. It could be a bunch of random digits and the database design would be the same. It is used for linking tables in queries but do not have any other value. All the data belongs in other fields.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    James,

    Look at the materials and work through 1 or 2 tutorials. It will save you time and you will learn.
    Good luck.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There are a few things that would throw a wrench into that plan.
    What you're doing may depend on whether or not the next calibration date is fixed or variable; i.e. is the next date on a set frequency regardless of when it was done last? Probably not smart. Then it would be t period after the last calibration. Regardless, this date (next calibration date) would not be stored - usually bad form to store calculations and I think this is one of those cases. You perform calculations in queries/forms/reports.

    A table has no guaranteed order for records, even if sorted. You should think of it as a bucket of marbles, each marble being a record. Access usually gets it right for display purposes, but if you create a recordset from a table and begin to loop through it, you might actually begin working on a record far removed from the 1st one. I have seen this myself. The only 'guarantee' that you can start at the beginning is to base things on an ordered query.

    That being said, a table for inspections/calibrations is only for those that were performed - that is the entity, which I hope you will learn about from the links orange posted (or others if you prefer). What's important here is for you to identify the entities (tables) involved, along with their characteristics (fields). Such a calibration table should be along the lines of
    calID EquipID LastCalDte DoneBy CalType
    1 42 5/08/18 44 3
    2 11 8/15/18 44 3
    3 85 9/24/18 55 5
    4 15 3/15/18 44 8
    5 6 7/18/18 55 7

    Whatever record ID of equipment table = 42 tells you everything you want to know from the equipment table, which is where the calibration frequency (and maybe type, both from their own tables perhaps). Similarly, DoneBy is 44 from tblEmpl. LastCalDate is the only thing here that ideally doesn't come from another table (or at least a 'controlled list' belonging to a form control). The form shows you what the next calibration date is by adding the frequency associated with a piece of equipment to the last cal date. If the frequency of 42 is 26 weeks, it is due somewhere around 11/08/2018 (I didn't make the exact calculation for this purpose).

    You might think this is all too complicated and prefer to do it in an un-normalized fashion. Access is easy to do poorly, not so easy to do well. There's a lot of conceptual design one needs to cover lest you put many hours into something that in the end, gets you banging your head up against the wall. If it was so easy, we wouldn't have Access forums!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It is used for linking tables in queries
    Umm, maybe not so much. If it's so random, it has no meaning. As you say, even an ordered autonumber has no meaning (as far as relating records is concerned). Thus I think it would be pretty useless in joining tables. Here's a couple of what I think are the best treatises on the subject.
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    @James - these links may also prove useful to you going forward. Suggest you don't overlook the one on calculated fields and storing calculations (different things)

    Naming things
    - http://access.mvps.org/access/general/gen0012.htm
    - https://access-programmers.co.uk/for...d.php?t=225837

    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm

    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp

    About calculated table fields - http://allenbrowne.com/casu-14.html

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by ssanfu View Post
    That's the same page as Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    but yours is prettier.

    P.S. - beat you again.
    Last edited by Micron; 09-25-2018 at 07:54 PM. Reason: added info

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    That's the same page as Table and PK design tips
    Oops... missed that.


    P.S. - beat you again.
    You just HAD to rub it in.... didn't you.

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

Similar Threads

  1. Replies: 8
    Last Post: 09-22-2015, 02:59 PM
  2. Replies: 2
    Last Post: 02-05-2015, 03:51 PM
  3. Replies: 3
    Last Post: 05-02-2014, 09:27 AM
  4. Replies: 1
    Last Post: 09-12-2012, 12:44 PM
  5. Combo Box to Update Tabbed Information ona Form
    By JessicaNicole in forum Access
    Replies: 3
    Last Post: 08-30-2012, 01:56 PM

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