Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Giving Dates a Sequence Number

    From a listing of Dates in a Tables how can I give them a sequence number?

    My table example

    AddressID RequiredDate
    001 1-May-08
    001 5-May-08
    001 6-May-08
    002 1-May-08
    003 1-May-08
    003 3-May-08

    I am trying to get my table to look like this

    AddressID RequiredDate Sequence
    001 1-May-08 1
    001 5-May-08 2
    001 6-May-08 3
    002 1-May-08 1
    003 1-May-08 1
    003 3-May-08 2

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How do you intend to use that Sequence number once it's been assigned? There are a few ways to do things, but some of them may be inappropriate depending on exactly what the business requirements are for those sequence numbers.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    With a Sequence Number I can establish what sort of things have been done per Date, eg Can see when job was completed, can easily show the visits route through dates. Comparisons can be done.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I presume Jez this relates to this?
    http://www.dbforums.com/showthread.php?t=1630158

    Just to cover two things:
    Is this going into a report?
    Is the actual database itself Access\ JET?

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    No this is another part of the database that isnt linked to the previous thread.

    I need it for being based in a query. I'm building reports from query's (but not reports in Access)
    The database is based around Linked Tables from a SQL Server.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What would happen to your sequence numbers if the following entry was removed:

    001 5-May-08

    Would you get:

    001 1-May-08 1
    001 6-May-08 3
    002 1-May-08 1
    003 1-May-08 1
    003 3-May-08 2

    or

    001 1-May-08 1
    001 6-May-08 2
    002 1-May-08 1
    003 1-May-08 1
    003 3-May-08 2
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by JezLisle
    The database is based around Linked Tables from a SQL Server.
    SQL Server 2k5? You got DDL creation permissions on that?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is the nice, simple & efficient SQL 2k5 syntax:
    Code:
    DECLARE    @mytable TABLE
        (
            AddressID        CHAR(3)
            , RequiredDate    SMALLDATETIME
        )
    
    INSERT @mytable
                SELECT    '001', '1-May-08'
    UNION ALL    SELECT    '001', '5-May-08'
    UNION ALL    SELECT    '001', '6-May-08'
    UNION ALL    SELECT    '002', '1-May-08'
    UNION ALL    SELECT    '003', '1-May-08'
    UNION ALL    SELECT    '003', '3-May-08'
    
    
    SELECT    AddressID 
            , RequiredDate
            , sequence    = ROW_NUMBER()    OVER    (
                                                    PARTITION BY    AddressID
                                                    ORDER BY        RequiredDate ASC
                                                )
    FROM    @mytable

  9. #9
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    It is SQL Server 2005. I dont understand what you mean DDL creations

    I work from an Access Database using ODBC Linked Tables.

    In the Code above what do you mean by the @ before the mytable?

  10. #10
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Teddy... There couldnt be a entry removed. Once the end user inputs the entry they are then locked on the forms so that editing them can't be done.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    DDL = Data Definition Language. Basically can you create objects on the SQL Server? (in this case a view - this is very similar to an Access saved query)
    The code is T-SQL - run it in SQL Server Management Studio, or use an Access Pass-through query, and you'll see how it works. All variables in T-SQL are prefixed "@" - that is a table variable.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by JezLisle
    Teddy... There couldnt be a entry removed. Once the end user inputs the entry they are then locked on the forms so that editing them can't be done.
    You might be surprised. What if you as an admin had to remove one?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just to be real picky...

    "001" is not a number
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by georgev
    Just to be real picky...

    "001" is not a number
    Maybe not in your mathematics, but in my mathematics all numbers have an infinity of leading and trailing zeros, but we're too lazy to write those zeros even though we know that they are there.

    -PatP

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Pat Phelan
    Maybe not in your mathematics, but in my mathematics
    Lol, nice try

    Ok then; in Access, to display / store leading zeroes you must convert your numeric value to text.

    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •