Sorting on multiple factors - Either using VBA or SQL

[canadianacorn]

I'm not even sure how to describe this, so please bear with me. I'm an experienced VBA developer, but I have a unique challenge. I am developing outbound call sheets for between one and four associates, but there are complicated duplicate rules involved.

Each call line calls one business about one of their clients. There can be multiple call lines per client, and multiple call lines per business. Also, one client may (and likely will) have visited more than one business in the pool of available call lines, and one business may have served multiple clients. Basically, I have a nasty multiplicity issue.

At the end of the day, I have to ensure that I have between 1 and four call sheets where:
Businesses are grouped on one sheet (ie one business can't span multiple call sheets)
Clients are grouped on one sheet (ie one client can't span multiple call sheets)
There are an even number of businesses spread on each call sheet

I can do half of this easily by looping through a sorted list of the businesses and assigned them to one of the n number of callsheets (total businesses mod n), but once I have that, there is a good chance that clients are going to be spanning two or more call sheets.



This strikes me as an opportunity for a recursive algorithm, but I can't figure out where to start. Does anyone know an easier solution for this? Or a harder solution, so long as it works? Any tips would help.

If it helps:
Windows 7 32 bit
Access 2010 32 bit

[orange]

Have you set up some logical-design based on your detailed knowledge of the business and processes involved?

Seems you are dealing with:

Associates
CallSheets
CallLines
Businesses
Clients

You need to identify the business rules as they relate to these "entities". Create a model or ERD that reflects your business rules. (can be done with pencil and paper). Create some test data (or copy a sample set of real data) and work the data against your evolving model. Adjust as necessry and retest the data. When there are no anomalies, work on your database design based on the ERD.

Good luck.

[canadianacorn]

Thanks for the feedback. You are quite right, and I have done an ERD, and many of my tables are modeled after the objects that they represent. I think what I'm taking away from your reply is that I need to go back to the drawing board and solve the multiplicity by normalizing the relationships Im dealing with here at the table level rather than trying to sort it out with complex code. There are reasons that this section of the project is not in 3rd normal form: I'm dealing with a transactional schema as the original data source, and I don't want to have to do a lot of data manipulation at ETL.

If anyone has another option for my sort issue, I'd be interested. Otherwise I might have to go back to the drawing board and do some more design work.