Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2006
    Posts
    5

    Continuous Counting on Grouping /Start renumbing on next ID

    I've been stuggling with this for days and I can't seem to find a solution online, however I've found similar references but can't get them to work. I tired searching this forum and can't provide the proper key words to find a sample. I would greatly appreciate any assistance. I'm using Access 2000 and working off a query: I have a query called "Provider And Address Detail Combine Q". This query qroups all doctors by their PPOOK_PROV_ID and Full Address and I'm looking for a continuous count of the address grouped by PPOOK_PROV_ID. The object is to provide each Address (FullAddress) with a location number for each provider. Below is the results I like to obtain.



    PPOOK_PROV_ID, FullAddress, Count (location#)
    123456, 123 Fourth St, 1
    123456, 9 Nineth St 2,
    123456, 1 Hunderd Ave, 3
    123456, 19 Seventy Blvd., 4
    987654, 98 Hunters Lane, 1
    987654, 1 Four the Road, 2

    Thank you in advance for taking the time to help me.

  2. #2
    StepUP is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Dec 2005
    Posts
    99
    Not that easy to do in a query (although not impossible), but very easy to do in a report. Just use grouping and the Running Sum property.

    Will a report it your needs?

  3. #3
    Join Date
    Jan 2006
    Posts
    5
    Quote Originally Posted by StepUP
    Not that easy to do in a query (although not impossible), but very easy to do in a report. Just use grouping and the Running Sum property.

    Will a report it your needs?
    I was able to do it in a report however I need to accomplish a delimited export from a query. Thanks.

  4. #4
    StepUP is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Dec 2005
    Posts
    99
    Then I would just export the report out to a text or Excel file. Otherwise you would have to write some code. Might be a little tricky.

  5. #5
    Join Date
    Jan 2006
    Posts
    5
    Quote Originally Posted by StepUP
    Then I would just export the report out to a text or Excel file. Otherwise you would have to write some code. Might be a little tricky.


    Thanks, I guess that is what I will need to do. I appreciate your time!

  6. #6
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    JJ,

    It's ugly, but assuming you are drawing from a single table, it works:

    SELECT tblRunningSum.PPOOK_PROV_ID, tblRunningSum.FullAddress, (SELECT Count(T.FullAddress) FROM tblRunningSum AS T WHERE T.PPOOK_PROV_ID = tblRunningSum.PPOOK_PROV_ID AND T.FullAddress <= tblRunningSum.FullAddress) AS TheCount
    FROM tblRunningSum
    ORDER BY tblRunningSum.PPOOK_PROV_ID, tblRunningSum.FullAddress;

    Even so, if it were me, I'd do it in a report.

    Patrick

  7. #7
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    BTW, you could achieve the same result using DCount instead of the subquery, but the subquery runs considerably faster than DCount...

    SELECT tblRunningSum.PPOOK_PROV_ID, tblRunningSum.FullAddress, DCount("[FullAddress]", "[tblRunningSum]", "[PPOOK_PROV_ID] = " & [PPOOK_PROV_ID] & " AND [FullAddress] <= '" & [FullAddress] & "'") AS TheCount
    FROM tblRunningSum
    ORDER BY tblRunningSum.PPOOK_PROV_ID, tblRunningSum.FullAddress;

  8. #8
    Join Date
    Jan 2006
    Posts
    5
    Quote Originally Posted by matthewspatrick
    BTW, you could achieve the same result using DCount instead of the subquery, but the subquery runs considerably faster than DCount...

    SELECT tblRunningSum.PPOOK_PROV_ID, tblRunningSum.FullAddress, DCount("[FullAddress]", "[tblRunningSum]", "[PPOOK_PROV_ID] = " & [PPOOK_PROV_ID] & " AND [FullAddress] <= '" & [FullAddress] & "'") AS TheCount
    FROM tblRunningSum
    ORDER BY tblRunningSum.PPOOK_PROV_ID, tblRunningSum.FullAddress;
    Patrick,

    It worked perfectly! I can’t thank you enough! I GREATLY appreciate your time and expertise!!

    Thank you very much!

    JJ

  9. #9
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    JJ,

    I'm glad it worked out for you. BTW, if you used the DCount version, I would like very much to know if you tried the subquery version, and if there were a noticeable difference in speed. (If you have more than a few dozen records, the subquery version should be faster.)

  10. #10
    Join Date
    Jan 2006
    Posts
    5
    Quote Originally Posted by matthewspatrick
    JJ,

    I'm glad it worked out for you. BTW, if you used the DCount version, I would like very much to know if you tried the subquery version, and if there were a noticeable difference in speed. (If you have more than a few dozen records, the subquery version should be faster.)
    Patrick,

    I did use the DCount version for my query. I had 4550 records and used a subquery and it only takes <2 seconds to return the results. Thanks again for providing me the solution and relieving me of my headache.

    JJ

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

Similar Threads

  1. Replies: 3
    Last Post: 09-06-2008, 10:19 AM
  2. Replies: 0
    Last Post: 08-19-2008, 11:12 AM
  3. Continuous Range of Records
    By tigers in forum Access
    Replies: 0
    Last Post: 10-10-2007, 08:36 AM
  4. Replies: 1
    Last Post: 12-13-2005, 08:07 AM
  5. auto start/stop time
    By j0ker21m in forum Reports
    Replies: 1
    Last Post: 12-10-2005, 08:42 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