Access SQL How to Make an Increment in Select Query

Access SQL how to make an increment in SELECT query

Alright, I guess this comes close enough to constitute an answer: the following link specifies two approaches: http://www.techrepublic.com/blog/microsoft-office/an-access-query-that-returns-every-nth-record/

The first approach assumes that you have an ID value and uses DCount (similar to @mikeY's solution).

The second approach assumes you're OK creating a VBA function that will run once for EACH record in the recordset, and will need to be manually reset (with some VBA) every time you want to run the count - because it uses a "static" value to run its counter.

As long as you have reasonable numbers (hundreds, not thousands) or records, the second approach looks like the easiest/most powerful to me.

Using a MS Access query to auto increment my primary key

I worked it out myself. If anybody has a similar problem, it was the order of the words that was the problem.

ALTER TABLE Unified_Backlog_Shipping
ADD P_Key AUTOINCREMENT PRIMARY KEY;

How can I increment a field value by 1 using SQL Query in MS Access

Not sure about the variable aspect mentioned, but bumping the VisitingNumber count should be able to be done as:

UPDATE $vDiagnosis$ 
   SET DateOfVisit = '$vDateOfVisit$',
       Condition = '$vCondition$',
       VisitingNumber = VisitingNumber + 1
 WHERE ID = '$vID$' 

To the insert, the value for the visitingNumber could simply be 1 without a variable, like so:

INSERT INTO $vDiagnosis$ 
VALUES ('$vID$','$vFirstName$','$vLastName$','$vGender$','$vPhone$','$vAge$',
'$vDateOfVisit$','$vCondition$','$vInsuranceCo$','$vInsuranceNr$', 1)

Or better, perhaps a default value of 1 could be specified at the table level, and then it would not need to be included with the insert query at all. (it would be set to 1 at insert)

Hope that makes sense.

How to generate auto increment field in select query

If it is MySql you can try

SELECT @n := @n + 1 n,
       first_name, 
       last_name
  FROM table1, (SELECT @n := 0) m
 ORDER BY first_name, last_name

SQLFiddle

And for SQLServer

SELECT row_number() OVER (ORDER BY first_name, last_name) n,
       first_name, 
       last_name 
  FROM table1 

SQLFiddle

MS Access - How to Increment number in a different table?

You can't do that directly inside the table in MS Access. In MS Excel you can create a formula that counts the TRUE-FALSE flags in a table and gives that count using COUNTIFS but there is no option to do that directly in a table field in Access (writing a formula). Access is not working like that.

You need a SQL query in order to do what you want. However MS Access doesn't allow aggregation functions on UPDATE queries and that means you cannot use GROUP BY with UPDATE.

What you could do is write a macro that calls 3 different SQL queries to do what you want in a similar workaround way

We have the following example:

I will use the Tables:
Table1(ID, Name As Text, Check AS Boolean)
Table2(ID, Name_Total as Text, Total as Integer)

I have created and saved the following Queries:

Query1

SELECT * INTO Temp_Table
FROM 
    (SELECT Name, Count(Name) AS Count_Total 
    FROM Table1, Table2 
    WHERE Name = Name_Total AND Check = True GROUP BY Name) AS [Counter];

Query2

UPDATE Table2 
INNER JOIN Temp_Table ON Table2.Name_Total = Temp_Table.Name 
SET Total = Count_Total;

Query3

DROP TABLE Temp_Table;

The first query creates a temporary table that counts in Table1 for every name how many times appears in the table.
The second Query updates the Total Values on Table2 with the values found and put on the Temp_Table
The third query just deletes the Temp_Table

Every time you want to update your table2, you have to run those queries in that order.

Now you just have to put that queries (in correct order) in a macro and run that macro manual or on Database open or whatever you want it to run (when you press a button on a Form)

It is not exactly what you wanted but I hope it will help working around your problem.

How to generate incremental numbers in query, Access 2000?

You can use this function which if for exactly the purpose. See in-line comments for typical usage:

Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long

' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String

  On Error GoTo Err_RowCounter

  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If

  RowCounter = col(strKey)

Exit_RowCounter:
  Exit Function

Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

---

Related Topics