My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() Post#1 | |
UtterAccess VIP Posts: 5,161 Joined: 17-June 04 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. |
![]() Post#2 | |
![]() Posts: 1,655 Joined: 3-May 10 From: Arkansas ![]() | I think you may be able to use a Dcount Count("*";"QuerySource";"Key<" & Key) |
![]() Post#3 | |
UtterAccess VIP Posts: 5,161 Joined: 17-June 04 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. |
![]() Post#4 | |
![]() Posts: 1,655 Joined: 3-May 10 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. |
![]() Post#5 | |
UtterAccess VIP Posts: 5,161 Joined: 17-June 04 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. |
![]() Post#6 | |
![]() UtterAccess Editor Posts: 16,264 Joined: 27-June 06 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. |
![]() Post#7 | |
![]() UtterAccess Editor Posts: 16,264 Joined: 27-June 06 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. |
![]() Post#8 | |
![]() UtterAccess Editor Posts: 16,264 Joined: 27-June 06 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.) |
![]() Post#9 | |
UtterAccess VIP Posts: 5,161 Joined: 17-June 04 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? |
![]() Post#10 | |
![]() UtterAccess Editor Posts: 16,264 Joined: 27-June 06 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. |
![]() Post#11 | |
UtterAccess VIP Posts: 5,161 Joined: 17-June 04 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. |
![]() Post#12 | |
UtterAccess VIP Posts: 2,163 Joined: 21-February 07 ![]() | 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 |
![]() Post#13 | |
Posts: 2,412 Joined: 27-February 09 ![]() | 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? |
![]() Post#14 | |
![]() UtterAccess Editor Posts: 16,264 Joined: 27-June 06 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. |
![]() Post#15 | |
Posts: 101 Joined: 17-October 03 ![]() | 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]) |
![]() Post#16 | |
UtterAccess VIP Posts: 5,161 Joined: 17-June 04 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. |
![]() Post#17 | |
![]() Posts: 2,018 Joined: 2-June 09 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 |
![]() Post#18 | |
UtterAccess VIP Posts: 5,161 Joined: 17-June 04 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. |
![]() Post#19 | |
![]() Posts: 2,018 Joined: 2-June 09 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.
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. |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 22nd April 2018 - 03:47 AM |