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?

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):

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?

This is incorrect. You must use an alias for one of the references to the table or query.

Read the example again:

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.

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.

roNum is built from a DCount query. Could that be the problem?

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.

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.

Can't you include this SQL statement in the original query, or create a query with subqueries merging everything together?

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.

This is precisely what the Modulo operation does !

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