Creating a statement that produces an Primary Key ID

  • drybone

    Old Hand

    Points: 328

    Hello,
    I'm currently working on a project to track containers that I ship out. My problem is that I have different people I ship to on a regular basis and I want a shipping container number to automatically generate based on the person it is going to. What I want to do is laid out below:
    I have 3 fields in the shipping form I want to use because they use to create the number
    Field 1- FldCompanyID (ie Autobots= 13)
    Field 2- FldContactID (ie Optimus Prime= 1)
    Field 3- FldYearIssued (ie 2018)
    I want to be able to input all that info onto the form and then combine it in Field 4 which will be the Primary key for the table (TblOGML) to which the form is bound to. The output will look like this:
    1312018
    In addition I want it to automatically add a serial number to the end of that string to identify the particular package that was sent out based on field 2 info. Which would make it look like:
    1312018001
    So I tried to do this by using all the bound info in fields 1-3 on the form and placing it into an unbound field 4.
    =[FldCompanyID] & "" & [FldContactID] & "" & [FldYearIssued]
    Then via a command button (Cmd05) run a macro that:
    Executes a query to append table (TblOGML)
    I just don't see how I can add a serial number to the end of the string automatically based on the information in Field 2.
    Any help would be great and I thank you all for taking the time to read this.

    James

  • pietlinden

    SSC Guru

    Points: 62394

    Primary keys are for relating to other records in other tables. Creating an "intelligent" primary key is going to be a pain to manage. Why can you not use a regular IDENTITY column, and then have several required fields in the table? (You could have a group of fields that together must be unique, but they're not necessarily the primary key.)

  • drybone

    Old Hand

    Points: 328

    pietlinden - Thursday, March 22, 2018 6:22 PM

    Primary keys are for relating to other records in other tables. Creating an "intelligent" primary key is going to be a pain to manage. Why can you not use a regular IDENTITY column, and then have several required fields in the table? (You could have a group of fields that together must be unique, but they're not necessarily the primary key.)

    I didn't mention but I receive all of my containers back due to the reoccurring shipments with the same type of items. I use the primary keys assigned to the aforementioned setup I'm currently trying to make to control the supply chain from shipping through return. It also helps me to see how long I have boxes in suspense of being returned. I'm trying to do it to where I don't have to sift through the container ID's to get the last one used by a specific customer. This will save around 3 minutes per transaction and an overall of 2hrs per day that I can be spending not doing paperwork.
    Let me ask you this, if I did have a field: FldContainerID how would I auto-populate that field based on the FldContactID Input?

  • pietlinden

    SSC Guru

    Points: 62394

    Let me back up a step.  if you want to identify a Shipment etc, you can use an AutoNumber primary key. Sure, they're meaningless, but you can have foreign keys pointing to that Shipment's owner etc.

    drybone - Thursday, March 22, 2018 5:46 PM

    Hello,
    I'm currently working on a project to track containers that I ship out. My problem is that I have different people I ship to on a regular basis and I want a shipping container number to automatically generate based on the person it is going to. What I want to do is laid out below:
    I have 3 fields in the shipping form I want to use because they use to create the number
    Field 1- FldCompanyID (ie Autobots= 13)
    Field 2- FldContactID (ie Optimus Prime= 1)
    Field 3- FldYearIssued (ie 2018)
    I want to be able to input all that info onto the form and then combine it in Field 4 which will be the Primary key for the table (TblOGML) to which the form is bound to. The output will look like this:
    1312018
    In addition I want it to automatically add a serial number to the end of that string to identify the particular package that was sent out based on field 2 info. Which would make it look like:
    1312018001
    So I tried to do this by using all the bound info in fields 1-3 on the form and placing it into an unbound field 4.
    =[FldCompanyID] & "" & [FldContactID] & "" & [FldYearIssued]
    Then via a command button (Cmd05) run a macro that:
    Executes a query to append table (TblOGML)
    I just don't see how I can add a serial number to the end of the string automatically based on the information in Field 2.
    Any help would be great and I thank you all for taking the time to read this.

    James

    James, you can do the "serial number" part with DMAX().+ 1.  There are tons of examples on http://www.utteraccess.com .  There's also an example on http://www.mvps.org/access (moved somewhere)... but if you look it up (if that part still works)... there's an example there.  I just think that stuff gets messy fast.  What you're asking for is like an "intelligent" key. They're a PITA to maintain.

  • sgmunson

    SSC Guru

    Points: 110433

    pietlinden - Friday, March 23, 2018 12:03 AM

    Let me back up a step.  if you want to identify a Shipment etc, you can use an AutoNumber primary key. Sure, they're meaningless, but you can have foreign keys pointing to that Shipment's owner etc.

    drybone - Thursday, March 22, 2018 5:46 PM

    Hello,
    I'm currently working on a project to track containers that I ship out. My problem is that I have different people I ship to on a regular basis and I want a shipping container number to automatically generate based on the person it is going to. What I want to do is laid out below:
    I have 3 fields in the shipping form I want to use because they use to create the number
    Field 1- FldCompanyID (ie Autobots= 13)
    Field 2- FldContactID (ie Optimus Prime= 1)
    Field 3- FldYearIssued (ie 2018)
    I want to be able to input all that info onto the form and then combine it in Field 4 which will be the Primary key for the table (TblOGML) to which the form is bound to. The output will look like this:
    1312018
    In addition I want it to automatically add a serial number to the end of that string to identify the particular package that was sent out based on field 2 info. Which would make it look like:
    1312018001
    So I tried to do this by using all the bound info in fields 1-3 on the form and placing it into an unbound field 4.
    =[FldCompanyID] & "" & [FldContactID] & "" & [FldYearIssued]
    Then via a command button (Cmd05) run a macro that:
    Executes a query to append table (TblOGML)
    I just don't see how I can add a serial number to the end of the string automatically based on the information in Field 2.
    Any help would be great and I thank you all for taking the time to read this.

    James

    James, you can do the "serial number" part with DMAX().+ 1.  There are tons of examples on http://www.utteraccess.com .  There's also an example on http://www.mvps.org/access (moved somewhere)... but if you look it up (if that part still works)... there's an example there.  I just think that stuff gets messy fast.  What you're asking for is like an "intelligent" key. They're a PITA to maintain.

    James,

    After dealing with Access for more than 20 years, it is MUCH, MUCH easier to have foreign key relationships to tables that hold the details of the Company and Contract.  Creating your own composite to become an "intelligent key" is far more trouble than it could ever be worth.  Having those foreign key relationships in place makes everything else a piece of cake.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • Jeff Moden

    SSC Guru

    Points: 994261

    sgmunson - Friday, March 30, 2018 1:39 PM

    pietlinden - Friday, March 23, 2018 12:03 AM

    Let me back up a step.  if you want to identify a Shipment etc, you can use an AutoNumber primary key. Sure, they're meaningless, but you can have foreign keys pointing to that Shipment's owner etc.

    drybone - Thursday, March 22, 2018 5:46 PM

    Hello,
    I'm currently working on a project to track containers that I ship out. My problem is that I have different people I ship to on a regular basis and I want a shipping container number to automatically generate based on the person it is going to. What I want to do is laid out below:
    I have 3 fields in the shipping form I want to use because they use to create the number
    Field 1- FldCompanyID (ie Autobots= 13)
    Field 2- FldContactID (ie Optimus Prime= 1)
    Field 3- FldYearIssued (ie 2018)
    I want to be able to input all that info onto the form and then combine it in Field 4 which will be the Primary key for the table (TblOGML) to which the form is bound to. The output will look like this:
    1312018
    In addition I want it to automatically add a serial number to the end of that string to identify the particular package that was sent out based on field 2 info. Which would make it look like:
    1312018001
    So I tried to do this by using all the bound info in fields 1-3 on the form and placing it into an unbound field 4.
    =[FldCompanyID] & "" & [FldContactID] & "" & [FldYearIssued]
    Then via a command button (Cmd05) run a macro that:
    Executes a query to append table (TblOGML)
    I just don't see how I can add a serial number to the end of the string automatically based on the information in Field 2.
    Any help would be great and I thank you all for taking the time to read this.

    James

    James, you can do the "serial number" part with DMAX().+ 1.  There are tons of examples on http://www.utteraccess.com .  There's also an example on http://www.mvps.org/access (moved somewhere)... but if you look it up (if that part still works)... there's an example there.  I just think that stuff gets messy fast.  What you're asking for is like an "intelligent" key. They're a PITA to maintain.

    James,

    After dealing with Access for more than 20 years, it is MUCH, MUCH easier to have foreign key relationships to tables that hold the details of the Company and Contract.  Creating your own composite to become an "intelligent key" is far more trouble than it could ever be worth.  Having those foreign key relationships in place makes everything else a piece of cake.

    +1000 to that!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply