UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Insert Incrementing Number Into Query Result, Access 2013    
 
   
gint32
post Mar 5 2019, 04:00 PM
Post#1



Posts: 368
Joined: 8-May 09
From: Australia


Hi everyone I am wondering if its possible to insert an incrementle number into a querys results, meaning for each record found in the query > update that specific record to SomeTable.NumberField = 1, then the next found to = 2 etc etc




Go to the top of the page
 
June7
post Mar 5 2019, 04:12 PM
Post#2



Posts: 648
Joined: 25-January 16



This can be done with an UPDATE action or with VBA to add value to table. Requires use of nested subquery or DCount() aggregate function. Post sample data.

Or on a report use textbox RunningSum property to dynamically calculate this increment.




--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Jeff B.
post Mar 5 2019, 08:04 PM
Post#3


UtterAccess VIP
Posts: 10,259
Joined: 30-April 10
From: Pacific NorthWet


As <June7> points out, using a report's Running Sum property would be simpler. Is there a reason you need to have the 'running sum' ONLY in a query?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
gint32
post Mar 6 2019, 12:58 AM
Post#4



Posts: 368
Joined: 8-May 09
From: Australia


CODE
Re -  Post sample data.


Thanks for your input
Below is a stripped down query, to the bare min just to reflect my goal. the field I'd like to insert the sequential numbering into would be "Attend.SortOrder_ID", as you can see in this instance the SQL taken from the design view of a query sorts and filters on the currently open form.


CODE
SELECT Attend.AttDate, Tbl_Employees.Surname, Tbl_Employees.FirstName, Attend.SortOrder_ID
FROM Tbl_ReasonTypes RIGHT JOIN ((WorkLocations INNER JOIN (Tbl_Employee_Types INNER JOIN Tbl_Employees ON Tbl_Employee_Types.Employee_Type_ID = Tbl_Employees.Employee_Type_ID) ON WorkLocations.WorkID = Tbl_Employees.WorkID) INNER JOIN (Attend INNER JOIN AttendTypes ON Attend.AttType = AttendTypes.AttType) ON Tbl_Employees.EmployeeID = Attend.AttEmployee) ON Tbl_ReasonTypes.Reason_ID = Attend.Reason_ID
WHERE (((Attend.AttDate)=[Forms]![LT_Board]![txt_LTDate]))
ORDER BY Attend.AttDate;

QUOTE
re- Is there a reason you need to have the 'running sum' ONLY in a query?


Yes, the reason is this database is for allocating O/T to willing employees, and with the query supplied above (though this is not the full query) O/T (when available) will be allocated to some but not all employees on any given date = "Attend.AttDate=[Forms]![LT_Board]![txt_LTDate]" on a first in best dressed order, so in the interests of fairness, our company needs to record where they are in the list (as in the order) and if or not allocated O/T where they were on the list and then write this data to an event log for archiving and audit purposes.

So I need a way of collecting where each employee was on the list and also why they did or didn't get O/T issued (a lot of bickering and complaints of O/T being firstly issues to favorites) before some other that was also on the list for O/T on any given day, So basically a report will not suffice as users would be printing out all day, and also I need to write to an event log.
so I kind of thought it needs to be done via SQL recordset till EOF or temp make table with a field for auto-numbering or ??

This post has been edited by gint32: Mar 6 2019, 01:02 AM
Go to the top of the page
 
June7
post Mar 6 2019, 01:22 AM
Post#5



Posts: 648
Joined: 25-January 16



Examples of two ways to calculate an incrementing value in query:

SELECT Umpires.Gender, Umpires.LastN, DCount("*","Umpires","Gender='" & [Gender] & "' AND UmpID<" & [UmpID])+1 AS GrpSeq
FROM Umpires
ORDER BY Umpires.Gender, Umpires.LastN;

SELECT Umpires.Gender, Umpires.LastN (SELECT Count(*) AS GrpSeq FROM Umpires AS Dupe WHERE Dupe.Gender=Umpires.Gender AND Dupe.UmpID<Umpires.UmpID)+1 AS GrpSeq
FROM Umpires
ORDER BY Umpires.Gender, Umpires.LastN;

The first version can be used in an UPDATE action. The second cannot.

Alternative is VBA opening and manipulating recordset.

This post has been edited by June7: Mar 6 2019, 01:58 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
gint32
post Mar 6 2019, 02:02 AM
Post#6



Posts: 368
Joined: 8-May 09
From: Australia


QUOTE
re two examples



QUOTE
SELECT Umpires.Gender, Umpires.LastN, DCount("*","Umpires","Gender='" & [Gender] & "' AND UmpID<" & [UmpID])+1 AS GrpSeq


Thanks for that, my grey matter is fried i guess that comes with being 60+, I am trying to get my head around whats actually happening within your examples

stick with me on this, but I can't see where if I implementing your suggestions it would actually update my filtered fields "Attend.SortOrder_ID" or would I then need to pipe into another update query the values from GrpSeq into "Attend.SortOrder_ID"

