help with the Easiest way to finding where in the list (1 Viewer)

Gint32

Registered User.
Local time
Today, 15:24
Joined
Jan 21, 2018
Messages
39
Hi Everyone,

I need to find a way of finding where a particular record is within a query, meaning if my query returns 20 records of Surnames and Mr Smith happens to be a returned as a row somewhere in the middle of the returned values, then I need to show that Number.value

I thought I might be best to have a go and tweak the below code(I found on this Forum) to suit my needs, but will need a little help with the VBA!

Hopefully someone with expertise will be able to tell me where I am going wrong or if the below is way to complex for doing what I need.

Here goes !!!

Code:
[FONT=Courier New]Dim Cnt as long[/FONT]

[COLOR=#8d38c9][FONT=Courier New]For [/FONT][/COLOR][COLOR=#8d38c9][FONT=Courier New]Each[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] fld [/FONT][/COLOR][COLOR=#8d38c9][FONT=Courier New]In[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] qryfld.Fields    [/FONT][/COLOR][COLOR=green][FONT=Courier New]'loop through all the fields of the Query[/FONT][/COLOR]

[COLOR=#555555][FONT=Courier New]Cnt = Cnt + 1[/FONT][/COLOR]

[COLOR=#555555][FONT=Courier New]      If fld.Name = [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"[/FONT][/COLOR][COLOR=#555555][FONT=Courier New]mr Smith[/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] then[/FONT][/COLOR]
[COLOR=#555555][FONT=Courier New]Msgbox [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"[/FONT][/COLOR][COLOR=#555555][FONT=Courier New]mr Smith[/FONT][/COLOR][COLOR=maroon][FONT=Courier New] is at row “ & Cnt [/FONT][/COLOR]

[COLOR=#8d38c9][FONT=Courier New]Next[/FONT][/COLOR]
Code:
[COLOR=green][FONT=Courier New]'---------------------------------------------------------------------------------------[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Procedure : listQueryFields[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Author    : CARDA Consultants Inc.[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Website   : http://www.cardaconsultants.com[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Purpose   : Return a listing of all the fields (column names) of a give Query[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Copyright : The following code may be used as you please, but may not be resold, as[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]'             long as the header (Author, Website & Copyright) remains with the code.[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]'[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Input Variables:[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' ~~~~~~~~~~~~~~~~[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' strQryName - Name of the query to list the fields of.[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]'[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Revision History:[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Rev       Date(yyyy/mm/dd)        Description[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' **************************************************************************************[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' 1         2007-June-01            Initial Release[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]'---------------------------------------------------------------------------------------[/FONT][/COLOR]
[B][COLOR=#e56717][FONT=Courier New]Function[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] listQueryFields(strQryName [/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]As[/FONT][/COLOR][/B][B][COLOR=#f660ab][FONT=Courier New]String[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New]) [/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]As[/FONT][/COLOR][/B][B][COLOR=#f660ab][FONT=Courier New]String[/FONT][/COLOR][/B]
[B][COLOR=#151b8d][FONT=Courier New]On[/FONT][/COLOR][/B][B][COLOR=#151b8d][FONT=Courier New]Error[/FONT][/COLOR][/B][B][COLOR=#8d38c9][FONT=Courier New]GoTo[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] listQueryFields_Error[/FONT][/COLOR]
[B][COLOR=#151b8d][FONT=Courier New]Dim[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] db [/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]As[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] DAO.Database[/FONT][/COLOR]
[B][COLOR=#151b8d][FONT=Courier New]Dim[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] qryfld [/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]As[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] DAO.QueryDef[/FONT][/COLOR]
[B][COLOR=#151b8d][FONT=Courier New]Dim[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] fld [/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]As[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] Field[/FONT][/COLOR]

[B][COLOR=#151b8d][FONT=Courier New]Set[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] db = CurrentDb()[/FONT][/COLOR]
[B][COLOR=#151b8d][FONT=Courier New]Set[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] qryfld = db.QueryDefs(strQryName)[/FONT][/COLOR]
[B][COLOR=#8d38c9][FONT=Courier New]For[/FONT][/COLOR][/B][B][COLOR=#8d38c9][FONT=Courier New]Each[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] fld [/FONT][/COLOR][B][COLOR=#8d38c9][FONT=Courier New]In[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] qryfld.Fields    [/FONT][/COLOR][COLOR=green][FONT=Courier New]'loop through all the fields of the Query[/FONT][/COLOR]
[COLOR=#555555][FONT=Courier New]      Debug.[/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]Print[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] fld.Name[/FONT][/COLOR]
[B][COLOR=#8d38c9][FONT=Courier New]Next[/FONT][/COLOR][/B]

[COLOR=#555555][FONT=Courier New]Error_Handler_Exit:   [/FONT][/COLOR]
[B][COLOR=#151b8d][FONT=Courier New]Set[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] qryfld = [/FONT][/COLOR][B][COLOR=#00c2ff][FONT=Courier New]Nothing[/FONT][/COLOR][/B]
[B][COLOR=#151b8d][FONT=Courier New]Set[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] db = [/FONT][/COLOR][B][COLOR=#00c2ff][FONT=Courier New]Nothing[/FONT][/COLOR][/B]
[B][COLOR=#e56717][FONT=Courier New]Exit[/FONT][/COLOR][/B][B][COLOR=#e56717][FONT=Courier New]Function[/FONT][/COLOR][/B]

[COLOR=#555555][FONT=Courier New]listQueryFields_Error:[/FONT][/COLOR]
[COLOR=#555555][FONT=Courier New]  MsgBox [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"MS Access has generated the following error"[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] & vbCrLf & vbCrLf & [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"Error Number: "[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] & _[/FONT][/COLOR]
[COLOR=#555555][FONT=Courier New]  Err.Number & vbCrLf & [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"Error Source: listQueryFields"[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] & vbCrLf & _[/FONT][/COLOR]
[COLOR=maroon][FONT=Courier New]"Error Description: "[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] & Err.Description, vbCritical, [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"An Error has Occured!"[/FONT][/COLOR]
[B][COLOR=#8d38c9][FONT=Courier New]Resume[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] Error_Handler_Exit[/FONT][/COLOR]
[B][COLOR=#8d38c9][FONT=Courier New]End[/FONT][/COLOR][/B][B][COLOR=#e56717][FONT=Courier New]Function[/FONT][/COLOR][/B]
 

MarkK

bit cruncher
Local time
Today, 15:24
Joined
Mar 17, 2004
Messages
8,178
I need to find a way of finding where a particular record is within a query
Why? This is an usual thing to need to do with data. Typically in a database you treat data as an aggregate, and the position of a particular datapoint within the set is not relevant. Maybe you can describe in more detail why knowing the rank of a particular row advances your purpose. What is your purpose that this matters?
And that is the reason that it is hard to do, because it is typically not important. Also, the rank is not a property of the datapoint itself, it is a property of the list.
hth
Mark
 

plog

Banishment Pending
Local time
Today, 17:24
Joined
May 11, 2011
Messages
11,612
First, that code you posted only spits out the column names of the fields in the query.
It does nothing with values inside the query results itself.

Second, what you want to do is ill defined. Suppose this is your query:

SELECT * FROM YourTable;

That query has no first record, it has no last record, no 41st, etc. Order only exists on a query when you tell it to exist. Usually this is done via an ORDER BY clause. Does the query you want to do this with have an ORDER BY?

Even with an ORDER BY often a query is not well ordered--meaning that there is only one way to order the data.

SELECT * FROM YourTable ORDER BY FirstName

If you have more than 1 'John' in your table, then there's no telling which record will be 'first'.

With all that said, if you do have a way to well order your data, then I suggest you create a new calculated field in your query and assign the order position via that field.
That way each record in the query gets a number and there's no question as to its 'position' and you only need look at that field to get its value that you ultimately want.
 

Gint32

Registered User.
Local time
Today, 15:24
Joined
Jan 21, 2018
Messages
39
Why? This is an usual thing to need to do with data. Typically in a database you treat data as an aggregate, and the position of a particular datapoint within the set is not relevant. Maybe you can describe in more detail why knowing the rank of a particular row advances your purpose. What is your purpose that this matters?
And that is the reason that it is hard to do, because it is typically not important. Also, the rank is not a property of the datapoint itself, it is a property of the list.
hth
Mark


Sorry, I am building an over-time Register and staff wish to know what position they are on the call list for any given day!

My query in question retrieves the staff in an ordered list for any given date.
I hope this helps explain the why's!
 

MarkK

bit cruncher
Local time
Today, 15:24
Joined
Mar 17, 2004
Messages
8,178
...then I suggest you create a new calculated field in your query and assign the order position via that field.
That way each record in the query gets a number and there's no question as to its 'position'...
...but if you do this, there is no guarantee that the list was not edited by another user, who may have changed, deleted, or added a record, and now your stored rank is stale, and can never be guaranteed to be correct.
This is why rank is not a property of the datapoint itself, it is a property of the list, and only a property of the list as you just queried it, in that moment. Query it again, and that rank may have changed. So rank is a very fluid concept in a database, always subject to change without notice, and it can only be assured to be correct in the current moment in time, so not something you should store.
Mark
 

Gint32

Registered User.
Local time
Today, 15:24
Joined
Jan 21, 2018
Messages
39
If you do have a way to well order your data, then I suggest you create a new calculated field in your query and assign the order position via that field. QUOTE]

Thanks for your reply, and yes the query is ordered and yes they all are unique records within as I use Id numbers, Also, thanks for your suggestion, as it sounds like an easier approach, but do you have any suggestions on what would go into this calculated field, As I don't know much about calculated fields
 

plog

Banishment Pending
Local time
Today, 17:24
Joined
May 11, 2011
Messages
11,612
...and now your stored rank is stale...

Please post the portion of my answer where I recommended that.
 

Gint32

Registered User.
Local time
Today, 15:24
Joined
Jan 21, 2018
Messages
39
...but if you do this, there is no guarantee that the list was not edited by another user, who may have changed, deleted, or added a record, and now your stored rank is stale, and can never be guaranteed to be correct.
This is why rank is not a property of the datapoint itself, it is a property of the list, and only a property of the list as you just queried it, in that moment. Query it again, and that rank may have changed. So rank is a very fluid concept in a database, always subject to change without notice, and it can only be assured to be correct in the current moment in time, so not something you should store.
Mark


Good point, so, I'll have a refresh button installed, if I get the vba correct.
 

Gint32

Registered User.
Local time
Today, 15:24
Joined
Jan 21, 2018
Messages
39
First, that code you posted only spits out the column names of the fields in the query.
It does nothing with values inside the query results itself.

Second, what you want to do is ill defined. Suppose this is your query:



That query has no first record, it has no last record, no 41st, etc. Order only exists on a query when you tell it to exist. Usually this is done via an ORDER BY clause. Does the query you want to do this with have an ORDER BY?

Even with an ORDER BY often a query is not well ordered--meaning that there is only one way to order the data.



If you have more than 1 'John' in your table, then there's no telling which record will be 'first'.

With all that said, if you do have a way to well order your data, then I suggest you create a new calculated field in your query and assign the order position via that field.
That way each record in the query gets a number and there's no question as to its 'position' and you only need look at that field to get its value that you ultimately want.

Thanks for your reply, and yes the query is ordered and yes they all are unique records within as I use Id numbers, Also, thanks for your suggestion, as it sounds like an easier approach, but do you have any suggestions on what would go into this calculated field, As I don't know much about calculated fields
 

plog

Banishment Pending
Local time
Today, 17:24
Joined
May 11, 2011
Messages
11,612
as I use Id numbers

If that's the case, then you can use this as a calculated field:

SortOrder: (SELECT COUNT(ID) FROM YourTable Sub1 WHERE Sub1.ID<YourTable.ID)+1


With that said, I don't know if an ID field is the best field to use for this. An ID field is really only for ensuring uniqueness in a table.
 

Gint32

Registered User.
Local time
Today, 15:24
Joined
Jan 21, 2018
Messages
39
If that's the case, then you can use this as a calculated field:

SortOrder: (SELECT COUNT(ID) FROM YourTable Sub1 WHERE Sub1.ID<YourTable.ID)+1


With that said, I don't know if an ID field is the best field to use for this. An ID field is really only for ensuring uniqueness in a table.

thanks, but unfortunately I'm not quite following your suggestion ,so am having trouble with the syntax, if you could can you tell me what "Sub1" is and also what the "<" part is doing?
please elaborate if possible ..thanks
 

plog

Banishment Pending
Local time
Today, 17:24
Joined
May 11, 2011
Messages
11,612
That calculated field should be pasted into the Query Design view. Then you only should need to change 'YourTable' to the name of your table.

It's a subquery running inside your actual query to get the number of records with IDs less than the current record (thus the < ). Sub1 is the name of that query, you don't need to touch it.

Can you post the SQL of your query?
 

Gint32

Registered User.
Local time
Today, 15:24
Joined
Jan 21, 2018
Messages
39
That calculated field should be pasted into the Query Design view. Then you only should need to change 'YourTable' to the name of your table.

It's a subquery running inside your actual query to get the number of records with IDs less than the current record (thus the < ). Sub1 is the name of that query, you don't need to touch it.

Can you post the SQL of your query?

Thanks, but I don't think I am after the number of I'ds less than the current record, Correct me if I'm wrong but I need the query to add a sequential number in the column "SortOrder:", If I can get this newly created value from as you suggested from a totals query then I will be able to display this value within Form.




Code:
[SIZE=3][FONT=Calibri]SELECT WorkLocations.Priority, Tbl_Staff_Types.Staff_OrderID, Tbl_Staffs.OT_Hours, Attend.LastAmendedDate, AttendTypes.Attend, WorkLocations.WorkLocation, Tbl_Staffs.BA, Tbl_Staffs.StaffID, Tbl_Staffs.Surname, Tbl_Staffs.FirstName, Tbl_Staffs.StaffPhoneNumber1, Tbl_Staffs.StaffPhoneNumber2, Attend.CalledTime, Tbl_Staffs.WrkPreferences, Attend.WrkDayBefore, Attend.AllocatedShift_Id, Attend.ShiftAllocatedTime, Attend.ShiftHours, [Ot_hours]+[Forms]![OT_Board]![ShiftHrs] AS AdjustedHours, Attend.Notes, Attend.AttDate, Tbl_Staff_Types.Staff_Type_Description, WorkLocations.WarehouseID, Attend.Prev_AllocatedShift_id, Attend.StaffBeingReplaced_id, Tbl_ReasonTypes.ReasonExplanation, Tbl_Staffs.workLocation, Tbl_Staff_Types.Staff_Type_ID, Attend.PurchaseLeave, Attend.NightsDays, Attend.Reason, Attend.EmailSent, Tbl_Staffs.ExtraShiftHrs, Attend.ExcessShift, (SELECT COUNT(StaffID) FROM tbl_Staffs Sub1 WHERE Sub1.StaffID<tbl_Staffs.StaffID)+1 AS SortOrder[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]FROM Tbl_ReasonTypes RIGHT JOIN ((WorkLocations INNER JOIN (Tbl_Staff_Types INNER JOIN Tbl_Staffs ON Tbl_Staff_Types.Staff_Type_ID = Tbl_Staffs.Staff_Type_ID) ON WorkLocations.WarehouseID = Tbl_Staffs.WarehouseID) INNER JOIN (Attend INNER JOIN AttendTypes ON Attend.AttType = AttendTypes.AttType) ON Tbl_Staffs.StaffID = Attend.AttStaff) ON Tbl_ReasonTypes.Reason_ID = Attend.Reason_ID[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]WHERE (((AttendTypes.Attend)<>"Nothing Booked") AND ((Attend.AttDate)=[Forms]![OT_Board]![txt_OTDate]) AND ((Tbl_Staff_Types.Staff_Type_ID)<>4))[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]ORDER BY WorkLocations.Priority, Tbl_Staff_Types.Staff_OrderID, Tbl_Staffs.OT_Hours, Attend.LastAmendedDate, AttendTypes.Attend, Tbl_Staffs.BA, Tbl_Staffs.StaffID;[/SIZE][/FONT]
 

isladogs

MVP / VIP
Local time
Today, 22:24
Joined
Jan 14, 2017
Messages
18,186
Have a look at the link I gave in my previous post
 

Gint32

Registered User.
Local time
Today, 15:24
Joined
Jan 21, 2018
Messages
39
Have a look at the link I gave in my previous post

Many Thanks Ridders.


I copied your function over into my DB
and then amended my query in design mode to ..

Code:
AssRank: Serialize("qry_Ot_Calls","staffID",[staffID])
before I did this my query returned two records (eg staff members) in the list, but after inserting this it now it throws this error:

Error 3061 in serialize procedure: Too Few parameters. Expected 2.

Do you know of any reason why this error might happen?
 

isladogs

MVP / VIP
Local time
Today, 22:24
Joined
Jan 14, 2017
Messages
18,186
I haven't studied your query in detail but it may be because you are using a field that exists in more than one table

Try it a different way.
Revert to your original query.
Now create a new query adding your serialize field to the existing fields or to those fields you need for this purpose.

BUT are you sure you want to sort by StaffID as that will give the same order each day. I thought it was to determine the order of eligibility for overtime
 

Gint32

Registered User.
Local time
Today, 15:24
Joined
Jan 21, 2018
Messages
39
I haven't studied your query in detail but it may be because you are using a field that exists in more than one table

Try it a different way.
Revert to your original query.
Now create a new query adding your serialize field to the existing fields or to those fields you need for this purpose.

BUT are you sure you want to sort by StaffID as that will give the same order each day. I thought it was to determine the order of eligibility for overtime

Thanks, the Staff are on rosters (12hrs shifts), so list is different every day! if they get called in on Over-time it'll be for a 12hrs shift.

If I understand you, create a brand new query and just use the line :
Code:
AssRank: Serialize("qry_Ot_Calls","staffID",[staffID])
then somehow call this from my original query, is this correct?
 

isladogs

MVP / VIP
Local time
Today, 22:24
Joined
Jan 14, 2017
Messages
18,186
Thanks, the Staff are on rosters (12hrs shifts), so list is different every day! if they get called in on Over-time it'll be for a 12hrs shift.

If I understand you, create a brand new query and just use the line :
Code:
AssRank: Serialize("qry_Ot_Calls","staffID",[staffID])
then somehow call this from my original query, is this correct?

No.
If your original query is queryA then create a new queryB using some or all the fields from queryA including StaffID.
Add an extra field as your first column so it is used for sorting.
The extra field Uses the Serialize function and has the new query name (queryB) in the first part
Run that query to get your 'rank order'
 

Users who are viewing this thread

Top Bottom