Syntax needed to get needed reports

I need help!

I am using the existing address database that comes with Microsoft office for a roster of fellow shipmates. We use it for keeping
others in touch with one another. We have 1175 shipmates listed. We recently established 10 era groups with a group coordinator
for each era. The field name I used was "hobbies" and on the report we call it "years in squadron". The field is empty for some
shipmates (175 shipmates) as we don't have the years they were in the squadron. We want to send a report of ten equal groups with
the groups in ascending order to each coordinator. The ones with no years we want to also break down evenly into ten separate
groups. The years in squadron field is like 1954-59 etc. I don't know anything about writing the syntax to get the program to do
that, but I'm sure someone out there can help me. Thanking you in advance....Frank

I suggest that you not store a date range in one field (don't store it as
1954-1959); instead, store a starting year in one field, and an ending year
in another field. This will make it easier to "find" a specific record when
looking for a certain year.

You will need to give us a lot more information about what you are wanting
to achieve. What is an "era" group? What is meant by "ten equal groups"
(split the entire roster into ten groups that each have same number of
people? what if there is an "odd" number so that not all groups have same
number of people?)? Please provide some sample data and how you would like
the database to "split" the sample data.
--

Ken Snell
MS ACCESS MVP



"Frank Lueder" wrote in message
...
I need help!

I am using the existing address database that comes with Microsoft office
for a roster of fellow shipmates. We use it for keeping
others in touch with one another. We have 1175 shipmates listed. We
recently established 10 era groups with a group coordinator
for each era. The field name I used was "hobbies" and on the report we
call it "years in squadron". The field is empty for some
shipmates (175 shipmates) as we don't have the years they were in the
squadron. We want to send a report of ten equal groups with
the groups in ascending order to each coordinator. The ones with no years
we want to also break down evenly into ten separate
groups. The years in squadron field is like 1954-59 etc. I don't know
anything about writing the syntax to get the program to do
that, but I'm sure someone out there can help me. Thanking you in
advance....Frank

I want to let you know up front that I don't know that much about using SQL or Access. I was nominated as secretary of our Naval
Squadron Association (now decommissioned). Most all of the members are no longer in the military, but we are trying to keep them
all informed of what's happening with the association and keeping their contact information up-to-date for reunion purposes. As
secretary I have to do that. I used the easiest method I could think of and used the existing address data base in Access 2000. At
our last reunion we assigned 10 members to overlook and keep in contact with their ERA. We wanted to break the total members into
10 Groups. Easier for me and not having to contact all 1175 members. Each group leader would be responsible for keeping their
assigned individuals up to date.

The ERA Groups a
ERA Grp 1 Years 1946-51
ERA Grp 2 Years 1952-56
ERA Grp 3 Years 1957-58
ERA Grp 4 Years 1959-60
ERA Grp 5 Years 1961-62
ERA Grp 6 Years 1963-65
ERA Grp 7 Years 1966-69
ERA Grp 8 Years 1970-72
ERA Grp 9 Years 1973-83
ERA Grp 10 Years 1984-93

Total members at present time is 1175. I am using the "Members Table" & the "Household Table". I'm not sure on how to copy them so
you can see them other than type them individually. If you need them let me know. In the "Members Table" there is a field called
"Hobbies". I used this field to put the dates each individual was in the squadron just like it is shown above. I just change the
name on the report so it displays "Yr in Squad". There are 175 individuals that we don't have the years in squadron listed. I was
able set a query criteria "is null" for "hobbies" and get all members listed without years in squadron. I would like to break that
down into 10 groups as easily as possible, and then add each of these to the ERA Groups, so each Group Leader will have about the
same amount of members. If you need any other information please let me know.
Here's hoping you will be able to help figure this out.
Thanks, Frank

"Frank Lueder" wrote in message ...
I need help!

I am using the existing address database that comes with Microsoft office for a roster of fellow shipmates. We use it for keeping
others in touch with one another. We have 1175 shipmates listed. We recently established 10 era groups with a group coordinator
for each era. The field name I used was "hobbies" and on the report we call it "years in squadron". The field is empty for some
shipmates (175 shipmates) as we don't have the years they were in the squadron. We want to send a report of ten equal groups with
the groups in ascending order to each coordinator. The ones with no years we want to also break down evenly into ten separate
groups. The years in squadron field is like 1954-59 etc. I don't know anything about writing the syntax to get the program to do
that, but I'm sure someone out there can help me. Thanking you in advance....Frank

Are you using the "1946-51" text string (for example) to know that that
member is part of ERA Grp 1? or are you storing ERA Grp 1 somewhere in the
table?

When you "separate" the members for mailing to different coordinators, are
you doing this via a query that gives you just the members for ERA Grp 1, or
are you getting a list of all members and then physically splitting the
groups apart?

What I am asking is how do you "separate" the members into their groups so
that you can "split" them up? Somehow, the members with "blank date ranges"
will need to be assigned to a group. Before I can give you a recommendation,
I need to know how this is done.

--

Ken Snell
MS ACCESS MVP

"Frank Lueder" wrote in message
...
I want to let you know up front that I don't know that much about using SQL
or Access. I was nominated as secretary of our Naval
Squadron Association (now decommissioned). Most all of the members are no
longer in the military, but we are trying to keep them
all informed of what's happening with the association and keeping their
contact information up-to-date for reunion purposes. As
secretary I have to do that. I used the easiest method I could think of
and used the existing address data base in Access 2000. At
our last reunion we assigned 10 members to overlook and keep in contact
with their ERA. We wanted to break the total members into
10 Groups. Easier for me and not having to contact all 1175 members. Each
group leader would be responsible for keeping their
assigned individuals up to date.

The ERA Groups a
ERA Grp 1 Years 1946-51
ERA Grp 2 Years 1952-56
ERA Grp 3 Years 1957-58
ERA Grp 4 Years 1959-60
ERA Grp 5 Years 1961-62
ERA Grp 6 Years 1963-65
ERA Grp 7 Years 1966-69
ERA Grp 8 Years 1970-72
ERA Grp 9 Years 1973-83
ERA Grp 10 Years 1984-93

Total members at present time is 1175. I am using the "Members Table" &
the "Household Table". I'm not sure on how to copy them so
you can see them other than type them individually. If you need them let
me know. In the "Members Table" there is a field called
"Hobbies". I used this field to put the dates each individual was in the
squadron just like it is shown above. I just change the
name on the report so it displays "Yr in Squad". There are 175
individuals that we don't have the years in squadron listed. I was
able set a query criteria "is null" for "hobbies" and get all members
listed without years in squadron. I would like to break that
down into 10 groups as easily as possible, and then add each of these to
the ERA Groups, so each Group Leader will have about the
same amount of members. If you need any other information please let me
know.
Here's hoping you will be able to help figure this out.
Thanks, Frank

"Frank Lueder" wrote in message
...
I need help!

I am using the existing address database that comes with Microsoft office
for a roster of fellow shipmates. We use it for keeping
others in touch with one another. We have 1175 shipmates listed. We
recently established 10 era groups with a group coordinator
for each era. The field name I used was "hobbies" and on the report we
call it "years in squadron". The field is empty for some
shipmates (175 shipmates) as we don't have the years they were in the
squadron. We want to send a report of ten equal groups with
the groups in ascending order to each coordinator. The ones with no years
we want to also break down evenly into ten separate
groups. The years in squadron field is like 1954-59 etc. I don't know
anything about writing the syntax to get the program to do
that, but I'm sure someone out there can help me. Thanking you in
advance....Frank

Because the groups would be unevenly divided by selecting (for example) the "1946-51" ERA (this group might only have 25 individuals
where the "1969-72" group might have 200 individuals) we wanted each of the 10 group leaders to have approximately equal amounts of
shipmates, even if they were not all within the years for that ERA.

To separate the individuals, I did a query with the "is Null" criteria in the "Hobbies" field to get the members with no years. I
would have to cut and past them into 10 equal groups. Then I would run a query with the "is not null" criteria and do the same
thing. I would then do an ascending sort on the "Hobbies" field (I used that field as it was part of the existing Microsoft Access
database, in the report I just change it to "Yrs in Squadron"). That would put the individuals in order of years they were in the
squadron. Then I would have to cut and past them into 10 equal groups and combine each of those groups with the ones with no years.

I hope that helps. There must be an easier way to do the separation of groups. I hope you can help.
Thanks, Frank Lueder


"Ken Snell [MVP]" wrote in message ...
Are you using the "1946-51" text string (for example) to know that that
member is part of ERA Grp 1? or are you storing ERA Grp 1 somewhere in the
table?

When you "separate" the members for mailing to different coordinators, are
you doing this via a query that gives you just the members for ERA Grp 1, or
are you getting a list of all members and then physically splitting the
groups apart?

What I am asking is how do you "separate" the members into their groups so
that you can "split" them up? Somehow, the members with "blank date ranges"
will need to be assigned to a group. Before I can give you a recommendation,
I need to know how this is done.

--

Ken Snell
MS ACCESS MVP

"Frank Lueder" wrote in message
...
I want to let you know up front that I don't know that much about using SQL
or Access. I was nominated as secretary of our Naval
Squadron Association (now decommissioned). Most all of the members are no
longer in the military, but we are trying to keep them
all informed of what's happening with the association and keeping their
contact information up-to-date for reunion purposes. As
secretary I have to do that. I used the easiest method I could think of
and used the existing address data base in Access 2000. At
our last reunion we assigned 10 members to overlook and keep in contact
with their ERA. We wanted to break the total members into
10 Groups. Easier for me and not having to contact all 1175 members. Each
group leader would be responsible for keeping their
assigned individuals up to date.

The ERA Groups a
ERA Grp 1 Years 1946-51
ERA Grp 2 Years 1952-56
ERA Grp 3 Years 1957-58
ERA Grp 4 Years 1959-60
ERA Grp 5 Years 1961-62
ERA Grp 6 Years 1963-65
ERA Grp 7 Years 1966-69
ERA Grp 8 Years 1970-72
ERA Grp 9 Years 1973-83
ERA Grp 10 Years 1984-93

Total members at present time is 1175. I am using the "Members Table" &
the "Household Table". I'm not sure on how to copy them so
you can see them other than type them individually. If you need them let
me know. In the "Members Table" there is a field called
"Hobbies". I used this field to put the dates each individual was in the
squadron just like it is shown above. I just change the
name on the report so it displays "Yr in Squad". There are 175
individuals that we don't have the years in squadron listed. I was
able set a query criteria "is null" for "hobbies" and get all members
listed without years in squadron. I would like to break that
down into 10 groups as easily as possible, and then add each of these to
the ERA Groups, so each Group Leader will have about the
same amount of members. If you need any other information please let me
know.
Here's hoping you will be able to help figure this out.
Thanks, Frank

"Frank Lueder" wrote in message
...
I need help!

I am using the existing address database that comes with Microsoft office
for a roster of fellow shipmates. We use it for keeping
others in touch with one another. We have 1175 shipmates listed. We
recently established 10 era groups with a group coordinator
for each era. The field name I used was "hobbies" and on the report we
call it "years in squadron". The field is empty for some
shipmates (175 shipmates) as we don't have the years they were in the
squadron. We want to send a report of ten equal groups with
the groups in ascending order to each coordinator. The ones with no years
we want to also break down evenly into ten separate
groups. The years in squadron field is like 1954-59 etc. I don't know
anything about writing the syntax to get the program to do
that, but I'm sure someone out there can help me. Thanking you in
advance....Frank

I'm not suggesting that you'll need to do any cutting/pasting/copying of
data manually. I envision that we can do this via some queries once we
identify clearly what is needed for identifying which group the members who
have no "dates of service" data.

I still need to know how you're identifying the specific group to which the
members belong -- are you using just the Hobbies field with a date range
string in it? Or do you also use another field with a group number in it?
And how will you "separate" the members by groups -- are you running a
report that just shows all members and notes which group the member is in?
or are you running queries that show just the members of each group, where
you specify the group?

If you're using just the Hobbies field, then it'll be necessary to put the
"group date range" string into that field for the members who currently have
no date range shown. But that will mean that your data will be incorrect for
those members, as they really don't have a date of service range at this
time.

I am willing to assist, but I need more information, per my questions.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
Because the groups would be unevenly divided by selecting (for example)
the "1946-51" ERA (this group might only have 25 individuals
where the "1969-72" group might have 200 individuals) we wanted each of
the 10 group leaders to have approximately equal amounts of
shipmates, even if they were not all within the years for that ERA.

To separate the individuals, I did a query with the "is Null" criteria in
the "Hobbies" field to get the members with no years. I
would have to cut and past them into 10 equal groups. Then I would run a
query with the "is not null" criteria and do the same
thing. I would then do an ascending sort on the "Hobbies" field (I used
that field as it was part of the existing Microsoft Access
database, in the report I just change it to "Yrs in Squadron"). That
would put the individuals in order of years they were in the
squadron. Then I would have to cut and past them into 10 equal groups and
combine each of those groups with the ones with no years.

