Query to run until total of column reaches value (1 Viewer)

[razorrussian]

Hello All,

long time lurker, first time poster!

I have been wracking my brain trying to figure out how to do this - I have a query in access that goes through one of my tables - tblSelections (with fields Primary Key, email, firstname, lastname, eventdate, and Partysize, the query asigns a RND value to its primary key(auto Number), and picks the top 30 rows based on a date entered into a parameter box.

We use this as sort of a lottery system, so that is why we are randomizing the PK.

If needed, I can attach a sample of my database, but what I want to do is get this query to do is instead of returning only the first 30 lines, I want it to keep running until the Total of the PartySize column reaches a specified number.

In the past this query just returned the first 30 rows on a specified day, which means that the total headcount was between 30 and 180, but we want to fine tune this to get as close to 150 as possible!

Any ideas would be greatly appreciated, I will upload a sample shortly. Thanks all!

 

[MajP]

If needed, I can attach a sample of my database, but what I want to do is get this query to do is instead of returning only the first 30 lines, I want it to keep running until the Total of the PartySize column reaches a specified number

Are you saying you want to provide an input prior to running the query so you can specify how many records to return?

 

[cheekybuddha]

Why bother futzing with the primary key?

It is a simple query to select random records.

However, you may need a code solution if you wish only to select random records where the sum of a certain field is as close to a specific total as possible

 

[plog]

the query asigns a RND value to its primary key(auto Number), and picks the top 30 rows based on a date entered into a parameter box.

That can't be. First, does RND=random or RND=round? You saved 2-3 letters in exchange for not clearly communicating. Second, a query can't assign a primary key--even if you assign a calculated value in a query theres no guarantee that value will stay attached to each specific record.

I suggest you demonstrate your issue with data, provide 2 sets:

A. starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with when you feed this query the data from A.

Again, 2 sets of data, starting and ending.

 

[June7]

An INSERT action can assign value to autonumber field, but this should NOT be routine practice. An UPDATE action cannot.

So it is not clear what you mean by "assigns a RND value to its primary key".

Selecting random records should not require changing data.

Selecting records until their sum matches some given value is not simple. Have you searched this topic?

Bing: access select records sum equals a number

Review https://stackoverflow.com/questions...cords-until-the-sum-is-less-than-n-access-sql

 

[MajP]

I am going to take a wild guess and what the OP meant to say is that they are properly seeding their RND function with a unique key for each record as they need to
Sort: RND([SomePrimaryKEY])
And if you do not do this or use a constant value see what happens.

Solved - How to get random record from MS Access database

Actually, if you are using the Rnd() function, one call to Randomize OUTSIDE the query will nonetheless affect the query too, because this is one of those cases where SQL and VBA use the same Rnd function (from the same library). There IS no SQL "Random" function but there is nothing to stop...

www.access-programmers.co.uk

 

[cheekybuddha]

>> I am going to take a wild guess and what the OP meant to say is that they are properly seeding their RND function with a unique key for each record <<
Aahh! That makes a bit more sense!

 

[MajP]

Aahh! That makes a bit more sense!

I am only guessing and may be wrong, but how they said it would be both bizarre and really complicated.

 

[cheekybuddha]

>> but how they said it would be both bizarre and really complicated. <<
I think everyone who has replied is in agreement there!