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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Extract The Value Of Previous And Next Record Of Current Field Value?, Access 2013    
 
   
freespiritcheris...
post Aug 9 2018, 03:28 PM
Post#1



Posts: 85
Joined: 19-October 04



Hurty head..

I want to filter out a value and it show the value of the previous record and next record as well, even if the value occurs multiple times in the table.

eg

and have a msg box/prompt to enter name say 'Zoe' and the results show Sophia, Emma, Riley, Amelia and then send it to a separate table called PrevNextbl? Is this possible?

Thanks in advance.
This post has been edited by freespiritcherishes: Aug 9 2018, 03:43 PM
Attached File(s)
Attached File  filt.png ( 21.77K )Number of downloads: 0
 
Go to the top of the page
 
RJD
post Aug 9 2018, 03:50 PM
Post#2


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


Hi: Okay, if the IDs are sequenced as you show them, this is pretty easy. See the demo attached. See qryResults.

I'll let you do the append or make-table query from this.

I just had to take a crack at this, given that you included my Great-great-great-great-great-grandmother (Isabella Brown from Lisburn, Northern Ireland) in the list. True.

HTH
Joe

See below. Looks like Daniel and I did essentially the same thing, although I must have posted a couple seconds before. And although I did not mention the field name "Name", I did change it in the demo. Good that Daniel pointed that out...
Attached File(s)
Attached File  PreviousAndNextRecords.zip ( 18.82K )Number of downloads: 3
 
Go to the top of the page
 
Daniel_Stokley
post Aug 9 2018, 03:50 PM
Post#3



Posts: 273
Joined: 22-December 14
From: Grand Junction, CO, USA


Hello freespiritcherishes,Well, we have to be careful with the definition of "previous" and "next". I suspect you mean the record with an ID value one less (previous) and one more (next). Also, I recommend you change the column name "Name" to something that is not a reserved word. For example "FName". Given all that, the following might help.
I got your desired results with a pair of queries. Here is the SQL of the first:
SQL
SELECT FName, ID
FROM Namestbl WHERE (((FName)="Zoe"));
The second query uses the first query. Here is the SQL:
SQL
SELECT qry_N01.FName, qry_N01.ID, Namestbl.ID, Namestbl.FName
FROM Namestbl, qry_N01
WHERE (((Namestbl.ID)=[qry_N01].[ID]-1)) OR (((Namestbl.ID)=[qry_N01].[ID]+1));

Hope that helps, Daniel
Go to the top of the page
 
Daniel_Stokley
post Aug 9 2018, 03:54 PM
Post#4



Posts: 273
Joined: 22-December 14
From: Grand Junction, CO, USA


Hello Joe, the similarity of our solutions is interesting, don't you think.?. smile.gif
Go to the top of the page
 
RJD
post Aug 9 2018, 03:57 PM
Post#5


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


Hi Daniel: Indeed so! See my comment added to my post above. "Great minds ..." and all that sort of thing! hat_tip.gif

Now let's see if the sequence number thing holds up for the OP ...

Regards,
Joe
Go to the top of the page
 
freespiritcheris...
post Aug 9 2018, 04:08 PM
Post#6



Posts: 85
Joined: 19-October 04



@Joe (RJD)
@Daniel_Stokley

wow, you guys are awesome. Off I pop with your sugestions to try out.. back soon, thank you notworthy.gif! Thank you Joe for the Sample.. like.png
Go to the top of the page
 
RJD
post Aug 9 2018, 04:14 PM
Post#7


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


I am sure Daniel joins me in saying ... yw.gif

Let us know how this works for you - or if we need to look at this more ...

Regards,
Joe
Go to the top of the page
 
freespiritcheris...
post Aug 9 2018, 05:07 PM
Post#8



Posts: 85
Joined: 19-October 04



It worked, I don't know how to thank you Joe for the sample. I looked at the SQL too and I get it, I can see what is happening.. I get the logic and the process, but how you remember and retrieve the knowhow, like on tap, its like a superpower, seriously!. like.png thanks.gif !
Go to the top of the page
 
freespiritcheris...
post Aug 9 2018, 05:40 PM
Post#9



Posts: 85
Joined: 19-October 04



It sure does Daniel, thank you thumbup.gif
Go to the top of the page
 
RJD
post Aug 9 2018, 05:42 PM
Post#10


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


Ah, shucks ... blush.gif

Seriously, Daniel and I are both happy to assist (if I can speak for us both ... Daniel?). Let us know if we can be of further help.

Regards,
Joe
Go to the top of the page
 
RJD
post Aug 13 2018, 11:29 AM
Post#11


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


This from your PM ...

QUOTE
Hi Joe,
re that prev next sample file you kindly sent, is there any chance you could show me how to create a form with a combo box to do the same thing? It has been so many years since I have used access and when I look at my old databases, all the stuff I used to do with VBA (when I was young!) Now that I am old i can't remember a thing.:-/

See the revised demo attached. See how the DISTINCT first names list is generated and used in the combobox. And see the code behind the command button on the form that checks the combobox and opens the query.

I suggest you might want to use a more reliable way to select the person. What will you do if two people have the same first name? If you have a separate table with unique names, then use that in the combobox (ID, first name, last name), and include the person ID in the other table, and use the ID as the selection, then you can be assured of a unique selection.

HTH
Joe
Attached File(s)
Attached File  PreviousAndNextRecords_Rev1.zip ( 25.29K )Number of downloads: 1
 
Go to the top of the page
 
freespiritcheris...
post Aug 13 2018, 11:57 AM
Post#12



Posts: 85
Joined: 19-October 04



Thank you Joe. I was hoping more for a combo lookup of the name and the prev next results to appear at the bottom as opposed to clicking a button.
This post has been edited by freespiritcherishes: Aug 13 2018, 11:59 AM
Go to the top of the page
 
RJD
post Aug 13 2018, 12:11 PM
Post#13


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


Could you show us a picture of the desired result layout? I know you have a picture in your head of what this should look like, but I'm still fuzzy on this.

Do you mean a subform below the combobox that requeries when you select a name?

Thanks,
Joe
Go to the top of the page
 
freespiritcheris...
post Aug 13 2018, 03:28 PM
Post#14



Posts: 85
Joined: 19-October 04



Hi Joe,

Thank you x

This is a db I created back in the day when my Emily was 1! I want to re-create the sync combo that I did on this diary but showing a list of prev next records in a list below in a subform when I have selected a name from the combo list. This is the code I used behind the combo at the time on my personal diary when I was a YOUNG mum!

Private Sub CDDETypeCbo_Click()

Dim CDDETypeSQL As String

CDDETypeSQL = "select * from CDQ"
CDDETypeSQL = CDDETypeSQL & " where CDDEType = '" & CDDETypeCbo & "'"

Me.Form.RecordSource = CDDETypeSQL

End Sub


but, I can't formulate the process in my head .. after 15 years, of Adulting what little I did no has all gawwwwn. Anyway.. this is kind of what I am after.

Can you help?
This post has been edited by freespiritcherishes: Aug 13 2018, 03:55 PM
Attached File(s)
Attached File  pic1.png ( 114.03K )Number of downloads: 2
Attached File  Pic2.png ( 22.42K )Number of downloads: 2
Attached File  pic3.png ( 29.24K )Number of downloads: 2
Attached File  pic4.png ( 47.18K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Aug 13 2018, 04:47 PM
Post#15


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


See if this is what you are trying to do ...

It's not necessary to change the record source for the form each time. The SQL is always the same and looks to combobox for criteria. The AfterUpdate event procedure for the combobox simply requeries the subform.

HTH
Joe
Attached File(s)
Attached File  PreviousAndNextRecords_Rev2.zip ( 29.12K )Number of downloads: 3
 
Go to the top of the page
 
freespiritcheris...
post Aug 13 2018, 05:06 PM
Post#16



Posts: 85
Joined: 19-October 04



WOW WOW WOW !!! notworthy.gif That is exactly what Im after, thank you so much Joe.. I shall now DELVE!!! THANK YOU!
Go to the top of the page
 
RJD
post Aug 13 2018, 05:24 PM
Post#17


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that works for you. thumbup.gif

And although I used a subform, this could be done in the main form itself. I made it a subform to add some flexibility to positioning and format.

Regards,
Joe
Go to the top of the page
 
freespiritcheris...
post Aug 13 2018, 06:10 PM
Post#18



Posts: 85
Joined: 19-October 04



Its just what I needed and is saving me so much time.. compute.gif thumbup.gif thanks.gif like.png
Go to the top of the page
 
RJD
post Aug 13 2018, 07:40 PM
Post#19


UtterAccess VIP
Posts: 8,894
Joined: 25-October 10
From: Gulf South USA


thumbup.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th October 2018 - 03:25 AM