ACCESS FEED: Juni 2013

Minggu, 30 Juni 2013

RE: [MS_AccessPros] Re: update query help

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)

Recent Activity:

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

__,_._,___

[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
>

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (6)

Recent Activity:

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

__,_._,___

[MS_AccessPros] Re: update query help

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
>

Kami ingin Anda kembali

Jaga akun Anda tetap aktif dengan sign in sebelum 15 Juli 2013.

Dengan mengaktifkan kembali akun Yahoo! Anda, Anda bisa menikmati Yahoo! Mail baru, lebih banyak konten personal di Yahoo.com, dan lebih banyak lagi.

Begitu akun Anda diaktifkan kembali, setiap kali Anda sign in, akun Anda akan diperpanjang selama 12 bulan.

Perlu mengeset ulang kata sandi Anda?
Bantuan telah tiba!

Ada pertanyaan lain?
Kunjungi Layanan Pelanggan

Yahoo! Customer Experience

Kebijakan Privasi | Web Beacon di Email

[MS_AccessPros] Re: Error 3061 Too few parameters

John,

Thank you! I'm not in the office today but I can't wait to try it on Monday. I'll let you know how it comes out.

Doyce

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Doyce-
>
> You cannot put a parameter in SQL that you plan to Execute. You can,
> however, do direct substitution, like this:
>
> strSQL = "UPDATE VinMaster SET VinMaster.CustPONo = " &
> [Forms]![frmAddInvoiceDetail]![txtCustPO] & ", " & _
> "VinMaster.Mat_Labor = '" &
> [Forms]![frmAddInvoiceDetail]![txtMatAndLabor] & "', " & _
> "VinMaster.FET = '" & [Forms]![frmAddInvoiceDetail]![txtFET] & "', "
> & _
> "VinMaster.Freight = '" & [Forms]![frmAddInvoiceDetail]![txtDelChg]
> & "', " & _
> "VinMaster.BOM = '" & [Forms]![frmAddInvoiceDetail]![cboBOM] & "' "
> & _
> "WHERE (((VinMaster.[Cust ID])= " &
> [Forms]![frmAddInvoiceDetail]![cboCustomer] & ") " & _
> "AND ((VinMaster.[Unit #]) Between " &
> [Forms]![frmAddInvoiceDetail]![cboStartNo] & " " & _
> "And " & [Forms]![frmAddInvoiceDetail]![cboEndNo] & "));"
>
> What I've done is supply the values directly in the SQL by resolving the
> control references in code BEFORE I pass it to the SQL parser. I've made
> some assumptions as to which fields might be number and which might be text.
> You may have to alter the insertion of ' characters accordingly.
>
> 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 dnwinberry
> Sent: Thursday, June 27, 2013 9:10 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Error 3061 Too few parameters
>
> I need some help with this error. I'm trying to update some records in a
> table using a button on a form and using controls on the form for parameters
> and data to update the records with. This form is unbound. Here is my code:
>
> Dim db As Database, strSQL As String
> Set db = CurrentDb
> strSQL = "UPDATE VinMaster SET VinMaster.CustPONo =
> [Forms]![frmAddInvoiceDetail]![txtCustPO], " & _
> "VinMaster.Mat_Labor =
> [Forms]![frmAddInvoiceDetail]![txtMatAndLabor], " & _
> "VinMaster.FET = [Forms]![frmAddInvoiceDetail]![txtFET], " & _
> "VinMaster.Freight = [Forms]![frmAddInvoiceDetail]![txtDelChg], " &
> _
> "VinMaster.BOM = [Forms]![frmAddInvoiceDetail]![cboBOM] " & _
> "WHERE (((VinMaster.[Cust
> ID])=[Forms]![frmAddInvoiceDetail]![cboCustomer]) " & _
> "AND ((VinMaster.[Unit #]) Between
> [Forms]![frmAddInvoiceDetail]![cboStartNo] " & _
> "And [Forms]![frmAddInvoiceDetail]![cboEndNo]));"
> db.Execute strSQL, dbFailOnError
> 'Debug.Print strSQL
> Set db = Nothing
>
> I get Error 3061 Too few parameters expected 8.
>
> Thanks for any help.
> Doyce
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (4)

Recent Activity:

New Members 4
New Photos 1

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

__,_._,___