Query - selecting smallest records up to a certain (total) size

[streff]

Hi all, my first post here, i'm a bit of a newbie when it comes to access programming.

I need to design a query that will return a number of dvd titles with a total running time of a set size (7hrs/420mins) - and it must cram in as many titles as it can. I can get access to create a running total of all the dvd's lengths, but cant get it to stop at the 420min mark.
So far i have it merely listing the data is ascending order by length, and giving me a running total in another column, using a simple select query.

Any suggestions or help would be appreciated. :)

streff.

[beat1078]

Do you have a field in your table that has the exact run time of each movie? If you do then in your qry put Runtime="420" or Runtime="7hrs" depending on how your field is formatted or weather you put the minutes or hours in the field data.

[comteck]

What code are you using?

comteck

[streff]

Hi guys, thanks for the replies.

I'm trying to do the query using the access design view, and entering expressions into the "criteria" field, although i get the feeling that this method does not have the ability to do what i need it to.

The table has a field for the runtime of the film in minutes - "length".

Putting just length="420" in the query results in 0 listings, as none of the films are 420 minutes long.

I think what i need the query to do is keep a running total of the overall length as it selects records, and stop when that overall length exceeds 420mins; but also add the films to the total overall time in the order of smallest first, so there is as little of the 420 mins left over as possible.

[comteck]

I'm not sure how to do it with a query. But it can be done using code. I'm not sure if you want to do it on a form, or if you just want to run a query and have the total appear. Not 100% sure what your application is.

comteck

[streff]

Ok, how would you do it in a form with code? I've realised its a bit complex for a query.

I have a basic grasp of programming, just not in any access or database specific areas. Heres a loose algorythm of what i think i need to do -

---------------------------------
look at the "length" column,
start a loop,
calculate the total running time of the column,
if that is over "420", drop the largest sized entry from the list
end loop
display results, preferably so i can put the results in a report, so i guess it would be a table.
---------------------------------


Basically, i need to pick out the best combination of films to fill a 7 hour (420 mins) period, from a list of dvd's and thier respective lengths, in order to watch as many titles as possible. its a bit of an abstract reason, but its a challenge i've been set, and ive been trying to figure it out all week.. lol

[streff]

Ok, got me a big book on VBA in access... a little old, called Access 97 Power Programming, but the info still seems relevent, and i can double check it all on the 'net. Also got Using access 97 to look up as well.. using acess 2003 at home, access 200 at work.
i think the way i'll try it is a form, with an output to a list box, displaying the name and individual runtimes of the movies and an unbound text box displaying the total runtime of the movies, but i'm unsure of where to put the code to actually filter the results - i will try putting in a filter button with the code on the OnClick function first, but i need to read more into VBA to actually do the process. I understand the loop functions and if statements i think i will need, its just the actual interfacing with the database part i dont know yet.

I'll update this as i go, just in case someone is interested :D

[PEB]

Hi everyone! You can try the follow:
1. You have to create a module in your database
2. Place the follow inside:

Option Explicit
Global last_criteria
Global last_used

Function Set_last(Values, criterias)
last_criteria = criterias
last_used = Values
Set_last = last_used
End Function
Function show_last(criterias)
show_last = last_used
End Function

3. USE append query to insert the results in a new table! Other way your results won't be true!

4. To do a running Sum you have to do the follow:
INSERT INTO New_table(f1,f2,f3)
SELECT film, mylength, Set_last(val(show_last(mylength))+mylength , film)
FROM my_table;

Have a nice luck!