UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Make An Auto-increment Field In A Query, Office 2007    
 
   
accesshawaii
post Jun 29 2012, 09:27 AM
Post #1

UtterAccess VIP
Posts: 5,046
From: From Hawaii - Now in Wisconsin...Am I Nuts?



I remember reading something on this awhile back but can't remember where. I need to make an auto-increment # in a query. Ok, Ok, Ok, I know doing it at the table level is the way to go but in this instance, I can't do that. The tables come from SQL Server. The reason that I need to have a unique identifier such as this is because this data is displayed in a list-box, which contains thousands of records. In order to make it a little easier on the user, I have a combo-box where they can select the record they need and it will highlight that record in the list-box to include it in the criteria. As it is now, it will not work because there is no unique identifier.
Since it is using SQL table, I can use regular SQL query language e.g. Select Cases, etc. I have tried researching if there is a way to increment a # in a SQL query and have not been able to find anything. Any assistance would be appreciated.
Go to the top of the page
 
+
MiltonPurdy
post Jun 29 2012, 09:36 AM
Post #2

UtterAccess Ruler
Posts: 1,651
From: Arkansas



I think you may be able to use a Dcount
Count("*";"QuerySource";"Key<" & Key)
Go to the top of the page
 
+
accesshawaii
post Jun 29 2012, 10:17 AM
Post #3

UtterAccess VIP
Posts: 5,046
From: From Hawaii - Now in Wisconsin...Am I Nuts?



Thanks for the reply. How exactly would that work? What would I use for the "Key" field? I could see where this might work if there was sequential #s but I don't have that, that's what I need to produce.
Go to the top of the page
 
+
MiltonPurdy
post Jun 29 2012, 11:07 AM
Post #4

UtterAccess Ruler
Posts: 1,651
From: Arkansas



I am probably in left field, but couldn't you do dcount( .... ) as key?
It would not be a field pulled in from a table but a new field in the query.
Go to the top of the page
 
+
accesshawaii
post Jun 29 2012, 11:53 AM
Post #5

UtterAccess VIP
Posts: 5,046
From: From Hawaii - Now in Wisconsin...Am I Nuts?



I just had an epithamy, which might make this easier to explain. Actually, I was wrong. This will be Access based, so can't use the regular SQL functions. I do remember how to do this in SQL, I actually did do this a little while back but just clicked with me now. In SQL, it's the row_number() function. So, what I need is the equivalent to that in Access. I was almost thinking something like Select Top 100 from blah blah would work but not sure on how to do the increment.
Go to the top of the page
 
+
LPurvis
post Jun 29 2012, 11:56 AM
Post #6

UtterAccess Editor
Posts: 14,758
From: England (North East / South Yorks)