I was thinking something along the lines of the below (although if I could write the exact syntax I'd have done it ) but I'm useless at scripting SQL and dealing with recordsets.

CODE
Dim I as Integer

I = 1

Do While Not rst.EOF


With rstAttend ' or if possible use my preferred query here

    .Edit
    ![ Attend.SortOrder_ID] = I
    .Update

End With

    For I = I + 1 ‘ increment


   rst.MoveNext


Loop

Go to the top of the page
 
June7
post Mar 6 2019, 02:41 AM
Post#7



Posts: 648
Joined: 25-January 16



The query I posted would be source for another query for UPDATE action.

Your pseudocode is on right track, as long as the query you build is editable.

Need to declare and set recordset object.

Not sure why you show two recordset variables.





--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
gint32
post Mar 6 2019, 02:54 AM
Post#8



Posts: 368
Joined: 8-May 09
From: Australia


thanks...where the two recordset variables, not sure what you mean
Go to the top of the page
 
June7
post Mar 6 2019, 03:02 AM
Post#9



Posts: 648
Joined: 25-January 16



rst and rstAttend are variables.
CODE
Dim rst As DAO.Recordset
Dim I as Integer
Set rst = CurrentDb.OpenRecordset("your query name")
I = 1
Do While Not rst.EOF
    With rst
        .Edit
        !SortOrder_ID = I
        .Update
    End With
    I = I + 1 ‘ increment
    rst.MoveNext
Loop

This post has been edited by June7: Mar 6 2019, 03:10 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
isladogs
post Mar 6 2019, 03:27 AM
Post#10


UtterAccess VIP
Posts: 1,451
Joined: 4-June 18
From: Somerset, UK


In case it helps, see this article for another approach to a 'ranking' query using the Serialize function Rank Order Queries
This post has been edited by isladogs: Mar 6 2019, 03:30 AM

--------------------
Go to the top of the page
 
gint32
post Mar 6 2019, 03:44 AM
Post#11



Posts: 368
Joined: 8-May 09
From: Australia


Re serialize..thanks i had a look at this option before and once again today. I still cant get my head completely around it. though thanks for the suggestion anyway. Each peice of code would need to be baby stepped for my little brain.
Go to the top of the page
 
isladogs
post Mar 6 2019, 04:03 AM
Post#12


UtterAccess VIP
Posts: 1,451
Joined: 4-June 18
From: Somerset, UK


Have a look at the examples using serialize in the database on that web page. Its very easy once you've done it once

--------------------
Go to the top of the page
 
gint32
post Mar 6 2019, 04:31 AM
Post#13



Posts: 368
Joined: 8-May 09
From: Australia


Thanks. I will have another play with the download again..although it has be said running one query is difficult enough for me without having to pipe one queries results into another query
This post has been edited by gint32: Mar 6 2019, 04:38 AM
Go to the top of the page
 
isladogs
post Mar 6 2019, 05:47 AM
Post#14


UtterAccess VIP
Posts: 1,451
Joined: 4-June 18
From: Somerset, UK


OK good luck. Shout if you need some help.
Its not always necessary to do a second query to manage the ranking

--------------------
Go to the top of the page
 
isladogs
post Mar 6 2019, 05:47 AM
Post#15


UtterAccess VIP
Posts: 1,451
Joined: 4-June 18
From: Somerset, UK


OK good luck. Shout if you need some help.
Its not always necessary to do a second query to manage the ranking though it may be easier to do so.
This post has been edited by isladogs: Mar 6 2019, 05:47 AM

--------------------
Go to the top of the page
 
gint32
post Mar 6 2019, 05:56 AM
Post#16



Posts: 368
Joined: 8-May 09
From: Australia


re-rst and rstAttend are variables. Code

CODE
Set rst = CurrentDb.OpenRecordset("Myqry???")


I just discovered that as soon as any query contents reference an open forms value it jumps straight out of it and will not action any further. So I'm stuck with this option at the moment
This post has been edited by gint32: Mar 6 2019, 05:56 AM
Go to the top of the page
 
isladogs
post Mar 6 2019, 06:03 AM
Post#17


UtterAccess VIP
Posts: 1,451
Joined: 4-June 18
From: Somerset, UK


Sorry no idea what you mean.
However you solve this, you should only ever do a ranking in a SELECT query.
Doing so in an UPDATE query makes no sense as you're saving a calculated value and that rank will change as the underlying data changes.

--------------------
Go to the top of the page
 
gint32
post Mar 6 2019, 06:16 AM
Post#18



Posts: 368
Joined: 8-May 09
From: Australia


re- june 7 suggestin
CODE
Dim rst As DAO.Recordset
Dim I as Integer
Set rst = CurrentDb.OpenRecordset("your query name") '''only works id the under lying query does not reference a value form an open form and in my case it does so hence doen't run
I = 1
Do While Not rst.EOF
    With rst
        .Edit
        !SortOrder_ID = I
        .Update
    End With
    I = I + 1 ‘ increment
    rst.MoveNext
Loop


Meaning the june7's suggestion will only work when there is not a reference to a form to collect a value for part of the criteria thats runs the query. I Hope this explanation makes more sense to you now , if not let me know

ie - [Forms]![Board]![ltDate]...does not work but if I manually change the querty to = 06/03/2019 then it'll run


Go to the top of the page
 
isladogs
post Mar 6 2019, 06:20 AM
Post#19


UtterAccess VIP
Posts: 1,451
Joined: 4-June 18
From: Somerset, UK


Save your form reference to a variable e.g . DteItDate.
Then use a function GetItDate and set that equal to dteItDate.
Use the function in your query filter criteria

--------------------
Go to the top of the page
 
June7
post Mar 6 2019, 02:30 PM
Post#20



Posts: 648
Joined: 25-January 16



I do not use dynamic parameterized queries. I would build query without dynamic parameter then supply the parameter with VBA:

Set rst = CurrentDb.OpenRecordset ("SELECT * FROM queryname WHERE somefield =" & some control on form)


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 09:27 PM