Jump to content
Larry Ullman's Book Forums

Dividing A List, But Keep It Alphabetically Grouped.


Recommended Posts

This is a little difficult to explain, but I'll do my best:

 

I am creating an event ticketing system, and am using the info in this book create a PDF-formatted check-in list of each event's attendees.

 

Problem: An event can have hundreds of guests. The client wants the ability to divide the printed attendee list into smaller lists, so that if there are 5 staff members on hand for an event doing check-in, he will split the list into five lists and each staff member handles a list.

 

The problem is that if the attendee list is ordered by last name and then divided into five equal sections, the lists are not alphabetically grouped. Ideally, you would want list 1 to handle all attendees with a last name beginning A B or C, list 2 with all attendees E F G H and so on. If you just divide the list into five equal sections, list 1 would have all A B C and a few D's, the next list would have the remaining Ds and then E F G and some H, list 3 has some H and I J K and so on. Which is messy.

 

How do I divide a list into approximately x equal parts, while still keeping it alphabetically grouped?

Link to comment
Share on other sites

I'd set some limits based on the dataset - basically divided the total number of attendees by the number of staff you have working.

 

Then run a query that counts the number of rows in a group and group that by the first letter of the last name.

 

Initialise a counter variable, then loop through all the results progressively checking if the limit has been reached and if not adding the next value onto the variable. At the same time add the current letter to an array.

 

Once you reach the limit - reset the counter and create a new array (or sub-array) to hold the next series of letters and repeat the process.

 

You might want to consider setting tolerance bands so if the cut off is 500 you don't add on when you're on 499. Or you might want to check the amount before you add it on to ensure its not going to send you significantly over.

 

That all make sense?

Link to comment
Share on other sites

Basically, order the database list in ascending order by last name, and then use the LIMIT clause to divide it up into equal parts. And sorry Stuart, I didn't mean to talk over you, but I found your explanation to be slightly confusing.

 

As a think Stuart was saying, another thing worth considering is whether to divide up the list so that letters aren't split in the middle. Honestly though, if there aren't a lot of staff members, dividing the list up manually might be the easiest way.

 

Basically, alphabetize the entire list and print it out to the screen. Then just divide it up as evenly as possible, while avoiding letters from being split down the middle.

Link to comment
Share on other sites

Yeah was a little worried at the time HartleySan that it wasn't the clearest explanation but was putting off writing the code as I've got too much work at the moment. A simpler way and kind of a hybrid between our suggestions might be to divide your dataset by the number of staff. Then query at each of these points e.g. at 50, 100, 150, 200 and get the first letter of that ticketers last name (have to order by last name obviously) and that creates you bands.

 

It's not as effective at normalising the sizes of the lists as my first approach but it's a lot simpler. But like HartleySan says if you have to print the lists off anyway (probably with page breaks at each letter to make it easier for the staff) you could just hand out the paper equally amongst them.

Link to comment
Share on other sites

Yeah, I figured you might be busy. Whenever I'm busy and have a lot of things on my mind, I write cruddy, unclear answers to questions.

 

Anyway, David L, depending on the approach you want to take, you have a variety of choices. Care to enlighten us a bit more?

Link to comment
Share on other sites

Thanks guys, I like Stuart's first solution the most, using arrays and counters seems like the best and most polished way to go for lists as equal as possible without splitting letters in the middle...

 

Thanks so much!

Link to comment
Share on other sites

 Share

×
×
  • Create New...