Solved Extract Nth record in a query -VBA

[XelaIrodavlas]

Hello all,

Been a while, please help me remember how to do this

I'm creating a weekly 'meal planner'. I have selected 7 food items at random (from a query). Now I just need to assign those items to 7 different fields (Monday-Sunday) in the MealPlans table.

Basically like this:
MealPlan.txtMonday = "[myquery].[row1]"
MealPlan.txtTuesday = "[myquery].[row2]"
MealPlan.txtWednesday = "[myquery].[row3]"
..etc

Far as im aware there is no easy function to extract the Nth record of a query..
Guess I need to loop through the query and extract the data 1 record at a time, how would you do it???

Many thanks!

 

[MajP]

To select n record based on some sort. Select top n of these records ascending. Now select top 1 in descending order from select top N

 

[CJ_London]

why not just assign a random number in your query from 1 -7?

 

[XelaIrodavlas]

Sorry I'm being real dumb...

How do I extract the nth record from the query (to append it to a specific field in another record)? Can it be done in VBA? I could do it with 7 seperate SQL queries but that seems like a bad approach.

Thanks

 

[MajP]

You can return a recordset of your random query sorted by the random number then move through the recordset

dim rs as dao.recordset
set rs = currentdb.openrecordset ("select * from some query order by rnd([SomeIDField])")

Me.txtMonday = rs!SomeFieldName
rs.movenext
me.txtTuesday = rs!someFieldName
rs.moveNext
....
me.txtSunday = rs!someFieldName

 

[CJ_London]

@MajP think your first answer is closer to the OP's requirement - he want to update a table, not a form

@xelalrodavlas - the problem is you have not provided enough information to be clear about what you have and what it looks like to provide anything more than a vague answer.

If you want a more detailed answer, provide more detail

1. what is the sql your query that returns 7 random food items? Perhaps that can be adapted to be an insert query
2 what does the table look like that you want to update? - your description implies it is not normalised
3. provide some example data

 

[gemma-the-husky]

I don't think you could easily do it with 7 queries.
If you want the Wednesday value, you could just filter the Wednesday row

The trouble is, what if you you think you want the 5th row for Thursday (SMTWH), but there's no row for Tuesday, so the 5th row is the Friday. Or there's only 4 rows, and the read for the 5th row fails?

Underpinning this, is the issue that databases return an unordered set of data matching the conditions, and you can't really assume anything about the set.

Maybe sort the set (query) in date order then in code do this sort of process

open recordset
while not EndOfRecordset and not foundtherecord
  if  "the current record is for the correct day" then foundtherecord = true
  if not foundtherecord then recordset movenext
wend

if foundtherecord then
   got it
else
  there was no matching record
end if

given seven meals, this will apply the first 7 meals to days 1 to 7.
It will fail if you don't have at least 7 meals.
It might be hard to have a random selection of meal choices.

activeday=0
open recordset of meal choices
while not EndOfRecordset and activeday<=7
    activeday =activeday+1
    apply the current record to the active day
    move to next record
wend

 

[MajP]

@MajP think your first answer is closer to the OP's requirement - he want to update a table, not a form

Thanks. The OP's pseudo code

MealPlan.txtMonday = "[myquery].[row1]"

looked closer to something that would work on a form but I see now they wanted a table.

So maybe something like this

dim rs as dao.recordset
dim rsMealPlan as dao.recordset
set rs = currentdb.openrecordset ("select * from some query order by rnd([SomeIDField])")
set rsMealPlan = currentdb.openrecordset("tblMealPlan")
rsMealPlan.addnew
RsMealPlan!txtMonday = rs!SomeFieldName
rs.movenext
RsmealPlan!txtTuesday = rs!someFieldName
rs.moveNext
....
RsMealPlan!txtSunday = rs!someFieldName
rsMealPlan.update

 

[XelaIrodavlas]

Thanks everyone for an amazing response. I've sorted it now using @MajP's first solution. Truth is I wasn't sure whether to update it on the form or direct to the table, but I checked and both ways work great

@Gemma you're correct it may be possible there are only 4 rows etc, but should be very unlikely - will look into it later, have to walk before I can run :'D

 

[CJ_London]

if your query is genuinely random then best to put in a table because if you need to refer to it another time running the query again would produce a different result

 

[Pat Hartman]

The Meal Plan table is not normalized. It should have 7 rows, Not 7 columns. You can use the generated random number to sort the rows.

 

[CJ_London]

You can use the generated random number to sort the rows.

that was my thinking in post#3