If you have about 550 products don’t you need about 140 per list – ie. 25% rather than 25.

The easy solution is to make fixed breakup based on the name of the product. It doesn’t really matter if the four lists contain different numbers of products.

Select ProductID, ProductName from tblProducts where (productName < 'f") ORDER BY productName;
Select ProductID, ProductName from tblProducts where (productName Between "f" And "M") ORDER BY productName;

etc

Do do it dynamically would take a lot more work.

This will get the first list

Select top 25 percent ProductID, ProductName from tblProducts ORDER BY productName;

Select top 25 percent ProductID, ProductName from tblProducts ORDER BY productName Desc;
would get the bottom 25% in reverse order, so you could then have a second query to sort these back into the right order.

To get the middle groups, I am not sure. One possibility is to use subqueries..

SELECT TOP 34 PERCENT ProductID, ProductName FROM tblProducts
WHERE (PruductID Not In (select ProductID from qrytop))
ORDER BY productName;

where qrytop is the the query that fives the top 25%.

It does not really matter if there is some overlap between the lists, but it does matter if there is a gap between them.

If you have about 550 products don’t you need about 140 per list – ie. 25% rather than 25.

The easy solution is to make fixed breakup based on the name of the product. It doesn’t really matter if the four lists contain different numbers of products.

Select ProductID, ProductName from tblProducts where (productName < 'f") ORDER BY productName;
Select ProductID, ProductName from tblProducts where (productName Between "f" And "M") ORDER BY productName;

etc

Do do it dynamically would take a lot more work.

This will get the first list

Select top 25 percent ProductID, ProductName from tblProducts ORDER BY productName;

Select top 25 percent ProductID, ProductName from tblProducts ORDER BY productName Desc;
would get the bottom 25% in reverse order, so you could then have a second query to sort these back into the right order.

To get the middle groups, I am not sure. One possibility is to use subqueries..

SELECT TOP 34 PERCENT ProductID, ProductName FROM tblProducts
WHERE (PruductID Not In (select ProductID from qrytop))
ORDER BY productName;

where qrytop is the the query that fives the top 25%.

It does not really matter if there is some overlap between the lists, but it does matter if there is a gap between them.

If the number of records to list per listbox is to vary dynamically, one approach would be to create “temporary” queries programatically which would then be used to populate the listboxes on form. As test created some queries “manually” using TOP predicate & subqueries (similar to examples John Hutchinson posted) but this quickly became cumbersome – using NOT IN with more than one subquery resulted in very slow queries even with a small number of records. So instead used DAO methods to create the querydefs to return x number of records, based on user input on form. The function that generates the queries first determines the number of queries to create (based on number of records in table, and on value of x) then runs a loop. Excerpt of code:

For n = 1 To lQueryCount
strSQL = "SELECT TOP " & lngRecords & " ProductID, ProductName " & _
"FROM PRODUCTS WHERE ProductID " & _
"In(SELECT TOP " & CStr((lTotalCount - (lngRecords * (n - 1)))) & " " & _
"ProductID FROM PRODUCTS " & _
"ORDER BY ProductName Desc) " & _
"ORDER BY ProductName;"

Set qry = db.CreateQueryDef("Temp" & n, strSQL)

See attached revised demo database (A2K) for full code and details. Note above, to avoid dreaded NOT IN syntax the subquery selects records from the bottom, not top, calculated number of records, by simply sorting products in descending order in subquery. When function called from form to repopulate listboxes, there is little delay, as might be case if using NOT IN. To test, open Form1, enter valid value in the unbound, unlocked textbox, then click “Reset” button to requery listboxes. If number of queries is less than number of listboxes, code clears the “extra” listboxes. The listbox labels are also updated to reflect which records are listed. If actually using this, may want to add code to clean up the “temp” queries when form closes. Or ditch the querydefs & just use SQL strings as RowSource to populate the listboxes.

Hope this gives you some ideas.

The thought of searching through that number of records in a list box …

The original enquiry was for a multi select listbox. I wonder whether the better interface would be single select listbox with its contents filtered according to A B C buttons (Hans did a nice one a while ago). Each selection made could then be added to a temporary table and shown in a second list box. Once all selections had been made, the results would be in the temporary table.

The thought of searching through that number of records in a list box …

The original enquiry was for a multi select listbox. I wonder whether the better interface would be single select listbox with its contents filtered according to A B C buttons (Hans did a nice one a while ago). Each selection made could then be added to a temporary table and shown in a second list box. Once all selections had been made, the results would be in the temporary table.

Sweet!

This gives me some ideas for some other uder interfaces I have been thinking about and haven’t gotten to yet.

Nice job.

Thanks, John

Sweet!

This gives me some ideas for some other uder interfaces I have been thinking about and haven’t gotten to yet.

Nice job.

Thanks, John

Another idea on this is to have a temp table.
Clear its contents using a Delete query,
Refill with all products using an append query,
delete any records in the top 25% and bottom 25%, (using subqueries]
then select the top and bottom 50% of the remaining records.

All this could use fixed queries that could be saved and reused.

Another idea on this is to have a temp table.
Clear its contents using a Delete query,
Refill with all products using an append query,
delete any records in the top 25% and bottom 25%, (using subqueries]
then select the top and bottom 50% of the remaining records.

All this could use fixed queries that could be saved and reused.

If the number of records to list per listbox is to vary dynamically, one approach would be to create “temporary” queries programatically which would then be used to populate the listboxes on form. As test created some queries “manually” using TOP predicate & subqueries (similar to examples John Hutchinson posted) but this quickly became cumbersome – using NOT IN with more than one subquery resulted in very slow queries even with a small number of records. So instead used DAO methods to create the querydefs to return x number of records, based on user input on form. The function that generates the queries first determines the number of queries to create (based on number of records in table, and on value of x) then runs a loop. Excerpt of code:

For n = 1 To lQueryCount
strSQL = "SELECT TOP " & lngRecords & " ProductID, ProductName " & _
"FROM PRODUCTS WHERE ProductID " & _
"In(SELECT TOP " & CStr((lTotalCount - (lngRecords * (n - 1)))) & " " & _
"ProductID FROM PRODUCTS " & _
"ORDER BY ProductName Desc) " & _
"ORDER BY ProductName;"

Set qry = db.CreateQueryDef("Temp" & n, strSQL)

See attached revised demo database (A2K) for full code and details. Note above, to avoid dreaded NOT IN syntax the subquery selects records from the bottom, not top, calculated number of records, by simply sorting products in descending order in subquery. When function called from form to repopulate listboxes, there is little delay, as might be case if using NOT IN. To test, open Form1, enter valid value in the unbound, unlocked textbox, then click “Reset” button to requery listboxes. If number of queries is less than number of listboxes, code clears the “extra” listboxes. The listbox labels are also updated to reflect which records are listed. If actually using this, may want to add code to clean up the “temp” queries when form closes. Or ditch the querydefs & just use SQL strings as RowSource to populate the listboxes.

Hope this gives you some ideas.