Re: Syntax needed to get needed reports
From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 01/06/05
- Next message: TheScullster: "Re: Run Time Error"
- Previous message: anonymous_at_discussions.microsoft.com: "Listbox and images"
- In reply to: Frank Lueder: "Re: Syntax needed to get needed reports"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 6 Jan 2005 09:16:39 -0500
Yes, what you posted as a query will not work for you because you must
repeat the [Hobbies]= part for each test.
I trust that all is well now? Let me know if not.
--
Ken Snell
<MS ACCESS MVP>
"Frank Lueder" <luederf001@hawaii.rr.com> wrote in message
news:MO6Dd.51706$nP1.33810@twister.socal.rr.com...
> 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]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
> news:OBQqQQ88EHA.3124@TK2MSFTNGP11.phx.gbl...
> 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" <luederf001@hawaii.rr.com> wrote in message
> news:M75Dd.51698$nP1.24222@twister.socal.rr.com...
>>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]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
>> news:%236di0%2368EHA.3012@TK2MSFTNGP09.phx.gbl...
>> 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" <luederf001@hawaii.rr.com> wrote in message
>> news:eG3Dd.51278$nP1.14028@twister.socal.rr.com...
>>> 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]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
>>> news:Oznwi828EHA.1260@TK2MSFTNGP12.phx.gbl...
>>> 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" <luederf001@hawaii.rr.com> wrote in message
>>> news:cnjCd.40163$nP1.36606@twister.socal.rr.com...
>>>>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]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
>>>> news:%23bnA2er7EHA.2196@TK2MSFTNGP14.phx.gbl...
>>>> 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" <luederf001@hawaii.rr.com> wrote in message
>>>> news:dPYAd.36681$nP1.12991@twister.socal.rr.com...
>>>>> 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]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
>>>>> news:OzYZt3d7EHA.3836@tk2msftngp13.phx.gbl...
>>>>> 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" <luederf001@hawaii.rr.com> wrote in message
>>>>> news:MeDAd.55122$gd.14867@twister.socal.rr.com...
>>>>>> 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]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
>>>>>> news:OD0oHlc7EHA.3124@TK2MSFTNGP11.phx.gbl...
>>>>>> 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" <luederf001@hawaii.rr.com> wrote in message
>>>>>> news:2JnAd.40781$Ew6.6376@twister.socal.rr.com...
>>>>>>>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 are:
>>>>>>> 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" <luederf001@hawaii.rr.com> wrote in message
>>>>>>> news:iNOwd.22848$gd.11377@twister.socal.rr.com...
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>>
>
>
>
- Next message: TheScullster: "Re: Run Time Error"
- Previous message: anonymous_at_discussions.microsoft.com: "Listbox and images"
- In reply to: Frank Lueder: "Re: Syntax needed to get needed reports"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
