Selecting Data from Another Record

R

Rob B.

I have a table that contains an event number and the event date.

tblEvent: fldNo, fldDate

When someone is invited to the event, in case they can't make it, I want a
query to show the fldDate from the next record.

qryEvent: fldNo, fldDate, fldNextDate


How do I get the query to look at the next record to get the fldNextDate?


Thanks!


Rob B.
 
S

Steve Schapel

Rob,

One approach is:
NextDate: DMin("[fldDate]","tblEvent","[fldDate]>#" & [fldDate] & "#")
 
R

Rob B.

Steve,

Thanks. Doesn't seem to be taking me where I want to go, though. It
returns #Error. Let me add a bit more explanation.

Record 1 in tblEvents
fldNo = 1, fldDate = 5/17/2007

Record 2 in tblEvents
fldNo = 2, fldDate = 8/5/2007

In the query, qryEvents, I want each record to display fldNo, fldDate, and
the fldDate from the next record, so that in this example, the first record
displayed in the query would be

fldNo = 1, fldDate = 5/17/2007, fldDatefromRecord2 = 8/5/2007


Rob B.


Rob,

One approach is:
NextDate: DMin("[fldDate]","tblEvent","[fldDate]>#" & [fldDate] & "#")

--
Steve Schapel, Microsoft Access MVP
I have a table that contains an event number and the event date.

tblEvent: fldNo, fldDate

When someone is invited to the event, in case they can't make it, I want
a
query to show the fldDate from the next record.

qryEvent: fldNo, fldDate, fldNextDate


How do I get the query to look at the next record to get the
fldNextDate?


Thanks!


Rob B.
Click to expand...
Click to expand...
 
S

Steve Schapel

Rob,

I'm pretty sure I understood what you mean. And it's working for me.
Can you post back with the SQL view of the query you are using please.

Is the value of fldNo always increasing with increasing dates? If so,
you might try:
NextDate: DMin("[fldDate]","tblEvent","[fldNo]>" & [fldNo])
.... might be a bit simpler.

You may get an error for the last record in the table (i.e. where there
is no "next" anyway). To cater to this, try:
NextDate: CDate(Nz(DMin("[fldDate]","tblEvent","[fldNo]>" &
[fldNo]),[fldDate]))
 
R

Rob B.

That got it. And then I realized, "Oops."

Each event is geared towards a specific gender. [fldSex] in each tblEvent
record is set to either "M" or "W". Simply selecting the date from the next
record without accounting for the gender could result in inviting men to a
women's event or vice-versa. That may have some entertainment value, but a
few would get upset.

Unfortunately, these events do not alternate W, M, W, M... Sometimes,
there's 3 women's events for every men's. So the trick here is to select
the next event date while accounting for the gender.

If I was challenged by how to lookup the next event date simply by looking
at the next record, figuring this out will be really fun.


Best,
Rob B.
 
S

Steve Schapel

Rob,

Like this?...

NextDate: DMin("[fldDate]","tblEvent","[fldNo]>" & [fldNo] & " And
[fldSex]='" & [fldSex] & "'")
NextDate: CDate(Nz(DMin("[fldDate]","tblEvent","[fldNo]>" & [fldNo] & "
And [fldSex]='" & [fldSex] & "'"),[fldDate]))
 
R

Rob B.

And to think I was considering breaking out the men's and women's events
into separate tables. Your solution works, and I need to study why it
works. Thanks for the education, Steve!


Like this?...
Click to expand...
NextDate: CDate(Nz(DMin("[fldDate]","tblEvent","[fldNo]>" & [fldNo] & "
And [fldSex]='" & [fldSex] & "'"),[fldDate]))
Click to expand...
 
S

Steve Schapel

Rob,

I think it will be clear if you translate to English. :)

"Find the earliest event that is later than the current event and tagged
for the same sex as the current event."
 
Top