select First and last names based on a number

[BYarnn]

I have a DB which includes customer names. One report runs an alphabetical list from A on page 1 to Z on the last page of ALL customers. It also counts in 2 ways for the customers. For example for the last name Adams it will list all of the Adams then all Arsten's. It will give a running count up to the last customer listed (like 30 for Arsten) and it will also group all that end with ADAMS and give a count of how many there are(for example 15 for that # of Adam's). The customers are assigned to staff by alphabet and with the counting the way it is, it is easy to assign custmers to staff because we know how many of each customer there is and we know now many total there are on any page. All staff should get the same number of customers.
Need help with >>>>>I want to have the DB ALSO provide a separate list of customers assigned to staff listing ONLY the first custmers name and last customers name. For example staff # 1 who should have say 40 customers would have their 1st customer Adams and their last (40th) custober's name Arsten listed. Staff #2 would have their first Brayboy and last Cinsi names listed.
My actual field names are "LName" for last name of their customer and "employee#) for our staff person.

??? THANKS!!

 

[dhookom]

Do you expect to save this information? It would really help to see some sample records with the desired result. Could you apply your specifications to the Customer and Employee tables of Northwind and provide the desired results?

Does the first employee alphabetically get the first group of customers?

Do you have some actual table and field names?

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Hi,

So what happens when a NEW CUSTOMER comes in. Does the deck get reshuffled?

I'd think that the simplest way is to create a table of Customers and Staff, assigning by number of customers.

StaffNbr = INT((RecordNbr-1)/StaffCnt)+1

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BYarnn]

Sorry but I don't have Northwind available. Staff names are random (non-alpha within their division, but each division has a portion of the alphabet


A short sample of a report extracted from say, a table called customer DB:
StaffName Beginning Customer Ending Customer

John Lopez Petula Adams Mary Brown

Ellen Donroe Josephine Carlson Edward Dunigan

Sandra Mulroy Victor Finn Joe Gill

Note that staff names for this division are not in alpha order. Their assigned customers, however, cover the "A's" through the "G's". John Lopez has customers beginning with ADAMS and all customers until he gets to BROWN (in the table in might be a total of 600 names). Ellen pics up with "C" for Carlson and has all customers up to and incl Dunigan
The next division with other staff names will have names beginning with say Gomez and ending with Hammer
etc help???

THANKS!!!!!!!!!!!

 

[fneily]

There doesn't seem to be a connection between a staff member and the alpha group that they would be in charge. So, you should all ready have two tables made:
tblCustomers
CustID Primary Key
other info fields

tblEmployees
EmployeeID Primary Key
other info fields

Can't you simply make a junction table?
tblResponsibility
EmployeeID
CustID

The two fields together would be the primary key. So example data:
JL PA
JL MB
which would represent:
John Lopez Petula Adams
John Lopez Mary Brown

So two records for each employee. Then use the FAQ you can find in the Access forums to concatenate child records into one record for the report. Or:
John Lopez Petula Adams Mary Brown

Then using the junction table, you can change responsibilities between staff members, or change the "end points".