I hope that helps. There must be an easier way to do the separation of
groups. I hope you can help.
Thanks, Frank Lueder


"Ken Snell [MVP]" wrote in message
...
Are you using the "1946-51" text string (for example) to know that that
member is part of ERA Grp 1? or are you storing ERA Grp 1 somewhere in the
table?

When you "separate" the members for mailing to different coordinators, are
you doing this via a query that gives you just the members for ERA Grp 1,
or
are you getting a list of all members and then physically splitting the
groups apart?

What I am asking is how do you "separate" the members into their groups so
that you can "split" them up? Somehow, the members with "blank date
ranges"
will need to be assigned to a group. Before I can give you a
recommendation,
I need to know how this is done.

--

Ken Snell
MS ACCESS MVP

"Frank Lueder" wrote in message
...
I want to let you know up front that I don't know that much about using
SQL
or Access. I was nominated as secretary of our Naval
Squadron Association (now decommissioned). Most all of the members are
no
longer in the military, but we are trying to keep them
all informed of what's happening with the association and keeping their
contact information up-to-date for reunion purposes. As
secretary I have to do that. I used the easiest method I could think of
and used the existing address data base in Access 2000. At
our last reunion we assigned 10 members to overlook and keep in contact
with their ERA. We wanted to break the total members into
10 Groups. Easier for me and not having to contact all 1175 members. Each
group leader would be responsible for keeping their
assigned individuals up to date.

The ERA Groups a
ERA Grp 1 Years 1946-51
ERA Grp 2 Years 1952-56
ERA Grp 3 Years 1957-58
ERA Grp 4 Years 1959-60
ERA Grp 5 Years 1961-62
ERA Grp 6 Years 1963-65
ERA Grp 7 Years 1966-69
ERA Grp 8 Years 1970-72
ERA Grp 9 Years 1973-83
ERA Grp 10 Years 1984-93

Total members at present time is 1175. I am using the "Members Table" &
the "Household Table". I'm not sure on how to copy them so
you can see them other than type them individually. If you need them let
me know. In the "Members Table" there is a field called
"Hobbies". I used this field to put the dates each individual was in the
squadron just like it is shown above. I just change the
name on the report so it displays "Yr in Squad". There are 175
individuals that we don't have the years in squadron listed. I was
able set a query criteria "is null" for "hobbies" and get all members
listed without years in squadron. I would like to break that
down into 10 groups as easily as possible, and then add each of these to
the ERA Groups, so each Group Leader will have about the
same amount of members. If you need any other information please let me
know.
Here's hoping you will be able to help figure this out.
Thanks, Frank

"Frank Lueder" wrote in message
...
I need help!

I am using the existing address database that comes with Microsoft office
for a roster of fellow shipmates. We use it for keeping
others in touch with one another. We have 1175 shipmates listed. We
recently established 10 era groups with a group coordinator
for each era. The field name I used was "hobbies" and on the report we
call it "years in squadron". The field is empty for some
shipmates (175 shipmates) as we don't have the years they were in the
squadron. We want to send a report of ten equal groups with
the groups in ascending order to each coordinator. The ones with no
years
we want to also break down evenly into ten separate
groups. The years in squadron field is like 1954-59 etc. I don't know
anything about writing the syntax to get the program to do
that, but I'm sure someone out there can help me. Thanking you in
advance....Frank

We are just using the "Hobbies" field with the date range string. We have no listing or field name for the ERA Groups. Maybe we
should? The problem we have is that we are constantly finding other shipmates and they are added to the master database, some will
have the years in the squadron and others don't. Our main goal is to not overload one ERA group leader with more members then the
others. We want to keep the groups as equal as possible. If Access would know the total number of members and be able to assign
them to one of the ten ERA group automatically when a new member is added and still keep the groups fairly equal, that would be
great.

When we decided on using 10 ERA Group leaders to help keep everyone informed rather then one person trying to do it all himself,
one of our members took the report of our total membership that I post on our MyFamily web site each month. I post it in .rtf and
..xls format so others can open it. First he used the .rtf format and used Microsoft Word to separate all the members without any
years listed. He cut and pasted them into 10 groups of approximate year groupings.

Second, he sorted the remainder by Years in Squadron, Ascending using SORT and then divided them into TEN Groups of approximately
the same size by cut and paste.

Third, he combined Grp1 and Grp1, then Grp2 and Grp2, etc. He divided the no years guys up about equally.

The whole process took him about 4 or 5 hours. I told him there must be a better way using Microsoft Access. Especially seeing
that's what we are using for a database.

I started working on it by taking the total number of members with the "Hobbies" field "not null" and sorting that field ascending.
Then I tried to use criteria to get a query to show the first 100 members. I couldn't figure out what syntax or function to use to
get access to do that. I thought if I could figure it out I would be able to do the same thing with the 2nd 100, etc with the 3rd
100. I thought I could then take the first 100 and put them into ERA Grp 1, the 2nd 100 into ERA Grp 2 etc, etc. for the other 8
groups. I would then have to do the same thing with those without the years listed. (Note: These ERA Groups are just a Microsoft
Word file named Grp1, Grp2 etc. Nothing to do with Access).

Thanks Ken,
Frank


"Ken Snell [MVP]" wrote in message ...
I'm not suggesting that you'll need to do any cutting/pasting/copying of
data manually. I envision that we can do this via some queries once we
identify clearly what is needed for identifying which group the members who
have no "dates of service" data.

I still need to know how you're identifying the specific group to which the
members belong -- are you using just the Hobbies field with a date range
string in it? Or do you also use another field with a group number in it?
And how will you "separate" the members by groups -- are you running a
report that just shows all members and notes which group the member is in?
or are you running queries that show just the members of each group, where
you specify the group?

If you're using just the Hobbies field, then it'll be necessary to put the
"group date range" string into that field for the members who currently have
no date range shown. But that will mean that your data will be incorrect for
those members, as they really don't have a date of service range at this
time.

I am willing to assist, but I need more information, per my questions.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
Because the groups would be unevenly divided by selecting (for example)
the "1946-51" ERA (this group might only have 25 individuals
where the "1969-72" group might have 200 individuals) we wanted each of
the 10 group leaders to have approximately equal amounts of
shipmates, even if they were not all within the years for that ERA.

