Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2011
    Posts
    6

    Unanswered: Assigning Numbers to Records - 1,2,3,1,2,3...

    In a query I would like to insert a new column and assign numbers to the records in the output. I would like the first record to be assigned a 1, the second a 2, the third a 3 and, on the fourth, it resets to 1. Can anyone help with this. The purpose of this is so, when building a report, I can have all the records labeled "1"s appear in one place on the report, the "2"s in another...

    Can someone help out with this?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you have a column with an increasing value (ex. an autonumber), or a column with unique values that you can sort in an increasing order, here is a possibility.

    In this example the existing column SysCounter is defined of autonumber type and the count column (1,2,3,1...) receives Cycle3 as alias (i.e. Cycle3 is the name of the computed column):
    Code:
    SELECT *,
      (SELECT iif((count(SysCounter) mod 3) =0,3,(count(SysCounter) mod 3))
      FROM Table1
      WHERE Table1.SysCounter <= T1.SysCounter) AS Cycle3
    FROM Table1 AS T1;
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    6
    Thanks for the reply. It is probably something that I am doing incorrectly, but the Cycle3 column is all 3s. Below is the coding I put into the query:

    SELECT *,
    (SELECT iif((count(rowNum) mod 3) =0,3,(count(rowNum) mod 3))
    FROM Participant_List_Open_Qry_3
    WHERE Participant_List_Open_Qry_3.rowNum <= Participant_List_Open_Qry_3.rowNum) AS Cycle3
    FROM Participant_List_Open_Qry_3 AS Participant_List_Open_Qry_3;

    Thoughts?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    WHERE Participant_List_Open_Qry_3.rowNum <= Participant_List_Open_Qry_3.rowNum) AS Cycle3
    FROM Participant_List_Open_Qry_3 AS Participant_List_Open_Qry_3;
    This is incorrect. You must use an alias for one of the references to the table or query.

    Read the example again:
    Code:
    SELECT *, 
     (SELECT iif((count(SysCounter) mod 3) =0,3,(count(SysCounter) mod 3))
      FROM Table1
      WHERE Table1.SysCounter <= T1.SysCounter) AS Cycle3
    FROM Table1 AS T1;
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    6
    Thanks for the reply.

    When I create an alias, do I have to do anything but put an alias name in the query?

    I changed my query to the below and when I try to run it I get an "Invalid Argument to Function" error:

    SELECT *,
    (SELECT iif((count(rowNum) mod 3) =0,3,(count(rowNum) mod 3))
    FROM Participant_List_Open_Qry_3
    WHERE Participant_List_Open_Qry_3.rowNum <= Qry_3.rowNum) AS Cycle3
    FROM Participant_List_Open_Qry_3 AS Qry_3;

    Thanks so much for your help.

  6. #6
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Is what you are calling 'rowNum' and what Sinndo calls 'SysCounter' at least a number field within your table?

    This is the only thing that I can see would be breaking the code - everything else is structure correctly.

  7. #7
    Join Date
    Jul 2011
    Posts
    6
    roNum is built from a DCount query. Could that be the problem?

  8. #8
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I don't think it would cause the 'invalid arguement' that you are getting but it could certainly cause a problem if the count happens to generate the same values...

    Do you need it to be a DCount? As an experiment, Just assign it to the primary key field of your table and see how they works.

  9. #9
    Join Date
    Jul 2011
    Posts
    6
    Seems to be something with running it off of a query. When I make a table and run it from the table it works fine.

    However, I would like it all to run from a query so I don't have to make sure I re-run the make-table query each time.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can't you include this SQL statement in the original query, or create a query with subqueries merging everything together?
    Have a nice day!

  11. #11
    Join Date
    Jun 2011
    Posts
    10
    Could you do something with the Autonumber? You could potentially base a formula on this to return 1 - 3.

    For example. Autonumbers will go up 1 at a time, dividing by three will always give a number that ends in .3333, .6666 or .0 in order these decimals could be identified in a query formula to give 1, 2 or 3?

    Deleting records could throw it out though as would sorting records for any reason in a query.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Stream62 View Post
    Could you do something with the Autonumber? You could potentially base a formula on this to return 1 - 3.

    For example. Autonumbers will go up 1 at a time, dividing by three will always give a number that ends in .3333, .6666 or .0 in order these decimals could be identified in a query formula to give 1, 2 or 3?
    This is precisely what the Modulo operation does !
    Have a nice day!

  13. #13
    Join Date
    Jul 2011
    Posts
    6
    I can't seem to get this to work. However, I think I have found a different methodology to accomplish my end goal.

    Thank you for all your help.

    Jon

Posting Permissions

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