Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78

    Update Query? to insert sequential numbers from one table to another

    I'm not sure if an update query is the right approach but...



    My user has a form which inserts the next available number from table (A) into a single record in table (B). (the proper way to do it!)

    The set of numbers in table (A) change annually and the old numbers are retained in table (B).

    Table (B) is used to print addressed letters in alphabetical order. Table (A) holds numbers which identifies keys (for a lock)

    The task culminates in sending out the letters and enclosing the correct numbered key in each one.

    This arrangement works properly, however, the pandemic gave rise to a late delivery of keys this year. Rather than wait, all the other info was input and key numbers were added last.
    pulling up each record and adding the key numbers one by one was a real long winded job.

    My user has asked if there is way to add the key numbers to the records in bulk

    I can easily identify available key numbers and the records of people to add them to. Table (B) records are sorted A-Z and table (A) key numbers are sequentialy sorted low-high as that makes the physical job of collating everything easy.

    The number of people records are about 240 though in a normal year it's not likely that all key numbers would be added in one hit.

    does anyone have any suggestions please

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Could you provide some sample data to make sure we understand? I find the easiest is to use Excel then copy/paste the range here. You'll get an html table. I find it helps to center all the Excel data. Use more than one range if you need to have separate tables. Would be nice to see the records that need to be updated as well as what the data to use looks like.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with Micron, I'd like to see examples of the data from both tables.
    If the key numbers are in an Access table and the people are in an Access table, all you need is 2 record sets. Read from one table and write to the other table. Would need checks to know when the end of the recordset was reached for either table.

    Maybe make a copy of the dB, change/delete sensitive data, change names to Fred Flintstone, Clark Kent, Mighty Mouse, etc. Only need 10 - 15 names.
    Do a Compact and Repair, compress/Zip it and post.

  4. #4
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Hi,

    Thank you for your replies.
    In essence, the key register changes annually as new keys & the lock are changed. People are are issued a new numbered key each year. all the keys fit one gate padlock.
    Keys will be returned at the end of a year and we therefore retain key numbers in the persons record as there are always late returns and the register will have been repopulated.
    the keys will be reused several times over as they can be recut. So simply having a key register and linking a particular key to a person won't work.

    currently a dropdown box on a form allows selection of the next available key to be added to each persons record one at a time.
    That routine also checks the used checkbox, and inserts the memberhip number and year in the key register.
    What I seek to do is create a method of doing that same update but in a bulk hit. the maximum is around 230 records

    I've attached a couple of excel spreadsheets with only the particular fields included. The first two records in each sheet show what I need to end up with
    a pointer in the right direction would be most welcome

    New_key_register.zipNo_key_(bulk_issue).zip
    Last edited by PDilly; 03-17-2021 at 08:07 AM.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You want to update No_Key.NewKey with the value from New_Key.Keyno where No_Key.APERMNO matches New_Key.ID
    or is that more or less backwards?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by Micron View Post
    You want to update No_Key.NewKey with the value from New_Key.Keyno where No_Key.APERMNO matches New_Key.ID
    or is that more or less backwards?
    Thanks for the reply.



    One thing needs to happen for each record in the No_key table:
    the next unused Keyno from the key register needs to be added to Newkey

    three things need to happen in the New_key_register for each key issued:
    Used changed from FALSE to TRUE
    APERMNO added to Kpermno
    SEASON added to Kseason

    As an example row 4 of No_key, member 1359 would receive key no. 3 being the next unused key. NEWKEY would be updated to 3
    following that, row 4 of the New_key_register would be updated to; used=TRUE, Kpermno=1359 and Kseason=2021
    any routine to issue keys would end when all NEWKEY fields are no longer null

    hope that makes sense

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Yep, but I'm not seeing a solution that is strictly query based as opposed to code. However, there are query wizards here (not me) who might figure one out. I'll post a few records to save anyone the need for downloading, storing and unzipping your files just to see what they look like. What you want is doable and I'll try to make time for a code solution. Is this fix going to be an annual thing?

    New_Key_Register
    ID Keyno used Kpermno Kseason
    1093 1 TRUE 1006 2021
    1094 2 TRUE 1544 2021
    1095 3 FALSE 0 0
    1096 4 FALSE 0 0
    1097 5 FALSE 0 0

    No_Key
    APERMNO SEASON NEWKEY
    1006 2020 1
    1544 2020 2
    1359 2020
    1007 2020
    1008 2020
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by Micron View Post
    Yep, but I'm not seeing a solution that is strictly query based as opposed to code. However, there are query wizards here (not me) who might figure one out. I'll post a few records to save anyone the need for downloading, storing and unzipping your files just to see what they look like. What you want is doable and I'll try to make time for a code solution. Is this fix going to be an annual thing?

    New_Key_Register
    ID Keyno used Kpermno Kseason
    1093 1 TRUE 1006 2021
    1094 2 TRUE 1544 2021
    1095 3 FALSE 0 0
    1096 4 FALSE 0 0
    1097 5 FALSE 0 0

    No_Key
    APERMNO SEASON NEWKEY
    1006 2020 1
    1544 2020 2
    1359 2020
    1007 2020
    1008 2020
    Many thanks
    yes the change is annual.
    the member records stay put as updated until such time as we feel deletion is appropriate. The portion of member records used in this routine is the many part of one to many and a new portion is created every year.
    the key register will retain two sets of key numbers. One year’s Keys will be returned, re-cut and issued again the following year.
    i already have a returns structure in my DB to cope with this but essentially puts next year’s keys in an unused state but not able to be issued.
    I suspect that you are absolutely right. A query and a small piece piece of code handles single entries very well but given the parameters, bulk is a whole different ball-game.
    your assistance is very much appreciated.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    For anyone else that might be following, I think an issue is that there is no sorting field in No_Key table. My current thinking is
    - get DMin of NewKey.KeyNo where USED = False
    - get DMin of ??? where NewKey is null. However, there is no field on which to sort NoKey records, so there is no way to guarantee that 1359 would be the next record. If there was a form field containing 1359 you could do the one record but this is supposed to be a batch operation. So I'm stuck at the moment without a sort order for NoKey. If an autonumber field could be added to the table then the DMin could be on this sorting field. It will never matter if the NewKey values and autonumber values match. It would only matter that the autonumber won't alter the current NewKey values order. If the attachments represent the table records, it should be no problem.

    Anyone else is welcome to jump in. Obviously the thought process is incomplete as I've only considered the first 2 steps I had in mind.
    Last edited by Micron; 03-17-2021 at 02:26 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Hi micron
    For privacy I left quite a few fields out of the No_key table. Surname is included which is what I would prefer to sort on.
    otherwise you could sort on PERMNO which is a unique number for every record as that is auto numbered.
    ultimately. It’s only important that everyone gets a different numbered key
    hope that helps
    ...and thanks again

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Rather than anyone guess and guess wrong, could you upload a new file with the additional field(s) you think will allow for the correct sort?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    PDilly,

    This is the gate for the fishing lake, right? I had made some suggestions when you were trying to create the db, do you remember?

    Now, I think the fields used, Permno and Kseason, should not be in the New_Key_Register table. This is what is called data redundancy.
    The following query returns the same information:
    Code:
    SELECT New_key_register.ID, 
    New_key_register.Keyno, 
    Not IsNull([NEWKEY]) AS InUse, 
    No_key.APERMNO AS KPERMNO, 
    No_key.SEASON AS KSEASON
    FROM New_key_register LEFT JOIN No_key 
    ON New_key_register.Keyno = No_key.NEWKEY;
    In actually, I am little a bit confused with the different seasons in two tables for the same records. Could you please explain that?

    If I have understood correctly, the following function runs a bulk issue for the chosen season:
    Code:
    Function IssuedKeys(Optional intYear As Integer) As Long
        Dim rsNew As DAO.Recordset
        Dim rsIssued As DAO.Recordset
        Dim db As DAO.Database
        Dim i As Integer
        Dim strSQLReg As String
        Dim strSQLIssued As String
    
        On Error GoTo ErrH
        If intYear = 0 Then
            'No specific input year.
            'Get current year.
            intYear = Year(Date)
        End If
        'SQL for the members of the given year.
        strSQLIssued = "SELECT APERMNO, SEASON, NEWKEY FROM No_key " _
                       & "WHERE SEASON=" & intYear
        'SQL for the available keys for the given year.
        strSQLReg = "SELECT Keyno " _
                    & "FROM New_key_register AS Reg " _
                    & "LEFT JOIN (" & strSQLIssued & ") AS Iss " _
                    & "ON Reg.Keyno = Iss.NEWKEY " _
                    & "WHERE Iss.NEWKEY Is Null;"
        'SQL for only members w/o keys for the given year.
        strSQLIssued = strSQLIssued & " AND NEWKEY is null;"
    
        Set db = CurrentDb
        Set rsIssued = db.OpenRecordset(strSQLIssued, dbOpenDynaset)
        With rsIssued
            If Not (.BOF And .EOF) Then
                'Get the available keys from New_key_register.
                Set rsNew = db.OpenRecordset(strSQLReg, dbOpenDynaset)
                If Not (rsNew.BOF And rsNew.EOF) Then
                    While (Not .EOF) And (Not rsNew.EOF)
                        .Edit
                        'Add this key to this member.
                        !NEWKEY = rsNew!Keyno
                        .Update
                        rsNew.MoveNext
                        .MoveNext
                        i = i + 1
                    Wend
                End If
            End If
        End With
    ExitHere:
        On Error Resume Next
        rsNew.Close
        Set rsNew = Nothing
        rsIssued.Close
        Set rsIssued = Nothing
        Set db = Nothing
        IssuedKeys = i
        On Error GoTo 0
        Exit Function
    ErrH:
        MsgBox "Error: " & Err & vbCrLf & Err.Description, vbExclamation, "Bulk Issue"
        Resume ExitHere
    End Function
    Take a look in attachment and let me know if I have missed something.
    KeysRegister.zip

    Cheers,
    John

  13. #13
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Apologies Micron.
    I'm dreadful at remembering to include things that people need to know!
    I'll update the files later. Unexpected busy day today
    Best wishes
    Pete

  14. #14
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Hi John,
    Of course I remember you. Your help was most valuable.

    I can see your point on data reduncancy entirely. Back in the day that's how we worked to some extent. To a large extent the pandemic and a new kind of key type has changed that.
    a brief synopsis will help you understand why we are trying to work differently.

    We held 7 sets of keys & locks and rotated them over a 7 year period.
    We used to receive 90% of applications for membership by post. when they arrived. That was perhaps at a max rate of a dozen a day. The envelopes were all filed in a box in alphabecal order by members name. All the data was input as they arrived including key number and the key was physically put in their envelope at the time of input. At an appropriate time all the membership cards were printed in alphabetic order, matched with the key in their envelope and posted.

    Fast forward to this year. No-one likes going anywhere and the majority of people have found online everything is a life saver!
    We now get 99% of applications online so nothing to file in a box and they turn up less often in greater numbers.
    The new keys are designed to be ultra secure and unusually, can be re-cut each year to provide a different lock.
    we will have just two differently numbered sets of 250 keys and re-cut one set after each year's use. Those same 500 numbered keys will remain in use for at least ten years. I have already implemented a key return return system in the DB.
    One might questionably ask whether we need to retain key number in a member's annual record and in the key register but as it stands, it is part of the annual history and I'd prefer to let it stand.
    The key register itself tells us the status of any key; current series unused and in stock, current series issued and in use, previous series not returned by member xxxx, previous series but returned or not issued.

    Our delivery of keys and and applications and renewals from members were late this year which meant around 200 records were waiting to have a key number input. As a one-a-time job, it was a pig.
    Looking forward, we expect that online application is here to stay and they will come in thick and fast. It just seems that with keys stored in numerical order and nowhere to physically put them as records are input, it is sensible working practice to have as many records in place, and add key numbers to those records just prior to printing, print, and load up outgoing envelopes with cards and keys.

    To make the physical task work easily, it relies upon the no-key table sorted alphabetically by member surname, christian name, initial which it is by default and the key numbers being added to it low to high.

    hope that makes sense and gives some insight.

    I'll have more time to look at your zip file later today or tomorrow. Unexpected busy day today!
    regards & thanks
    Pete

  15. #15
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Pete!

    Thanks for the detailed description. I'll be back at the end of the day to review it carefully.
    I have to collect my wood for the winter these days. So, for the moment, I have to leave the laptop aside and start my chainsaw.

    Have a nice day!

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-08-2019, 11:24 AM
  2. sequential numbers on filtered query
    By SunTop in forum Access
    Replies: 1
    Last Post: 01-31-2018, 02:03 AM
  3. Replies: 10
    Last Post: 04-22-2014, 02:42 AM
  4. To generate sequential numbers in query
    By lizzywu in forum Programming
    Replies: 1
    Last Post: 03-02-2012, 12:01 PM
  5. Replies: 11
    Last Post: 07-14-2011, 03:03 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