Syntax needed to get needed reports

In the update query that I posted, CurrentTableName should be replaced by
the name of the table that contains the Hobbies field, which I believe is
Members. Also, you need to add the GroupID field to this Members table, in
addition to having put into the new table (tblGroup).

After you add the new field to the Members table, then run this update
query:

UPDATE Members
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;

You also will need to change the data in tblGroups back to what I'd posted
initially.
--

Ken Snell
MS ACCESS MVP


"Frank Lueder" wrote in message
...
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

The query worked partially. It found the exact [Hobbies]="1952-56" for instance and put "Grp02" into the GroupID, but some squadron
members were in say 1952-53 or 1952-54 or 1952-55 that should be in that group. How do we include those also? It's the same for
the other 9 groups also.
Thanks, Frank Lueder

"Ken Snell [MVP]" wrote in message ...
In the update query that I posted, CurrentTableName should be replaced by
the name of the table that contains the Hobbies field, which I believe is
Members. Also, you need to add the GroupID field to this Members table, in
addition to having put into the new table (tblGroup).

After you add the new field to the Members table, then run this update
query:

UPDATE Members
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;

You also will need to change the data in tblGroups back to what I'd posted
initially.
--

Ken Snell
MS ACCESS MVP


"Frank Lueder" wrote in message
...
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

Because your data are not consistent in that field, you will need to modify
the query to change the date range text strings to the values that are in
Hobbies field. In the query, the Switch function is finding a match for the
value that is in the Hobbies field, and then putting the "corresponding"
Grpxx string into the GroupID field.

So, for example, to assign members with a Hobbies value of "1952-53" or
"1952-54" or "1952-55" to "Grp02", the query would be this:
UPDATE Members
SET GroupID =
Switch([Hobbies]="1952-53" Or [Hobbies]="1952-54" Or
[Hobbies]="1952-553","Grp02")
WHERE [Hobbies] Is Not Null;

And so on for the other "nonmatched" values that are in your Hobbies field.

However, this effort will be just a "one-time" action. After you've
populated the GroupID field in the Members table, then you can assign new
members to an appropriate group by simply entering the correct GroupID value
for the group to which the new member will be assigned.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
The query worked partially. It found the exact [Hobbies]="1952-56" for
instance and put "Grp02" into the GroupID, but some squadron
members were in say 1952-53 or 1952-54 or 1952-55 that should be in that
group. How do we include those also? It's the same for
the other 9 groups also.
Thanks, Frank Lueder

"Ken Snell [MVP]" wrote in message
...
In the update query that I posted, CurrentTableName should be replaced by
the name of the table that contains the Hobbies field, which I believe is
Members. Also, you need to add the GroupID field to this Members table,
in
addition to having put into the new table (tblGroup).

After you add the new field to the Members table, then run this update
query:

UPDATE Members
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;

You also will need to change the data in tblGroups back to what I'd posted
initially.
--

Ken Snell
MS ACCESS MVP


"Frank Lueder" wrote in message
...
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

I would have to use the examples below plus 1953-54 or 1953-55 or 1953-55 or 1954-55 or 1954-56 etc. etc. Could I use Between
"1952" And "1956-99" somehow. I tried it and it gives me a systax error (missing operator) in query expression.
Thanks, Frank Lueder

"Ken Snell [MVP]" wrote in message ...
Because your data are not consistent in that field, you will need to modify
the query to change the date range text strings to the values that are in
Hobbies field. In the query, the Switch function is finding a match for the
value that is in the Hobbies field, and then putting the "corresponding"
Grpxx string into the GroupID field.

So, for example, to assign members with a Hobbies value of "1952-53" or
"1952-54" or "1952-55" to "Grp02", the query would be this:
UPDATE Members
SET GroupID =
Switch([Hobbies]="1952-53" Or [Hobbies]="1952-54" Or
[Hobbies]="1952-553","Grp02")
WHERE [Hobbies] Is Not Null;

And so on for the other "nonmatched" values that are in your Hobbies field.

However, this effort will be just a "one-time" action. After you've
populated the GroupID field in the Members table, then you can assign new
members to an appropriate group by simply entering the correct GroupID value
for the group to which the new member will be assigned.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
The query worked partially. It found the exact [Hobbies]="1952-56" for
instance and put "Grp02" into the GroupID, but some squadron
members were in say 1952-53 or 1952-54 or 1952-55 that should be in that
group. How do we include those also? It's the same for
the other 9 groups also.
Thanks, Frank Lueder

"Ken Snell [MVP]" wrote in message
...
In the update query that I posted, CurrentTableName should be replaced by
the name of the table that contains the Hobbies field, which I believe is
Members. Also, you need to add the GroupID field to this Members table,
in
addition to having put into the new table (tblGroup).

After you add the new field to the Members table, then run this update
query:

UPDATE Members
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;

You also will need to change the data in tblGroups back to what I'd posted
initially.
--

Ken Snell
MS ACCESS MVP


"Frank Lueder" wrote in message
...
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

No, you cannot use such an expression because your values are text strings.

Unless you can identify what is "unique" for each combination of date range
text strings (e.g., all of the ones for Grp01 contain 195, or 196, or 194,
or something like this), then you will have to do them as groups, the way I
posted in the last reply.

If you can identify something in the string that makes a group unique, then
I can assist you in writing an expression that should work for you.
Remember -- I cannot see all your data, I know only as much as you post in
this thread.
--

Ken Snell
MS ACCESS MVP



"Frank Lueder" wrote in message
...
I would have to use the examples below plus 1953-54 or 1953-55 or 1953-55
or 1954-55 or 1954-56 etc. etc. Could I use Between
"1952" And "1956-99" somehow. I tried it and it gives me a systax error
(missing operator) in query expression.
Thanks, Frank Lueder