To separate the individuals, I did a query with the "is Null" criteria in
the "Hobbies" field to get the members with no years. I
would have to cut and past them into 10 equal groups. Then I would run a
query with the "is not null" criteria and do the same
thing. I would then do an ascending sort on the "Hobbies" field (I used
that field as it was part of the existing Microsoft Access
database, in the report I just change it to "Yrs in Squadron"). That
would put the individuals in order of years they were in the
squadron. Then I would have to cut and past them into 10 equal groups and
combine each of those groups with the ones with no years.

I hope that helps. There must be an easier way to do the separation of
groups. I hope you can help.
Thanks, Frank Lueder


"Ken Snell [MVP]" wrote in message
...
Are you using the "1946-51" text string (for example) to know that that
member is part of ERA Grp 1? or are you storing ERA Grp 1 somewhere in the
table?

When you "separate" the members for mailing to different coordinators, are
you doing this via a query that gives you just the members for ERA Grp 1,
or
are you getting a list of all members and then physically splitting the
groups apart?

What I am asking is how do you "separate" the members into their groups so
that you can "split" them up? Somehow, the members with "blank date
ranges"
will need to be assigned to a group. Before I can give you a
recommendation,
I need to know how this is done.

--

Ken Snell
MS ACCESS MVP

"Frank Lueder" wrote in message
...
I want to let you know up front that I don't know that much about using
SQL
or Access. I was nominated as secretary of our Naval
Squadron Association (now decommissioned). Most all of the members are
no
longer in the military, but we are trying to keep them
all informed of what's happening with the association and keeping their
contact information up-to-date for reunion purposes. As
secretary I have to do that. I used the easiest method I could think of
and used the existing address data base in Access 2000. At
our last reunion we assigned 10 members to overlook and keep in contact
with their ERA. We wanted to break the total members into
10 Groups. Easier for me and not having to contact all 1175 members. Each
group leader would be responsible for keeping their
assigned individuals up to date.

The ERA Groups a
ERA Grp 1 Years 1946-51
ERA Grp 2 Years 1952-56
ERA Grp 3 Years 1957-58
ERA Grp 4 Years 1959-60
ERA Grp 5 Years 1961-62
ERA Grp 6 Years 1963-65
ERA Grp 7 Years 1966-69
ERA Grp 8 Years 1970-72
ERA Grp 9 Years 1973-83
ERA Grp 10 Years 1984-93

Total members at present time is 1175. I am using the "Members Table" &
the "Household Table". I'm not sure on how to copy them so
you can see them other than type them individually. If you need them let
me know. In the "Members Table" there is a field called
"Hobbies". I used this field to put the dates each individual was in the
squadron just like it is shown above. I just change the
name on the report so it displays "Yr in Squad". There are 175
individuals that we don't have the years in squadron listed. I was
able set a query criteria "is null" for "hobbies" and get all members
listed without years in squadron. I would like to break that
down into 10 groups as easily as possible, and then add each of these to
the ERA Groups, so each Group Leader will have about the
same amount of members. If you need any other information please let me
know.
Here's hoping you will be able to help figure this out.
Thanks, Frank

"Frank Lueder" wrote in message
...
I need help!

I am using the existing address database that comes with Microsoft office
for a roster of fellow shipmates. We use it for keeping
others in touch with one another. We have 1175 shipmates listed. We
recently established 10 era groups with a group coordinator
for each era. The field name I used was "hobbies" and on the report we
call it "years in squadron". The field is empty for some
shipmates (175 shipmates) as we don't have the years they were in the
squadron. We want to send a report of ten equal groups with
the groups in ascending order to each coordinator. The ones with no
years
we want to also break down evenly into ten separate
groups. The years in squadron field is like 1954-59 etc. I don't know
anything about writing the syntax to get the program to do
that, but I'm sure someone out there can help me. Thanking you in
advance....Frank

In order to assign each member to a group, you will need a field in the
table that holds a "group" number or letter or text string. That is what
then will allow you to separate the members by a group.

I recommend that you create a new table in the database:
tblGroups
GroupID Text field (50 characters) Primary key
GroupName Text field (255 characters)

In tblGroups, enter the following data (these are suggested entries, you can
use whichever values you wish):
GroupID GroupName
Grp01 ERA Group 1
Grp02 ERA Group 2
Grp03 ERA Group 3
Grp04 ERA Group 4
Grp05 ERA Group 5
Grp06 ERA Group 6
Grp07 ERA Group 7
Grp08 ERA Group 8
Grp09 ERA Group 9
Grp10 ERA Group 10

Then add a new field to your current table:
GroupID Text field (50 characters)

Now you'll be able to assign each member to a group. This can be done via
queries for all members.

To do for existing members, you can run an update query that inserts the
Grpxx value into the GroupID field in your current table, based on the date
range that is your Hobbies field. Something like this:

UPDATE CurrentTableName
SET GroupID =
Switch([Hobbies]="1946-51","Grp01",
[Hobbies]="1952-56","Grp02",
[Hobbies]="1957-58","Grp03",
[Hobbies]="1959-60","Grp04",
[Hobbies]="1961-62","Grp05",
[Hobbies]="1963-65","Grp06",
[Hobbies]="1966-69","Grp07",
[Hobbies]="1970-72","Grp08",
[Hobbies]="1973-83","Grp09",
[Hobbies]="1984-93","Grp10")
WHERE [Hobbies] Is Not Null;


Then, you can run 10 separate queries to assign the members (you said there
are 175) to each of the groups. With 175 members and 10 groups, that means
you can assign 18 members to groups 01 through 09, and then the remaining 13
to group10. To do this, you first create and save this select query that
gets us 18 of the members without a value in the Hobbies field (NOTE: this
query relies on the members table having a primary key!) :

SELECT TOP 18 PrimaryKeyField FROM CurrentTableName
WHERE [Hobbies] Is Null;

Let's assume that you name the above query "qry_Select18".

You then would run a query similar to the following query (making changes in
the "Grpxx" value for each time) 10 times:

UPDATE CurrentTableName
INNER JOIN qry_Select18
ON CurrentTableName.PrimaryKeyField =
qry_Select18.PrimaryKeyField
SET [Hobbies] = "Grp01";


You now have assigned each member to a group and now can easily "filter" the
members by groups.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
We are just using the "Hobbies" field with the date range string. We have
no listing or field name for the ERA Groups. Maybe we
should? The problem we have is that we are constantly finding other
shipmates and they are added to the master database, some will
have the years in the squadron and others don't. Our main goal is to not
overload one ERA group leader with more members then the
others. We want to keep the groups as equal as possible. If Access would
know the total number of members and be able to assign
them to one of the ten ERA group automatically when a new member is added
and still keep the groups fairly equal, that would be
great.

