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
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar