Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2006
    Posts
    7

    Unanswered: Urgent Query Sequential Numbering Issue

    My Access table looks like this (only 2 fields):

    ID # DATE
    1135 10-May-06
    1135 14-May-06
    2259 19-May-06
    2259 12-Jul-06
    3564 20-Jun-06
    3989 16-Jun-06
    3999 18-Jun-06
    3999 04-Jul-06
    3999 05-Jul-06
    4198 04-Jul-06
    4459 30-Jun-06
    4459 30-Jun-06
    5988 07-Jul-06
    6333 26-Jun-06

    With a query, I need to simply assign an autonumber for each ID# with each change in DATE. Goal of query is to return the following (SEQ field is what I need the query to create):

    SEQ ID # DATE
    1 1135 10-May-06
    2 1135 14-May-06
    1 2259 19-May-06
    2 2259 12-Jul-06
    1 3564 20-Jun-06
    1 3989 16-Jun-06
    1 3999 18-Jun-06
    2 3999 04-Jul-06
    3 3999 05-Jul-06
    1 4198 04-Jul-06
    1 4459 30-Jun-06
    2 4459 30-Jun-06
    1 5988 07-Jul-06
    1 6333 26-Jun-06

    Many, many thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    A query won't do that ... Some VBA code will ... And exactly where do you propose to hold this data?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Aug 2006
    Posts
    7
    The data is actually generated from another user via a make table query, so it is held in an Access database as a simple table. I am fairly good with queries and expressions..., but VBA is not yet my game. Could you provide an example of a string that might point me in the direction I need?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It can be derived in SQL but it will run like a dog I'm afradi. You sure you still want to do this? Is it to be displayed in a report or query? If the former it might be easier not to do it in SQL....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2006
    Posts
    7
    Displayed in a query. The good news is that there are few records generated per month (200 records or so) that I have to deal with. I am used to dealing with millions of records, but this very small size will hopefully help with the "running like a dog" scenario??? Currently the end user runs the make table, copies to Excel, and manually assigns the sequence number. I want a way of automating that via a query off the "made" table. The "SEQ" field (in my original post) represents what the user manually enters in Excel... Much appreciated!

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    It can be derived in SQL but it will run like a dog I'm afradi. You sure you still want to do this? Is it to be displayed in a report or query? If the former it might be easier not to do it in SQL....
    Poots,

    How can you set sequential #'s to ordered rows in a query where you can't test/look at/remember the previous record??? Ah ... secondary function call that does the dirty work ....
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - didn't notice the repeated date for 4459. It chokes on this. Anyway - the SQL below. I think you need to go with Mike & VB though now unless you have another column that can be used for an equality check.
    Code:
    SELECT A.ID, A.TheDate, (SELECT COUNT(*) FROM DateT_Incr B WHERE A.ID = B.ID AND A.TheDate >= B.TheDate) AS TheNumber
    FROM DateT_Incr AS A
    ORDER BY A.ID, A.TheDate
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Poots,

    Very nice ... I should never discount subqueries ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    Poots,

    Very nice ... I should never discount subqueries ...
    Thakyou Mike I've got a bit of a crush on SQL at the mo - trying to use it as much as possible.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Aug 2006
    Posts
    7
    Poots,
    Once I realized I had to make a copy of table A (and name it DateT_Incr), the query worked perfectly. The "choking" issue is resolved because I force no duplicate dates for each ID... A dumb question, I know, but where/what is B. I initially thought I had to make a copy of A (like I did with DateT_Incr) and name it B, but I did not. Remember, I only begin to operate on the periphery of the programming world (obviously). Very seriously, however, I truly appreciate your knowledge and guidance...

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - no need to make copies of tables.

    Tables A and B are the same table. Their real name is DateT_Incr - this is just a table I chucked together to test the SQL (normally I air code but I know Mike's attention was on the thread so I had to make sure it worked ).

    So - what are A and B about? Because we use the same table in both the main query and a sub query we need to differentiate them. You do this by aliasing the tables. So - in the main query DateT_Incr is called A and in the subquery the table is called B - this way we can join them.

    Make sense?

    EDIT - BTW - the consequence of all this is you don't need to change your table at all. Change the query, replacing DateT_Incr with your table name and ID and TheDate with your field names.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Aug 2006
    Posts
    7
    I understand. Many thanks for assisting the novice. This saved a great deal of manual effort. Much appreciated!

  13. #13
    Join Date
    Aug 2009
    Posts
    2

    "runs like a dog"

    Hello...
    I had an almost identical challenge with the sequence number and found this post. It worked slick and I implemented it fairly easily. The only trouble I've run into is that I'm dealing with thousands of records and it runs extremely slow. My procedure imports a file, adds the sequence number and drops whatever data I don't need and then the final query results are exported to a text file (there are three subqueries to the one that is exporting). It's been running a little over an hour and all I've got so far is a 525 kb file and it's still running. Is there any way to speed this up?
    Initially the sequence number counted from one (using an autonumber assigned primary key) without restarting back at 1 for each unique category. When the procedure ran this way, it was very fast. But unfortunately that didn't meet their needs and I had to rework the sequence to restart at each category change.
    If anyone has any ideas, please let me know. Your feedback is greatly appreciated!!

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What are you exporting to? Text, excel?

    The "right" way to do this for performance reasons would be to rip through a recordset one record at a time and write it out to your file. Asking the jet-sql to do this for you will only result in tears and decidedly unprofessional usage of profanity.

    If it were me and this were a process that didn't need to happen very often, I'd make it a two-step process and export my base recordset out to excel. From there it's trivial to add the sequence ordinal since excel is aware of ALL values right up front. It knows what was in the row above any given cell and it will tell you anything you need to know. At that point it's a simple matter of checking whether the sequence number of the current row is different from the sequence number of the row above it. If it is, then it's easy to say the ordinal for the current row should be the ordinal for the previous row, + 1, else the ordinal should be 1.

    IE This assumes the sequence number is in A1 and the ordinal in A2, operating on the second row:

    =IF(A1 = A2, B1 + 1, 1)

    Then it's a simple matter of fill-down. End.
    oh yeah... documentation... I have heard of that.

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

  15. #15
    Join Date
    Aug 2009
    Posts
    2

    4600 records took 2.5 hours

    Thanks for your reply - Unfortunately this is a daily procedure dealing with thousands of records so the two part option won't work. I also can't leave this file in the hands of a user in Excel because the order is critical. I can't take a chance on them accidentally resorting or changing any of the data.

Posting Permissions

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