When we decided on using 10 ERA Group leaders to help keep everyone
informed rather then one person trying to do it all himself,
one of our members took the report of our total membership that I post on
our MyFamily web site each month. I post it in .rtf and
.xls format so others can open it. First he used the .rtf format and used
Microsoft Word to separate all the members without any
years listed. He cut and pasted them into 10 groups of approximate year
groupings.

Second, he sorted the remainder by Years in Squadron, Ascending using SORT
and then divided them into TEN Groups of approximately
the same size by cut and paste.

Third, he combined Grp1 and Grp1, then Grp2 and Grp2, etc. He divided the
no years guys up about equally.

The whole process took him about 4 or 5 hours. I told him there must be a
better way using Microsoft Access. Especially seeing
that's what we are using for a database.

I started working on it by taking the total number of members with the
"Hobbies" field "not null" and sorting that field ascending.
Then I tried to use criteria to get a query to show the first 100 members.
I couldn't figure out what syntax or function to use to
get access to do that. I thought if I could figure it out I would be able
to do the same thing with the 2nd 100, etc with the 3rd
100. I thought I could then take the first 100 and put them into ERA Grp
1, the 2nd 100 into ERA Grp 2 etc, etc. for the other 8
groups. I would then have to do the same thing with those without the
years listed. (Note: These ERA Groups are just a Microsoft
Word file named Grp1, Grp2 etc. Nothing to do with Access).

Thanks Ken,
Frank


"Ken Snell [MVP]" wrote in message
...
I'm not suggesting that you'll need to do any cutting/pasting/copying of
data manually. I envision that we can do this via some queries once we
identify clearly what is needed for identifying which group the members
who
have no "dates of service" data.

I still need to know how you're identifying the specific group to which
the
members belong -- are you using just the Hobbies field with a date range
string in it? Or do you also use another field with a group number in it?
And how will you "separate" the members by groups -- are you running a
report that just shows all members and notes which group the member is in?
or are you running queries that show just the members of each group, where
you specify the group?

If you're using just the Hobbies field, then it'll be necessary to put the
"group date range" string into that field for the members who currently
have
no date range shown. But that will mean that your data will be incorrect
for
those members, as they really don't have a date of service range at this
time.

I am willing to assist, but I need more information, per my questions.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
Because the groups would be unevenly divided by selecting (for example)
the "1946-51" ERA (this group might only have 25 individuals
where the "1969-72" group might have 200 individuals) we wanted each of
the 10 group leaders to have approximately equal amounts of
shipmates, even if they were not all within the years for that ERA.

To separate the individuals, I did a query with the "is Null" criteria in
the "Hobbies" field to get the members with no years. I
would have to cut and past them into 10 equal groups. Then I would run a
query with the "is not null" criteria and do the same
thing. I would then do an ascending sort on the "Hobbies" field (I used
that field as it was part of the existing Microsoft Access
database, in the report I just change it to "Yrs in Squadron"). That
would put the individuals in order of years they were in the
squadron. Then I would have to cut and past them into 10 equal groups
and
combine each of those groups with the ones with no years.

I hope that helps. There must be an easier way to do the separation of
groups. I hope you can help.
Thanks, Frank Lueder


"Ken Snell [MVP]" wrote in message
...
Are you using the "1946-51" text string (for example) to know that that
member is part of ERA Grp 1? or are you storing ERA Grp 1 somewhere in
the
table?

When you "separate" the members for mailing to different coordinators,
are
you doing this via a query that gives you just the members for ERA Grp 1,
or
are you getting a list of all members and then physically splitting the
groups apart?

What I am asking is how do you "separate" the members into their groups
so
that you can "split" them up? Somehow, the members with "blank date
ranges"
will need to be assigned to a group. Before I can give you a
recommendation,
I need to know how this is done.

--

Ken Snell
MS ACCESS MVP

"Frank Lueder" wrote in message
...
I want to let you know up front that I don't know that much about using
SQL
or Access. I was nominated as secretary of our Naval
Squadron Association (now decommissioned). Most all of the members are
no
longer in the military, but we are trying to keep them
all informed of what's happening with the association and keeping their
contact information up-to-date for reunion purposes. As
secretary I have to do that. I used the easiest method I could think of
and used the existing address data base in Access 2000. At
our last reunion we assigned 10 members to overlook and keep in contact
with their ERA. We wanted to break the total members into
10 Groups. Easier for me and not having to contact all 1175 members.
Each
group leader would be responsible for keeping their
assigned individuals up to date.

The ERA Groups a
ERA Grp 1 Years 1946-51
ERA Grp 2 Years 1952-56
ERA Grp 3 Years 1957-58
ERA Grp 4 Years 1959-60
ERA Grp 5 Years 1961-62
ERA Grp 6 Years 1963-65
ERA Grp 7 Years 1966-69
ERA Grp 8 Years 1970-72
ERA Grp 9 Years 1973-83
ERA Grp 10 Years 1984-93

Total members at present time is 1175. I am using the "Members Table" &
the "Household Table". I'm not sure on how to copy them so
you can see them other than type them individually. If you need them
let
me know. In the "Members Table" there is a field called
"Hobbies". I used this field to put the dates each individual was in
the
squadron just like it is shown above. I just change the
name on the report so it displays "Yr in Squad". There are 175
individuals that we don't have the years in squadron listed. I was
able set a query criteria "is null" for "hobbies" and get all members
listed without years in squadron. I would like to break that
down into 10 groups as easily as possible, and then add each of these to
the ERA Groups, so each Group Leader will have about the
same amount of members. If you need any other information please let me
know.
Here's hoping you will be able to help figure this out.
Thanks, Frank

"Frank Lueder" wrote in message
...
I need help!

I am using the existing address database that comes with Microsoft
office
for a roster of fellow shipmates. We use it for keeping
others in touch with one another. We have 1175 shipmates listed. We
recently established 10 era groups with a group coordinator
for each era. The field name I used was "hobbies" and on the report we
call it "years in squadron". The field is empty for some
shipmates (175 shipmates) as we don't have the years they were in the
squadron. We want to send a report of ten equal groups with
the groups in ascending order to each coordinator. The ones with no
years
we want to also break down evenly into ten separate
groups. The years in squadron field is like 1954-59 etc. I don't know
anything about writing the syntax to get the program to do
that, but I'm sure someone out there can help me. Thanking you in
advance....Frank

Forgot to mention that, after you run all the queries, thenyou can
establish a relationship between the GroupID field in the new table and the
GroupID field in the current table. You also can use this new table to
display the group's name in forms and reports instead of having to show the
"GroupID" value.

--

Ken Snell
MS ACCESS MVP

"Ken Snell [MVP]" wrote in message
...
In order to assign each member to a group, you will need a field in the
table that holds a "group" number or letter or text string. That is what
then will allow you to separate the members by a group.

I recommend that you create a new table in the database:
tblGroups
GroupID Text field (50 characters) Primary key
GroupName Text field (255 characters)

In tblGroups, enter the following data (these are suggested entries, you
can use whichever values you wish):
GroupID GroupName
Grp01 ERA Group 1
Grp02 ERA Group 2
Grp03 ERA Group 3
Grp04 ERA Group 4
Grp05 ERA Group 5
Grp06 ERA Group 6
Grp07 ERA Group 7
Grp08 ERA Group 8
Grp09 ERA Group 9
Grp10 ERA Group 10

Then add a new field to your current table:
GroupID Text field (50 characters)

Now you'll be able to assign each member to a group. This can be done via
queries for all members.

To do for existing members, you can run an update query that inserts the
Grpxx value into the GroupID field in your current table, based on the
date range that is your Hobbies field. Something like this:

UPDATE CurrentTableName
SET GroupID =
Switch([Hobbies]="1946-51","Grp01",
[Hobbies]="1952-56","Grp02",
[Hobbies]="1957-58","Grp03",
[Hobbies]="1959-60","Grp04",
[Hobbies]="1961-62","Grp05",
[Hobbies]="1963-65","Grp06",
[Hobbies]="1966-69","Grp07",
[Hobbies]="1970-72","Grp08",
[Hobbies]="1973-83","Grp09",
[Hobbies]="1984-93","Grp10")
WHERE [Hobbies] Is Not Null;


Then, you can run 10 separate queries to assign the members (you said
there are 175) to each of the groups. With 175 members and 10 groups, that
means you can assign 18 members to groups 01 through 09, and then the
remaining 13 to group10. To do this, you first create and save this select
query that gets us 18 of the members without a value in the Hobbies field
(NOTE: this query relies on the members table having a primary key!) :

SELECT TOP 18 PrimaryKeyField FROM CurrentTableName
WHERE [Hobbies] Is Null;

Let's assume that you name the above query "qry_Select18".

You then would run a query similar to the following query (making changes
in the "Grpxx" value for each time) 10 times:

UPDATE CurrentTableName
INNER JOIN qry_Select18
ON CurrentTableName.PrimaryKeyField =
qry_Select18.PrimaryKeyField
SET [Hobbies] = "Grp01";


You now have assigned each member to a group and now can easily "filter"
the members by groups.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
We are just using the "Hobbies" field with the date range string. We
have no listing or field name for the ERA Groups. Maybe we
should? The problem we have is that we are constantly finding other
shipmates and they are added to the master database, some will
have the years in the squadron and others don't. Our main goal is to not
overload one ERA group leader with more members then the
others. We want to keep the groups as equal as possible. If Access
would know the total number of members and be able to assign
them to one of the ten ERA group automatically when a new member is added
and still keep the groups fairly equal, that would be
great.

When we decided on using 10 ERA Group leaders to help keep everyone
informed rather then one person trying to do it all himself,
one of our members took the report of our total membership that I post on
our MyFamily web site each month. I post it in .rtf and
.xls format so others can open it. First he used the .rtf format and
used Microsoft Word to separate all the members without any
years listed. He cut and pasted them into 10 groups of approximate year
groupings.

Second, he sorted the remainder by Years in Squadron, Ascending using
SORT and then divided them into TEN Groups of approximately
the same size by cut and paste.

Third, he combined Grp1 and Grp1, then Grp2 and Grp2, etc. He divided
the no years guys up about equally.

The whole process took him about 4 or 5 hours. I told him there must be
a better way using Microsoft Access. Especially seeing
that's what we are using for a database.

I started working on it by taking the total number of members with the
"Hobbies" field "not null" and sorting that field ascending.
Then I tried to use criteria to get a query to show the first 100
members. I couldn't figure out what syntax or function to use to
get access to do that. I thought if I could figure it out I would be
able to do the same thing with the 2nd 100, etc with the 3rd
100. I thought I could then take the first 100 and put them into ERA
Grp 1, the 2nd 100 into ERA Grp 2 etc, etc. for the other 8
groups. I would then have to do the same thing with those without the
years listed. (Note: These ERA Groups are just a Microsoft
Word file named Grp1, Grp2 etc. Nothing to do with Access).

Thanks Ken,
Frank


"Ken Snell [MVP]" wrote in message
...
I'm not suggesting that you'll need to do any cutting/pasting/copying of
data manually. I envision that we can do this via some queries once we
identify clearly what is needed for identifying which group the members
who
have no "dates of service" data.

I still need to know how you're identifying the specific group to which
the
members belong -- are you using just the Hobbies field with a date range
string in it? Or do you also use another field with a group number in it?
And how will you "separate" the members by groups -- are you running a
report that just shows all members and notes which group the member is
in?
or are you running queries that show just the members of each group,
where
you specify the group?

If you're using just the Hobbies field, then it'll be necessary to put
the
"group date range" string into that field for the members who currently
have
no date range shown. But that will mean that your data will be incorrect
for
those members, as they really don't have a date of service range at this
time.

I am willing to assist, but I need more information, per my questions.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
Because the groups would be unevenly divided by selecting (for example)
the "1946-51" ERA (this group might only have 25 individuals
where the "1969-72" group might have 200 individuals) we wanted each of
the 10 group leaders to have approximately equal amounts of
shipmates, even if they were not all within the years for that ERA.

To separate the individuals, I did a query with the "is Null" criteria
in
the "Hobbies" field to get the members with no years. I
would have to cut and past them into 10 equal groups. Then I would run
a
query with the "is not null" criteria and do the same
thing. I would then do an ascending sort on the "Hobbies" field (I used
that field as it was part of the existing Microsoft Access
database, in the report I just change it to "Yrs in Squadron"). That
would put the individuals in order of years they were in the
squadron. Then I would have to cut and past them into 10 equal groups
and
combine each of those groups with the ones with no years.

I hope that helps. There must be an easier way to do the separation of
groups. I hope you can help.
Thanks, Frank Lueder


"Ken Snell [MVP]" wrote in message
...
Are you using the "1946-51" text string (for example) to know that that
member is part of ERA Grp 1? or are you storing ERA Grp 1 somewhere in
the
table?

When you "separate" the members for mailing to different coordinators,
are
you doing this via a query that gives you just the members for ERA Grp
1,
or
are you getting a list of all members and then physically splitting the
groups apart?

What I am asking is how do you "separate" the members into their groups
so
that you can "split" them up? Somehow, the members with "blank date
ranges"
will need to be assigned to a group. Before I can give you a
recommendation,
I need to know how this is done.

--

Ken Snell
MS ACCESS MVP

"Frank Lueder" wrote in message
...
I want to let you know up front that I don't know that much about using
SQL
or Access. I was nominated as secretary of our Naval
Squadron Association (now decommissioned). Most all of the members are
no
longer in the military, but we are trying to keep them
all informed of what's happening with the association and keeping their
contact information up-to-date for reunion purposes. As
secretary I have to do that. I used the easiest method I could think
of
and used the existing address data base in Access 2000. At
our last reunion we assigned 10 members to overlook and keep in contact
with their ERA. We wanted to break the total members into
10 Groups. Easier for me and not having to contact all 1175 members.
Each
group leader would be responsible for keeping their
assigned individuals up to date.

The ERA Groups a
ERA Grp 1 Years 1946-51
ERA Grp 2 Years 1952-56
ERA Grp 3 Years 1957-58
ERA Grp 4 Years 1959-60
ERA Grp 5 Years 1961-62
ERA Grp 6 Years 1963-65
ERA Grp 7 Years 1966-69
ERA Grp 8 Years 1970-72
ERA Grp 9 Years 1973-83
ERA Grp 10 Years 1984-93

Total members at present time is 1175. I am using the "Members Table"
&
the "Household Table". I'm not sure on how to copy them so
you can see them other than type them individually. If you need them
let
me know. In the "Members Table" there is a field called
"Hobbies". I used this field to put the dates each individual was in
the
squadron just like it is shown above. I just change the
name on the report so it displays "Yr in Squad". There are 175
individuals that we don't have the years in squadron listed. I was
able set a query criteria "is null" for "hobbies" and get all members
listed without years in squadron. I would like to break that
down into 10 groups as easily as possible, and then add each of these
to
the ERA Groups, so each Group Leader will have about the
same amount of members. If you need any other information please let
me
know.
Here's hoping you will be able to help figure this out.
Thanks, Frank

"Frank Lueder" wrote in message
...
I need help!

I am using the existing address database that comes with Microsoft
office
for a roster of fellow shipmates. We use it for keeping
others in touch with one another. We have 1175 shipmates listed. We
recently established 10 era groups with a group coordinator
for each era. The field name I used was "hobbies" and on the report we
call it "years in squadron". The field is empty for some
shipmates (175 shipmates) as we don't have the years they were in the
squadron. We want to send a report of ten equal groups with
the groups in ascending order to each coordinator. The ones with no
years
we want to also break down evenly into ten separate
groups. The years in squadron field is like 1954-59 etc. I don't know
anything about writing the syntax to get the program to do
that, but I'm sure someone out there can help me. Thanking you in
advance....Frank

I created the new table (TblGroups) & added the fields (GroupID) & (GroupName). I then entered the data as you show it below. I
ran the update query and it couldn't find the Current Table Name, so I put (TblGroup) in, then it asked for the (Hobbies) parameter
value. I entered "1946-51". It then asked for the (GroupID) parameter, I entered Grp01, then it said "the operation must use an
updateable query". Do I need to make a new query? I fooled around with one of the queries I had already done. When I copied and
pasted the data below for the update query and ran it. It made a list of all members but there were 10 of each listed. When I
checked the new table (TblGroup) it had changed the GroupID entries to all Grp01's. I must be doing something wrong. Could you
steer me in the right direction.
Thanks, Frank

"Ken Snell [MVP]" wrote in message ...
In order to assign each member to a group, you will need a field in the
table that holds a "group" number or letter or text string. That is what
then will allow you to separate the members by a group.

I recommend that you create a new table in the database:
tblGroups
GroupID Text field (50 characters) Primary key
GroupName Text field (255 characters)

In tblGroups, enter the following data (these are suggested entries, you can
use whichever values you wish):
GroupID GroupName
Grp01 ERA Group 1
Grp02 ERA Group 2
Grp03 ERA Group 3
Grp04 ERA Group 4
Grp05 ERA Group 5
Grp06 ERA Group 6
Grp07 ERA Group 7
Grp08 ERA Group 8
Grp09 ERA Group 9
Grp10 ERA Group 10

Then add a new field to your current table:
GroupID Text field (50 characters)

Now you'll be able to assign each member to a group. This can be done via
queries for all members.

To do for existing members, you can run an update query that inserts the
Grpxx value into the GroupID field in your current table, based on the date
range that is your Hobbies field. Something like this:

UPDATE CurrentTableName
SET GroupID =
Switch([Hobbies]="1946-51","Grp01",
[Hobbies]="1952-56","Grp02",
[Hobbies]="1957-58","Grp03",
[Hobbies]="1959-60","Grp04",
[Hobbies]="1961-62","Grp05",
[Hobbies]="1963-65","Grp06",
[Hobbies]="1966-69","Grp07",
[Hobbies]="1970-72","Grp08",
[Hobbies]="1973-83","Grp09",
[Hobbies]="1984-93","Grp10")
WHERE [Hobbies] Is Not Null;


Then, you can run 10 separate queries to assign the members (you said there
are 175) to each of the groups. With 175 members and 10 groups, that means
you can assign 18 members to groups 01 through 09, and then the remaining 13
to group10. To do this, you first create and save this select query that
gets us 18 of the members without a value in the Hobbies field (NOTE: this
query relies on the members table having a primary key!) :

SELECT TOP 18 PrimaryKeyField FROM CurrentTableName
WHERE [Hobbies] Is Null;

Let's assume that you name the above query "qry_Select18".

You then would run a query similar to the following query (making changes in
the "Grpxx" value for each time) 10 times:

UPDATE CurrentTableName
INNER JOIN qry_Select18
ON CurrentTableName.PrimaryKeyField =
qry_Select18.PrimaryKeyField
SET [Hobbies] = "Grp01";


You now have assigned each member to a group and now can easily "filter" the
members by groups.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
We are just using the "Hobbies" field with the date range string. We have
no listing or field name for the ERA Groups. Maybe we
should? The problem we have is that we are constantly finding other
shipmates and they are added to the master database, some will
have the years in the squadron and others don't. Our main goal is to not
overload one ERA group leader with more members then the
others. We want to keep the groups as equal as possible. If Access would
know the total number of members and be able to assign
them to one of the ten ERA group automatically when a new member is added
and still keep the groups fairly equal, that would be
great.

When we decided on using 10 ERA Group leaders to help keep everyone
informed rather then one person trying to do it all himself,
one of our members took the report of our total membership that I post on
our MyFamily web site each month. I post it in .rtf and
.xls format so others can open it. First he used the .rtf format and used
Microsoft Word to separate all the members without any
years listed. He cut and pasted them into 10 groups of approximate year
groupings.

Second, he sorted the remainder by Years in Squadron, Ascending using SORT
and then divided them into TEN Groups of approximately
the same size by cut and paste.

Third, he combined Grp1 and Grp1, then Grp2 and Grp2, etc. He divided the
no years guys up about equally.

The whole process took him about 4 or 5 hours. I told him there must be a
better way using Microsoft Access. Especially seeing
that's what we are using for a database.

I started working on it by taking the total number of members with the
"Hobbies" field "not null" and sorting that field ascending.
Then I tried to use criteria to get a query to show the first 100 members.
I couldn't figure out what syntax or function to use to
get access to do that. I thought if I could figure it out I would be able
to do the same thing with the 2nd 100, etc with the 3rd
100. I thought I could then take the first 100 and put them into ERA Grp
1, the 2nd 100 into ERA Grp 2 etc, etc. for the other 8
groups. I would then have to do the same thing with those without the
years listed. (Note: These ERA Groups are just a Microsoft
Word file named Grp1, Grp2 etc. Nothing to do with Access).

Thanks Ken,
Frank


"Ken Snell [MVP]" wrote in message
...
I'm not suggesting that you'll need to do any cutting/pasting/copying of
data manually. I envision that we can do this via some queries once we
identify clearly what is needed for identifying which group the members
who
have no "dates of service" data.

I still need to know how you're identifying the specific group to which
the
members belong -- are you using just the Hobbies field with a date range
string in it? Or do you also use another field with a group number in it?
And how will you "separate" the members by groups -- are you running a
report that just shows all members and notes which group the member is in?
or are you running queries that show just the members of each group, where
you specify the group?

If you're using just the Hobbies field, then it'll be necessary to put the
"group date range" string into that field for the members who currently
have
no date range shown. But that will mean that your data will be incorrect
for
those members, as they really don't have a date of service range at this
time.

I am willing to assist, but I need more information, per my questions.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
Because the groups would be unevenly divided by selecting (for example)
the "1946-51" ERA (this group might only have 25 individuals
where the "1969-72" group might have 200 individuals) we wanted each of
the 10 group leaders to have approximately equal amounts of
shipmates, even if they were not all within the years for that ERA.

To separate the individuals, I did a query with the "is Null" criteria in
the "Hobbies" field to get the members with no years. I
would have to cut and past them into 10 equal groups. Then I would run a
query with the "is not null" criteria and do the same
thing. I would then do an ascending sort on the "Hobbies" field (I used
that field as it was part of the existing Microsoft Access
database, in the report I just change it to "Yrs in Squadron"). That
would put the individuals in order of years they were in the
squadron. Then I would have to cut and past them into 10 equal groups
and
combine each of those groups with the ones with no years.

I hope that helps. There must be an easier way to do the separation of
groups. I hope you can help.
Thanks, Frank Lueder


"Ken Snell [MVP]" wrote in message
...
Are you using the "1946-51" text string (for example) to know that that
member is part of ERA Grp 1? or are you storing ERA Grp 1 somewhere in
the
table?

When you "separate" the members for mailing to different coordinators,
are
you doing this via a query that gives you just the members for ERA Grp 1,
or
are you getting a list of all members and then physically splitting the
groups apart?

What I am asking is how do you "separate" the members into their groups
so
that you can "split" them up? Somehow, the members with "blank date
ranges"
will need to be assigned to a group. Before I can give you a
recommendation,
I need to know how this is done.

--

Ken Snell
MS ACCESS MVP

"Frank Lueder" wrote in message
...
I want to let you know up front that I don't know that much about using
SQL
or Access. I was nominated as secretary of our Naval
Squadron Association (now decommissioned). Most all of the members are
no
longer in the military, but we are trying to keep them
all informed of what's happening with the association and keeping their
contact information up-to-date for reunion purposes. As
secretary I have to do that. I used the easiest method I could think of
and used the existing address data base in Access 2000. At
our last reunion we assigned 10 members to overlook and keep in contact
with their ERA. We wanted to break the total members into
10 Groups. Easier for me and not having to contact all 1175 members.
Each
group leader would be responsible for keeping their
assigned individuals up to date.

The ERA Groups a
ERA Grp 1 Years 1946-51
ERA Grp 2 Years 1952-56
ERA Grp 3 Years 1957-58
ERA Grp 4 Years 1959-60
ERA Grp 5 Years 1961-62
ERA Grp 6 Years 1963-65
ERA Grp 7 Years 1966-69
ERA Grp 8 Years 1970-72
ERA Grp 9 Years 1973-83
ERA Grp 10 Years 1984-93

Total members at present time is 1175. I am using the "Members Table" &
the "Household Table". I'm not sure on how to copy them so
you can see them other than type them individually. If you need them
let
me know. In the "Members Table" there is a field called
"Hobbies". I used this field to put the dates each individual was in
the
squadron just like it is shown above. I just change the
name on the report so it displays "Yr in Squad". There are 175
individuals that we don't have the years in squadron listed. I was
able set a query criteria "is null" for "hobbies" and get all members
listed without years in squadron. I would like to break that
down into 10 groups as easily as possible, and then add each of these to
the ERA Groups, so each Group Leader will have about the
same amount of members. If you need any other information please let me
know.
Here's hoping you will be able to help figure this out.
Thanks, Frank

"Frank Lueder" wrote in message
...
I need help!

I am using the existing address database that comes with Microsoft
office
for a roster of fellow shipmates. We use it for keeping
others in touch with one another. We have 1175 shipmates listed. We
recently established 10 era groups with a group coordinator
for each era. The field name I used was "hobbies" and on the report we
call it "years in squadron". The field is empty for some
shipmates (175 shipmates) as we don't have the years they were in the
squadron. We want to send a report of ten equal groups with
the groups in ascending order to each coordinator. The ones with no
years
we want to also break down evenly into ten separate
groups. The years in squadron field is like 1954-59 etc. I don't know
anything about writing the syntax to get the program to do
that, but I'm sure someone out there can help me. Thanking you in
advance....Frank