Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-24-2018, 08:08 AM   #1
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
help with the Easiest way to finding where in the list

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:
 
Dim Cnt as long

For Each fld In qryfld.Fields    'loop through all the fields of the Query

Cnt = Cnt + 1

      If fld.Name = "mr Smith" then
Msgbox "mr Smith is at row “ & Cnt 

Next
Code:
'---------------------------------------------------------------------------------------
' Procedure : listQueryFields
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return a listing of all the fields (column names) of a give Query
' Copyright : The following code may be used as you please, but may not be resold, as
'             long as the header (Author, Website & Copyright) remains with the code.
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strQryName - Name of the query to list the fields of.
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2007-June-01            Initial Release
'---------------------------------------------------------------------------------------
Function listQueryFields(strQryName AsString) AsString
OnErrorGoTo listQueryFields_Error
Dim db As DAO.Database
Dim qryfld As DAO.QueryDef
Dim fld As Field

Set db = CurrentDb()
Set qryfld = db.QueryDefs(strQryName)
ForEach fld In qryfld.Fields    'loop through all the fields of the Query
      Debug.Print fld.Name
Next

Error_Handler_Exit:   
Set qryfld = Nothing
Set db = Nothing
ExitFunction

listQueryFields_Error:
  MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
  Err.Number & vbCrLf & "Error Source: listQueryFields" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
EndFunction

Gint32 is offline   Reply With Quote
Old 04-24-2018, 08:19 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,572
Thanks: 10
Thanked 1,247 Times in 1,187 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Quote:
Originally Posted by Gint32 View Post
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
Gint32 (04-24-2018)
Old 04-24-2018, 08:20 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,611
Thanks: 10
Thanked 2,066 Times in 2,021 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: help with the Easiest way to finding where in the list

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:

Quote:
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.

Quote:
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.

plog is offline   Reply With Quote
Old 04-24-2018, 08:25 AM   #4
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by MarkK View Post
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!
Gint32 is offline   Reply With Quote
Old 04-24-2018, 08:33 AM   #5
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,572
Thanks: 10
Thanked 1,247 Times in 1,187 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Quote:
Originally Posted by plog View Post
...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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 04-24-2018, 08:37 AM   #6
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

[QUOTE=plog;1574747] 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
Gint32 is offline   Reply With Quote
Old 04-24-2018, 08:39 AM   #7
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,611
Thanks: 10
Thanked 2,066 Times in 2,021 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: help with the Easiest way to finding where in the list

Quote:
...and now your stored rank is stale...
Please post the portion of my answer where I recommended that.

plog is offline   Reply With Quote
Old 04-24-2018, 08:39 AM   #8
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by MarkK View Post
...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 is offline   Reply With Quote
Old 04-24-2018, 08:44 AM   #9
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by plog View Post
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
Gint32 is offline   Reply With Quote
Old 04-24-2018, 08:46 AM   #10
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,611
Thanks: 10
Thanked 2,066 Times in 2,021 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: help with the Easiest way to finding where in the list

Quote:
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.
plog is offline   Reply With Quote
Old 04-24-2018, 08:50 AM   #11
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,931
Thanks: 75
Thanked 1,218 Times in 1,135 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: help with the Easiest way to finding where in the list

Agree with previous comments but if you still want to assign a 'record number' an easy way to do so is using the Serialize function.

See this link for the function itself and examples of its use:
https://www.access-programmers.co.uk...d.php?t=297922
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 04-24-2018, 09:04 AM   #12
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by plog View Post
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
Gint32 is offline   Reply With Quote
Old 04-24-2018, 09:07 AM   #13
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,611
Thanks: 10
Thanked 2,066 Times in 2,021 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: help with the Easiest way to finding where in the list

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?
plog is offline   Reply With Quote
Old 04-24-2018, 09:21 AM   #14
Gint32
Newly Registered User
 
Join Date: Jan 2018
Posts: 39
Thanks: 19
Thanked 0 Times in 0 Posts
Gint32 is on a distinguished road
Re: help with the Easiest way to finding where in the list

Quote:
Originally Posted by plog View Post
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:
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
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
WHERE (((AttendTypes.Attend)<>"Nothing Booked") AND ((Attend.AttDate)=[Forms]![OT_Board]![txt_OTDate]) AND ((Tbl_Staff_Types.Staff_Type_ID)<>4))
ORDER BY WorkLocations.Priority, Tbl_Staff_Types.Staff_OrderID, Tbl_Staffs.OT_Hours, Attend.LastAmendedDate, AttendTypes.Attend, Tbl_Staffs.BA, Tbl_Staffs.StaffID;
Gint32 is offline   Reply With Quote
Old 04-24-2018, 09:23 AM   #15
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,931
Thanks: 75
Thanked 1,218 Times in 1,135 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: help with the Easiest way to finding where in the list

Have a look at the link I gave in my previous post

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] List not finding correct show Infinite Forms 0 07-03-2015 10:23 AM
finding smallest combinations of a list form another list buckau Modules & VBA 9 03-22-2009 03:31 PM
finding list needed yasin Forms 2 06-15-2005 09:21 PM
Finding List needed yasin Forms 0 06-15-2005 09:41 AM
finding the average in a list of numbers edtree Reports 4 10-28-2000 10:00 AM




All times are GMT -8. The time now is 09:04 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World