Results 1 to 3 of 3
  1. #1
    Moose is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Posts
    1

    Exclamation How to identify changes in data between consecutive rows (sorted in descending order by acct #)?

    I have data for multiple account numbers (for work) and dates, and I need to identify when there is a change in account number in order to add a new field with a count - which counts sequentially starting with 1 and then starts over at 1 when the account number changes.

    Please help! Thank you!

    Ex. The red column is what I am trying to create



    Account Date_To Counter
    12345 06/30/2015 1
    12345 05/31/2015 2
    12345 04/30/2015 3
    19999 06/30/2015 1

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You have two separate Domains, aka Columns, that you need to get information from before you can move forward with Appending or Updating a record. What is typically used in situations like this is a Domain function. For the "Counter" column, the function that comes to mind is the DMax() function.
    https://msdn.microsoft.com/en-us/lib.../ff835050.aspx

    I would probably retrieve the data for the other columns via a named and saved Query Object. Then, use the DMax() function to determine what the largest number for a given account is.

  3. #3
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    next, previous, first, last have no meaning without an order being applied - in the case of your sample data, the order is date descending so on that basis this pseudo code should do the job - change names to suit your table and field names

    Code:
    SELECT *, SELECT Count(*) FROM myTable as T WHERE Account=myTable.Account And Date>=myTable.Date) AS Counter
    FROM myTable
    ORDER BY Date Desc
    Note Date is a reserved word and should not be used as a field name

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

Similar Threads

  1. Replies: 6
    Last Post: 02-14-2015, 05:40 PM
  2. Subtrating Time in Consecutive Query Rows
    By andrebmsilva in forum Queries
    Replies: 1
    Last Post: 02-21-2013, 04:50 PM
  3. Identify "duplicated" rows
    By mkallover in forum Queries
    Replies: 7
    Last Post: 01-15-2013, 12:45 PM
  4. Make TEXTBOXES sort by DESCENDING order
    By taimysho0 in forum Programming
    Replies: 1
    Last Post: 12-05-2011, 04:52 PM
  5. Excel rows sorted when imported to Access
    By gwn in forum Import/Export Data
    Replies: 1
    Last Post: 06-15-2011, 08:34 AM

Tags for this Thread

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