Results 1 to 8 of 8
  1. #1
    accessorizer is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    4

    Insert a new record in a particular position in a table?

    Hi,
    What I'm trying to do is create a delivery sequence list for a mail route. I have a table with occupant names and addresses in one table and previous occupants with current and expired forwarding orders in another table. The primary key in the current occupants table is an autonumbered field used as the foreign key in the previous occupants table to pull associated records from there. To allow changes in the delivery sequence or add a delivery, I added a field in the current occupants table for a sequence number and it is this field I need to find a way to manage. Here is my problem - when I add a record and assign a sequence number, is there a way to make Access check for duplicates and handle the adjustment of all the other sequence numbers to accommodate the new entry, or do I have to do that in code? If it's code, can someone suggest a strategy to do the deed? I used to use some two way linked lists and build an inverted list from that as an index in this sort of situation, but that was a home-brew db that was code from scratch and I'm just wondering if there isn't something built into Access that can be used to do the same thing more simply.



    Thanks for any suggestions.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I expect it could be done. Means updating all the records by an increment of 1 (except the one being assigned the new sequence) from the current existing sequence number that is being reassigned up to the current sequence number of the record that is being reassigned (or -1 and modified criteria depending on which way the record is moving). Might be possible with an UPDATE sql action.

    If record is moving backward:
    UPDATE tablename SET SequenceNum = SequenceNum + 1 WHERE SequenceNum >= number reassigned And SequenceNum < number of record being reassigned

    If record is moving forward:
    UPDATE tablename SET SequenceNum = SequenceNum - 1 WHERE SequenceNum > number of record being reassigned And SequenceNum <= number being reassigned

    Getting the inputs into the criteria parameters and determining the arithmetic are the tricky parts.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If the sequence number is for your use, and it is in this case, then an autonumber would not be used. The autonumber field could still be the primary key of the table.
    It seems that you have a number of records and it is not until most, if not all, records have been "processed", do you need to put them into delivery sequence.

    How exactly do you determine a delivery sequence -- house numbers on a street? Postal codes or parts thereof?
    Please provide more info in plain English describing how you would put items in a delivery sequence.

    This is a little picky, but he title of your post is a little misleading. There is no position within a table per se that you control. Access will put records in the table. You can retrieve records from a table in various ways by means of queries and Order By parameters.

  4. #4
    accessorizer is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    4
    Quote Originally Posted by orange View Post
    If the sequence number is for your use, and it is in this case, then an autonumber would not be used. The autonumber field could still be the primary key of the table.
    It seems that you have a number of records and it is not until most, if not all, records have been "processed", do you need to put them into delivery sequence.

    How exactly do you determine a delivery sequence -- house numbers on a street? Postal codes or parts thereof?
    Please provide more info in plain English describing how you would put items in a delivery sequence.

    This is a little picky, but he title of your post is a little misleading. There is no position within a table per se that you control. Access will put records in the table. You can retrieve records from a table in various ways by means of queries and Order By parameters.
    Thank you for the response.
    I'm sorry for the title discrepency. Didn't know just how to phrase it. I understand that data is just added to the table and then retrieved in a particular order, and I think I do need the autonumber field as a primary key to establish and maintain a relationship of records in the deliveries table to related records in the second table containing forwarding orders from each delivery address.
    The required listing sequence is determined by the line of travel on the route which travels up and down street address numbers in ascending and descending order and crosses from one street to another and back several times on most streets and roads, so I can't order the listing by any of the data itself. I'm using a separate field for sequence numbers to establish the order in which the deliveries are listed, and it is this sequence field that I need to figure out how to maintain. When the data is initially entered in the deliveries table it can be entered in the correct line of travel route sequence and the sequence field can just be populated sequencially. This is a rural delivery route with considerable distances between some of the deliveries and so the line of travel can sometimes change as new homes are added, vacated or even removed. It's no problem to delete a delivery as the sequence is not altered by a missing number in that sequence, but when a delivery is added there is not necessarily a vacant sequence number of the proper value for the required position to be assigned without altering all subsequent sequence numbers, and a change in the line of travel creates the same problem in spades.
    I'm considering the idea of doing away with the sequence field and adding two new fields - fldNext and fldPrevious to implement a two way linked list. fldNext could serve the same purpose as the present sequence field and inserting a new delivery would just require retrieving the primary key of the record which the new record is to precede and using it's fldNext and fldPrevious. If that record is CurrentRecord then something like this...

    PreviousRecord = CurrentRecord.fldPrevious 'save pointer to the previous record
    NewRecord.fldNext = CurrentRecord
    NewRecord.fldPrevious = CurrentRecord.fldPrevious
    CurrentRecord.fldPrevious = NewRecord
    PreviousRecord.fldNext = NewRecord 'NewRecord is now in the sequence formerly occupied by CurrentRecord and CurrentRecord and all subsequent records follow.

    (similar sequence when a record is deleted)

    This would work with an array in memory, but I understand that it would have to include commands to move to each of the involved records in the table to make the alterations. I need to find the syntax to do that and I haven't decided how to enter the data in a new record and then trigger the code (in the BeforeUpdate event?) while also selecting the record (in a DataSheet view?) that the new record should be inserted before.

    Could I do this in a form with field textboxes for the new data and include a subdatasheet on the form (populated by a query ordered on fldNext) to select insertion position?

    edit to add:
    Is there a way to enter data in a new record in a datasheet view and then select (and capture) the insertion point before the update event of the new record is executed? (do away with the textboxes)?

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think I do need the autonumber field as a primary key
    yes I agree totally.
    I'm not sure about dropping the sequence number idea, or the fldPrevious, fldNext.

    A suggestion, but you may already be beyond this :

    I would focus on what I need to do to get a delivery schedule, and then move on to how once I had a technique.

    Get some dummy name and address records; concoct something that mimics your real situation, and work out a process as to how to sequence these for delivery. You might even show us some records and your attempts to see if anyone has other ideas(algorithms).

    If you don't have some sort of delivery route, or if routes are different every day/week whatever, I'm not sure there is an algorithm. I did see someone from the UK recently on a forum who was trying to optimize routes based on some Google map algorithm, but I don't think anything was optimized -- it just identified spots along the route.

    A real life issue where I am, I live at #18, and the house directly across the street from me is #3, (we're the first houses on opposite sides of the street) so there are some "unique" mechanisms for numbering houses in the block.
    There are Postal Code and block identifiers available via PostOffice for a price. I guess it depends on how big your operation is and how important optimization is , and at what level of granularity.

    I haven't done any optimization, or anything other than trivial, with Google Maps, but I'm sure that line of thinking may offer some approaches (again maybe at a cost).

  6. #6
    accessorizer is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    4
    Quote Originally Posted by orange View Post
    yes I agree totally.
    I'm not sure about dropping the sequence number idea, or the fldPrevious, fldNext.

    A suggestion, but you may already be beyond this :

    I would focus on what I need to do to get a delivery schedule, and then move on to how once I had a technique.

    Get some dummy name and address records; concoct something that mimics your real situation, and work out a process as to how to sequence these for delivery. You might even show us some records and your attempts to see if anyone has other ideas(algorithms).

    If you don't have some sort of delivery route, or if routes are different every day/week whatever, I'm not sure there is an algorithm. I did see someone from the UK recently on a forum who was trying to optimize routes based on some Google map algorithm, but I don't think anything was optimized -- it just identified spots along the route.

    A real life issue where I am, I live at #18, and the house directly across the street from me is #3, (we're the first houses on opposite sides of the street) so there are some "unique" mechanisms for numbering houses in the block.
    There are Postal Code and block identifiers available via PostOffice for a price. I guess it depends on how big your operation is and how important optimization is , and at what level of granularity.

    I haven't done any optimization, or anything other than trivial, with Google Maps, but I'm sure that line of thinking may offer some approaches (again maybe at a cost).
    Yes, I have the delivery sequence at present and in practice it changes very little most of the time. There is no scheduling involved as it is always the same, it is merely the sequence changes that I need to handle. There are about 485 delivery points on the route and no more frequently than the sequence changes, it would probably be feasible to simply increment all the values in the sequence field from the point of an insertion to the end of the table, but since the table is on disk rather than in memory I thought it would be faster and more transparent to use the two pointer fields and do the deed by altering only two of the existing records rather than changing potentially hundreds of records for each change.
    The main point of this little project is actually the association of the past occupants with the delivery points as there are about as many of those as there are current occupants and the fact that we often don't deal with each of those on a daily basis makes them much more difficult to handle from memory - especially for substitute carriers who often don't see those changes as they occur. This sequencing issue is just a snag I ran into on the way to that.

    Again, I appreciate your input.
    Thank you.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did my suggestion in post #2 have any relevance?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    accessorizer is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Mar 2012
    Posts
    4
    Quote Originally Posted by June7 View Post
    Did my suggestion in post #2 have any relevance?
    Yes, thank you, I think that may be the simplest solution if (as you pointed out) I can figure out how to hook it all up. I just haven't decided which approach to go for - may try both before it's done. My main hesitance with this method is that I'm no sql wizard and don't know really how to procede in that direction. I've used Access a few times with some fairly trivial coding, but I do this so infrequently that I pretty much have to start from scratch with coding language & syntax.

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

Similar Threads

  1. Replies: 7
    Last Post: 01-02-2012, 06:19 PM
  2. Get position of record in continuous form
    By Whizbang in forum Forms
    Replies: 3
    Last Post: 11-02-2011, 01:47 PM
  3. Insert record from table into different table
    By tpcervelo in forum Forms
    Replies: 3
    Last Post: 01-25-2011, 02:41 PM
  4. Insert Record checks table
    By pfarnell in forum Forms
    Replies: 13
    Last Post: 09-05-2010, 10:47 AM
  5. Replies: 7
    Last Post: 04-27-2010, 10:29 AM

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