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 (4) |
Tidak ada komentar:
Posting Komentar