Russ-
If next year your values are, for example, 15 and 16, you would need to
change those two values in your query. It would be better if you could
change the queries that generate the two groups to create the numbers you
need instead of 1 and 2. Then you could do the update via a JOIN instead of
using DLookup.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ghsclass65
Sent: Sunday, June 30, 2013 10:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: update query help
John,
That works just fine. I am not sure of your comment about having to
manually change the values in the query each time a random pick is done.
Doesn't GroupName have the actual two values?
One problem I see though is using the constant of 14 or 15. If the grouping
is changed someday those Autonumber values may change and the query will not
work. I think it might be better if it would instead use a variable to
choose the two numbers something like "the value of SubGrpID".
Although we currently have only two groups to divide there may be a day when
they want to divide the households into 3 groups then I would have to modify
the query.
Russ
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...>
wrote:
>
> Russ-
>
> But it looks like SubGrpID already contains 14 or 15. If you want to
> change it based on 1 or 2 in the matching TempFamilyAddress record, you
could try:
>
> UPDATE GrpMembers
> SET GrpMembers.SubGrpID =
> IIf(DLookUp("GroupName","TempFamilyAddress","[Household #] = " &
> [Household #])=1,14,15);
>
> Basically, this query is looking at each row in GrpMembers and looking
> up the GroupName value in TempFamilyAddress on matching Household #.
> If it finds a 1, then it updates SubGrpID to 14, otherwise 15. This
> is a bit awkward because you will have to manually change the values
> in this query each time you do a random pick. It would be better if
> GroupName had the actual two values, then you could do a direct update via
a JOIN.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications SQL Queries for Mere Mortals
> http://www.viescas.com/ (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> ghsclass65
> Sent: Sunday, June 30, 2013 1:39 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: update query help
>
> John,
>
> I'm sorry you got befuddled by this. This is from the same db that
> you helped me with a few weeks ago when I wanted a random pick for two
groups.
> I think that works just fine, but I thought that it would be
> appropriate to allow the user the flexibility to change or easily
> modify the random picks for the two groups, so I am attempting to
> merge that random chosen data into the original "draw a name out of a
> hat" table so the random picks can be easily fine tuned if necessary
> using the original manual method. Then the two groups would be
> randomly chosen, then the user can slightly modify the random picks.
>
> The field GroupName was originally a text field but I changed it to a
> number field so it would allow a simple transfer of data to the other
> table's number field.
>
> The field GroupName should relate to SubGrp Name in the OrgSub table
> and SubGrpID in the GrpMembers table. In other words a 1 for a SubGrp
> Name would be the equivalent of a 14 and a 2 would be the equivalent of a
15.
>
> I don't want to insert rows into the GroupMembers table but update the
> SubGrpID field so it reflects the random pick in the field GroupName
> in TempFamilyAddress. So when completed, a 1 field value in GroupName
> in the TempFamilyAddress table would be interpreted as a 14 and
> updated into the GrpMembers table SubGrpID field, and a 2 would be
> interpreted as a 15 and updated into the SubGrpID field in the GrpMembers
table.
>
> Ultimately what I would like to happen is modifying the SubGrpID
> field in the GrpMembers table (presently 14 or 15) so it will
> correspond with the field GroupName which is 1 or 2 but 14 or 15 in the
OrgSub table.
>
> I hope this does not seem too confusing.
>
> Russ
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> wrote:
> >
> > I have looked at your sample database, and I'm still befuddled.
> > GroupName in TempFamilyAddress appears to contain a number, not a
> > name. Is this related to GrpID or SubGrpID? Do you want to insert
> > rows
> into GrpMembers?
> >
> > Please clarify.
> >
> > John Viescas, Author
> > Microsoft Access 2010 Inside Out
> > Microsoft Access 2007 Inside Out
> > Microsoft Access 2003 Inside Out
> > Building Microsoft Access Applications SQL Queries for Mere Mortals
> > http://www.viescas.com/ (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > ghsclass65
> > Sent: Saturday, June 29, 2013 6:58 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] update query help
> >
> > I've got three tables that are related... OrgSub, TempFamilyAddress,
> > and GrpMembers.
> >
> > I want to update the field SubGrpID in the GrpMembers table with the
> > value in table OrgSub field SubGrpID.
> >
> >
> > The field SubGrpName corresponds to the GroupName field in the table
> > TempFamilyAddress and the SubGrpID Name value should be filled
> > according to that value of GroupName in TempFamilyAddress.
> >
> > They should be linked by Household #. See the sample db I've
> > uploaded called SampleUpdateSubGrpID.
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar