Need help with Query

  • Thread starter Thread starter TotallyConfused
  • Start date Start date
T

TotallyConfused

I would like to know how to do the following as easy as possible. Have a
list of unique business phone numbers about 2000 + can be more. These phone
numbers can have several people listed with same number. I need to split
this list by 5 or 6 lists as even;y as possible to assign to individuals to
call without having to run different queries and making invidual tables. I
thought maybe using a crosstab query but not sure if a crosstab query would
be the way to go. Thank you in advance for any help you can provide.
 
What other fields do you have in the table?

If the only thing you have is the phone number, you might be able to use the
following expression to generate 6 groups that are fairly equal in size.

Field: TheGroup: Val(Right([Phone Number],3)) Mod 6

Assumption is that the phone number ends in just numbers. If you have
numbers like 410 999 1234 x27, then those numbers will get assigned to group
zero.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Read the records into a recordset (eliminating the duplicate numbers), then
write them out assigning sequence numbers 1 thru whatever as they are written
out. You could even run this from a form where you specify the number of
lists.
Then you run a simple report to sort/print the lists.

-Dorian
 
Thank you for your response. Only fields are: phone # (999) 999-9999;
address, city, state, zip

Can I use the sample you provided?


John Spencer said:
What other fields do you have in the table?

If the only thing you have is the phone number, you might be able to use the
following expression to generate 6 groups that are fairly equal in size.

Field: TheGroup: Val(Right([Phone Number],3)) Mod 6

Assumption is that the phone number ends in just numbers. If you have
numbers like 410 999 1234 x27, then those numbers will get assigned to group
zero.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

TotallyConfused said:
I would like to know how to do the following as easy as possible. Have a
list of unique business phone numbers about 2000 + can be more. These
phone
numbers can have several people listed with same number. I need to split
this list by 5 or 6 lists as even;y as possible to assign to individuals
to
call without having to run different queries and making invidual tables.
I
thought maybe using a crosstab query but not sure if a crosstab query
would
be the way to go. Thank you in advance for any help you can provide.
 
I don't see any reason not to. Try it and if it doesn't work or you
don't understand what is happening, then post back.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you for your response. Only fields are: phone # (999) 999-9999;
address, city, state, zip

Can I use the sample you provided?


John Spencer said:
What other fields do you have in the table?

If the only thing you have is the phone number, you might be able to use the
following expression to generate 6 groups that are fairly equal in size.

Field: TheGroup: Val(Right([Phone Number],3)) Mod 6

Assumption is that the phone number ends in just numbers. If you have
numbers like 410 999 1234 x27, then those numbers will get assigned to group
zero.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

TotallyConfused said:
I would like to know how to do the following as easy as possible. Have a
list of unique business phone numbers about 2000 + can be more. These
phone
numbers can have several people listed with same number. I need to split
this list by 5 or 6 lists as even;y as possible to assign to individuals
to
call without having to run different queries and making invidual tables.
I
thought maybe using a crosstab query but not sure if a crosstab query
would
be the way to go. Thank you in advance for any help you can provide.
 
Try this ---
SELECT a.phone, a.address, a.city, a.state, a.zip,
(COUNT(*)\((DCount("Phone","YourTable")\[Enter number to divide])+1))+1 AS
rank
FROM [YourTable] AS a LEFT JOIN [YourTable] AS b ON a.Phone>=b.Phone
GROUP BY a.phone, a.address, a.city, a.state, a.zip;
 
Back
Top