Hi Dan
Am I misunderstanding - are you saying that there's no particular ordering to these rows at all anyway? (Which would make selection a bit of a nightmare indeed.)
If there is - then there's rhyme and reasoning to the ordering and therefore an assigned ID.
But this is a transient (temporary) ID purely for the purposes of this single selection from a list?
If there's order - then there's sense to the incremental numbering. If not - then it is random - but since only for the purposes of this single form instance, that's OK?
>> The reason that I need to have a unique identifier such as this is because this data is displayed in a list-box, which contains thousands of records.
I would question the sheer number of rows in the list. (Couldn't the earlier control be a pre-filter rather than jump-to functionality?)
Since this is a SQL sourced list and you are able to run T-SQL then you can easily create on the fly numbering. (row_number() springs to mind)
Cheers.
Go to the top of the page
 
+
LPurvis
post Jun 29 2012, 11:59 AM
Post #7

UtterAccess Editor
Posts: 14,758
From: England (North East / South Yorks)



There is no equivalent in Jet/ACE. You'll need a function - something like Serialize which, by comparison, is packed with overhead.
However - if you filter first - then your listbox is containing and sorting over fewer rows. Thousands of rows in a Listbox is hard to justify (and use).
Cheers.
Go to the top of the page
 
+
LPurvis
post Jun 29 2012, 12:06 PM
Post #8

UtterAccess Editor
Posts: 14,758
From: England (North East / South Yorks)



Or, as mentioned, use a Count (DCount or subquery) - but you'll still need that ordering then. A set of fields by which to guarantee the order of rows and so make sure to the incrementing ID/Count.
(You don't have to take only one column by which to perform a count upon.)
Go to the top of the page
 
+
accesshawaii
post Jun 29 2012, 12:38 PM
Post #9

UtterAccess VIP
Posts: 5,046
From: From Hawaii - Now in Wisconsin...Am I Nuts?



Hi Leigh,
The order in which it increments does not matter so much, I just need that as an identifier. I do have it ordered on a field of course, so there is some organization to it. I'm still not getting how to use the DCount to do this.
Let's say that my query is called "Query1" and I have something like this.
SELECT Field1, Field2, Field3
FROM MyTable
Orderbr />The order in which it increments does not matter so much, I just need that as an identifier. I do have it ordered on a field of course, so there is some organization to it. I'm still not getting how to use the DCount to do this.
Let's say that my query is called "Query1" and I have something like this.
SELECT Field1, Field2, Field3
FROM MyTable
ORDER BY Field1
How would I use the Dcount in that?
Go to the top of the page
 
+
LPurvis
post Jun 29 2012, 12:41 PM
Post #10

UtterAccess Editor
Posts: 14,758
From: England (North East / South Yorks)



Hi. Vaguely...
SELECT Field1, Field2, Field3, DCount("*", "MyTable", "Field1 < " & Field1 & " AND Field2 < " & Field2 & " AND Field3 < " & Field3) As OrderCol
FROM MyTable
Orderly...
SELECT Field1, Field2, Field3, DCount("*", "MyTable", "Field1 < " & Field1 & " AND Field2 < " & Field2 & " AND Field3 < " & Field3) As OrderCol
FROM MyTable
ORDER BY Field1, Field2, Field3
Not a fan of it at all though to be honest.
Go to the top of the page
 
+
accesshawaii
post Jun 29 2012, 12:47 PM
Post #11

UtterAccess VIP
Posts: 5,046
From: From Hawaii - Now in Wisconsin...Am I Nuts?



I know that there are better ways of doing this such as if I just did a recordset in VBA to grab SQL that way but I don't have the option in this case, so kind've having to work with the hand that I was dealt.
So, I would need to do that for each field? That was just a sample. Theres more fields than that.
Go to the top of the page
 
+
Gustav
post Jun 29 2012, 05:11 PM
Post #12

UtterAccess VIP
Posts: 2,163



Yes, a better (= way faster) way is to use a collection:
CODE
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.
nbsp; 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

/gustav
Go to the top of the page
 
+
MadPiet
post Jun 30 2012, 09:25 PM
Post #13

UtterAccess Ruler
Posts: 1,133



Just wondering, but can you just use a pass-through query that uses ROWNUMBER(). If you write a pass-through, it's executed on the backend (SQL Server), and then the results are returned. the local data engine ignores the SQL completely. That should work, right?
Go to the top of the page
 
+
LPurvis
post Jul 1 2012, 03:54 AM
Post #14

UtterAccess Editor
Posts: 14,758
From: England (North East / South Yorks)



That was the original idea yes. But then Dan said
"This will be Access based, so can't use the regular SQL functions."
assume, since Row_Number() was ruled out at that stage, that we're even talking about the BE here.
Go to the top of the page
 
+
shoei20
post Jul 5 2012, 09:34 AM
Post #15

UtterAccess Addict
Posts: 101



Hello,
I am trying to do something simular in a query, basically count field and reset the count as the defined field changes.
Field that changes: Mission
Table that has data: MissionComplete
My attempt: Expr2: DCount([Mission],[MissionComplete!],[Mission]=[Mission])
Go to the top of the page
 
+
accesshawaii
post Jul 6 2012, 06:24 AM
Post #16

UtterAccess VIP
Posts: 5,046
From: From Hawaii - Now in Wisconsin...Am I Nuts?



Thanks all for the replies, got it working. I totally agree with several of you who had said about doing this as a SQL based query, which is the way that I always do it when I have that option whether it be through recordsets or pass-throughs. In this instance, I'm working with a database that has already been completely built and just needs some enhancements. All the SQL tables are linked tables (Somethng that I personally woldn't do) but was one of those cases where had to work with I got, which I'm sure all of you have had to do at one time or another as well. Thanks again.
Go to the top of the page
 
+
genoma111
post Jul 6 2012, 07:58 AM
Post #17

UtterAccess Ruler
Posts: 2,018
From: Bogotá - Colombia



PMFYI,
Is the value of that field unique for the record?
If so, this simple function I created some time ago, will do the trick.
Please note that I'm assuming that the field you use for ordering is a numeric.
Use:
SQL
Expr1: RowNumber("ID",[ID],"Query7")

Query7 is the same query I'm using.
Save the query before running it, otherwise this won't work
CODE
Public Function RowNumber(YourFieldN As String, _
                          YourFieldV As Long, _
                      qry As String) As Long
Dim rst As DAO.Recordset
Dim lngVA As Long
On Error Resume Next
    
    Set rst = CurrentDb.OpenRecordset(qry, dbOpenDynaset)
    With rst
        .FindFirst "[" & YourFieldN & "] = " & YourFieldV
         lngVA = .AbsolutePosition + 1
    End With
        
RowNumber = lngVA
End Function

Change the declaration according to the datatype:
CODE
'Number
YourFieldV As Long,
'Text:
YourFieldV As String,
'Date:
YourFieldV As Date,

And change this line
CODE
'Number
.FindFirst "[" & YourFieldN & "] = " & YourFieldV
'Text:
.FindFirst "[" & YourFieldN & "] = '" & YourFieldV & "'"
'Date:
.FindFirst "[" & YourFieldN & "] = #" & YourFieldV & "#"

Regards,
Diego
Go to the top of the page
 
+
accesshawaii
post Jul 6 2012, 08:31 AM
Post #18

UtterAccess VIP
Posts: 5,046
From: From Hawaii - Now in Wisconsin...Am I Nuts?



Hi Dipete,
Thanks for the response. There was no unique identifer at all, that's where the problem layed because I have a function where the user will select a record from a combo-box and then the corresponding record in the listbox will be highlighted and included in the criteria. In order to do that, I needed some type of unique identifier to reference.
Go to the top of the page
 
+
genoma111
post Jul 6 2012, 08:36 AM
Post #19

UtterAccess Ruler
Posts: 2,018
From: Bogotá - Colombia



Hi Dan,
There is a trick that I use similar to the one I posted before but for adding a "counter" directly in a listbox.
Before posting it, may I ask you to explain a little bit the data that is contained in your combobox?
How do the data in a combobox relate to the data of the listbox?
If there are no unique Identifiers, can you build a unique "composed" identifier for every record?
Regards,
Diego
Edit:
If you can identify your records using several fields from a table or tables, you can probably do something like this:
CODE
Public Function RowNumber(YourFieldN1 As String, _
                          YourFieldV1 As String, _
                          YourFieldN2 As String, _
                          YourFieldV2 As Long, _
                                  qry As String) As Long
Dim rst As DAO.Recordset
Dim lngVA As Long
On Error Resume Next
    
    Set rst = CurrentDb.OpenRecordset(qry, dbOpenDynaset)
    With rst
        .FindFirst "[" & YourFieldN1 & "] = '" & YourFieldV1 & "' AND [" & YourFieldN2 & "] =" & YourFieldV2
         lngVA = .AbsolutePosition + 1
    End With
        
RowNumber = lngVA
End Function

SQL
Expr1: RowNumber("CapituloNo",[CapituloNo],"GrupoID",[GrupoID],"Query3")

The fields shown belong to different tables.
  • CapituloNo = String (table1)
  • GrupoID = Number (table2)

Since FindFirst is like the Where clause of a query, you can add a lot more fields in case you need it.
Note: This approach doesn't work with calculated fields.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 19th September 2014 - 06:54 PM