none
add row number in add query RRS feed

  • Question

  • Hi

    I am working with a Temp Table updated by a Add Query.

    I want the Add Ouery to add "row numbers" in a specific field. I tried to use Count(fieldname) but it doesnt work. "

    You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)"

    What can I do?




    Best // Peter Forss Stockholm and Sigtuna GMT &#43;1.00

    Tuesday, October 25, 2016 7:47 PM

Answers

  • An autonumber will usually work, but cannot be relied on with complete confidence as it is designed to guarantee distinct values rather than sequential values.  For a method which computes the number as each row is inserted into the table see CustomNumber.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes an 'Insert Multiple Rows' option.  It works by executing a series of INSERT INTO statements to add each row independently rather than inserting all rows as a set operation.  In my demo the values to be inserted are selected from a multi-select list box and the code loops through its ItemsSelected collection; in your case I'm guessing you'd need to loop through a recordset, building the INSERT INTO statement at each iteration of the loop.

    Ken Sheridan, Stafford, England

    • Marked as answer by ForssPeterNova Wednesday, October 26, 2016 3:57 PM
    Tuesday, October 25, 2016 10:08 PM
  • Hi Hans

    What I want is to get the "row counter" to start from 1 every time I add rows to that table.
    My way of solving this is now to use a Create Table Query with one field as autonumber.
    Then I run the Add query to add the rest of the data.

    It works fine right now
    Ken wrote, below, it cannot be relied on.
    But when I have add data to the table I send it to a XML file.
    Then there is a XML software checking if the XML-file is right/valid, so I will get a warning if row number is out of sequential values. So fare, no issues :-)


    Best // Peter Forss Stockholm and Sigtuna GMT &#43;1.00

    • Marked as answer by ForssPeterNova Wednesday, October 26, 2016 3:57 PM
    Wednesday, October 26, 2016 3:56 PM

All replies

  • You could add an AutoNumber field to the temporary table. It will be populated automatically when you run the append query.

    If you don't want that: do you have a field with unique values, or two fields the combinations of whose values are unique in the data that you append?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, October 25, 2016 9:18 PM
  • An autonumber will usually work, but cannot be relied on with complete confidence as it is designed to guarantee distinct values rather than sequential values.  For a method which computes the number as each row is inserted into the table see CustomNumber.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes an 'Insert Multiple Rows' option.  It works by executing a series of INSERT INTO statements to add each row independently rather than inserting all rows as a set operation.  In my demo the values to be inserted are selected from a multi-select list box and the code loops through its ItemsSelected collection; in your case I'm guessing you'd need to loop through a recordset, building the INSERT INTO statement at each iteration of the loop.

    Ken Sheridan, Stafford, England

    • Marked as answer by ForssPeterNova Wednesday, October 26, 2016 3:57 PM
    Tuesday, October 25, 2016 10:08 PM
  • Hi Hans

    What I want is to get the "row counter" to start from 1 every time I add rows to that table.
    My way of solving this is now to use a Create Table Query with one field as autonumber.
    Then I run the Add query to add the rest of the data.

    It works fine right now
    Ken wrote, below, it cannot be relied on.
    But when I have add data to the table I send it to a XML file.
    Then there is a XML software checking if the XML-file is right/valid, so I will get a warning if row number is out of sequential values. So fare, no issues :-)


    Best // Peter Forss Stockholm and Sigtuna GMT &#43;1.00

    • Marked as answer by ForssPeterNova Wednesday, October 26, 2016 3:57 PM
    Wednesday, October 26, 2016 3:56 PM