John,
Yes, the SubGrpID in the table GrpMembers does already have the IDs of 14 and 15 there but that was accomplished through the original db system where the user would manually assign them a group via a combo box. The TempFamilyAddress table however was filled via the random queries. If examining the Household # in both tables you can see that many households have a group assignment different from the other.
I've not tried your query solution yet and will get to it sometime today, but from the description, it sounds like it will work.
--- 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 (5) |
Tidak ada komentar:
Posting Komentar