"Ken Snell [MVP]" wrote in message
...
Because your data are not consistent in that field, you will need to
modify
the query to change the date range text strings to the values that are in
Hobbies field. In the query, the Switch function is finding a match for
the
value that is in the Hobbies field, and then putting the "corresponding"
Grpxx string into the GroupID field.

So, for example, to assign members with a Hobbies value of "1952-53" or
"1952-54" or "1952-55" to "Grp02", the query would be this:
UPDATE Members
SET GroupID =
Switch([Hobbies]="1952-53" Or [Hobbies]="1952-54" Or
[Hobbies]="1952-553","Grp02")
WHERE [Hobbies] Is Not Null;

And so on for the other "nonmatched" values that are in your Hobbies
field.

However, this effort will be just a "one-time" action. After you've
populated the GroupID field in the Members table, then you can assign new
members to an appropriate group by simply entering the correct GroupID
value
for the group to which the new member will be assigned.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
The query worked partially. It found the exact [Hobbies]="1952-56" for
instance and put "Grp02" into the GroupID, but some squadron
members were in say 1952-53 or 1952-54 or 1952-55 that should be in that
group. How do we include those also? It's the same for
the other 9 groups also.
Thanks, Frank Lueder

"Ken Snell [MVP]" wrote in message
...
In the update query that I posted, CurrentTableName should be replaced by
the name of the table that contains the Hobbies field, which I believe is
Members. Also, you need to add the GroupID field to this Members table,
in
addition to having put into the new table (tblGroup).

After you add the new field to the Members table, then run this update
query:

UPDATE Members
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;

You also will need to change the data in tblGroups back to what I'd
posted
initially.
--

Ken Snell
MS ACCESS MVP


"Frank Lueder" wrote in message
...
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

Ken, I ran the update query below to see what happens. It populated the entire (Members) Table, (GroupID) with Grp01 except of
course for the null fields. I then ran the update query with what you originally suggested and it just populated those fields that
were identical to what was between the "". I just wanted to try two groups to see if it would work before typing in the remaining 8
groups.



UPDATE Members

SET GroupID =

Switch([Hobbies]="1946-51" or "1946-48" or "1947-49" or "1947-50" or "1948-50" or "1948-51" or "1948-52" or "1949-51" or "1949-52"
or "1950" or "1950-51" or "1950-52" or "1050-51 MIA" or "1950-53" or "1950-54" or "1950's" or "1951" or "1951-52" or "1951-53" or
"1951-54" or "1952" or "1951-55","Grp01",

[Hobbies]= "1952-53" or "1952-54" or "1952-55" or "1953" or "1953-54" or "1953-55" or "1953-56" or "1953-57" or "1954-55" or
"1954-56" or "1954-57" or "1954-58" or "1954-59" or "1955-56" or "1955-57" or "1955-58" or "1955-60" or "1955-66" or "1956" or
"1956-57" or "1956-58" or "1956-59" or "1956-60" or "1956-60" or "1956-62" or "1952-56","Grp02")

WHERE [Hobbies] Is Not Null;



Thanks, Frank



"Ken Snell [MVP]" wrote in message ...
No, you cannot use such an expression because your values are text strings.

Unless you can identify what is "unique" for each combination of date range
text strings (e.g., all of the ones for Grp01 contain 195, or 196, or 194,
or something like this), then you will have to do them as groups, the way I
posted in the last reply.

If you can identify something in the string that makes a group unique, then
I can assist you in writing an expression that should work for you.
Remember -- I cannot see all your data, I know only as much as you post in
this thread.
--

Ken Snell
MS ACCESS MVP



"Frank Lueder" wrote in message
...
I would have to use the examples below plus 1953-54 or 1953-55 or 1953-55
or 1954-55 or 1954-56 etc. etc. Could I use Between
"1952" And "1956-99" somehow. I tried it and it gives me a systax error
(missing operator) in query expression.
Thanks, Frank Lueder

"Ken Snell [MVP]" wrote in message
...
Because your data are not consistent in that field, you will need to
modify
the query to change the date range text strings to the values that are in
Hobbies field. In the query, the Switch function is finding a match for
the
value that is in the Hobbies field, and then putting the "corresponding"
Grpxx string into the GroupID field.

So, for example, to assign members with a Hobbies value of "1952-53" or
"1952-54" or "1952-55" to "Grp02", the query would be this:
UPDATE Members
SET GroupID =
Switch([Hobbies]="1952-53" Or [Hobbies]="1952-54" Or
[Hobbies]="1952-553","Grp02")
WHERE [Hobbies] Is Not Null;

And so on for the other "nonmatched" values that are in your Hobbies
field.

However, this effort will be just a "one-time" action. After you've
populated the GroupID field in the Members table, then you can assign new
members to an appropriate group by simply entering the correct GroupID
value
for the group to which the new member will be assigned.
--

Ken Snell
MS ACCESS MVP




"Frank Lueder" wrote in message
...
The query worked partially. It found the exact [Hobbies]="1952-56" for
instance and put "Grp02" into the GroupID, but some squadron
members were in say 1952-53 or 1952-54 or 1952-55 that should be in that
group. How do we include those also? It's the same for
the other 9 groups also.
Thanks, Frank Lueder

"Ken Snell [MVP]" wrote in message
...
In the update query that I posted, CurrentTableName should be replaced by
the name of the table that contains the Hobbies field, which I believe is
Members. Also, you need to add the GroupID field to this Members table,
in
addition to having put into the new table (tblGroup).

After you add the new field to the Members table, then run this update
query:

UPDATE Members
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;

You also will need to change the data in tblGroups back to what I'd
posted
initially.
--

Ken Snell
MS ACCESS MVP


"Frank Lueder" wrote in message
...
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