UPDATE query creation for SQL

G

Guest

first off, i'm using A2K....

i want to create an update query that will let my users modify records in a
table because when they have to scroll through their usual data entry form it
takes too long to get to the record they need modify and i think a query
would run a little quicker -- that's my assumption.

if you grant my assumption, then what i see as the way to implement it
(since they don't know how to use the Access QBE interface, is by developing
a form which prompts them for the record selection criterion's/criteria's
values along with the updated values to be entered any of the remaining 8 or
9 fields in the underlying table.

let's say that the controls they could potentially use to select a record
are ("Study#" OR "IRB_Number") AND "MedRecNum". the PK in the table consists
of IRBNumber and MedRecNum (although Study# is mapped uniquely to IRBNumber).
there are some users who know that a MedRecNum is mapped to a Study# and
others who are more comfortable thinking of a MedRecNum mapped to its
IRB_Number so i want to try to keep this form general enough so that if they
input the value of one or the other plus the MedRecNum it will identify the
underlying record requiring updating.

assuming that this can be accomplished using SQL, i would want the user to
have the ability to update any or all of the remaining fields in the table
underlying the query.

i know that my WHERE clause in a query can filter for values in a form, e.g.

WHERE ((([Patients on Follow-Up].[Study#] = [Forms]![Query
Request]![Study#])));

and that the SET statement can accept the new values in the underlying
tables's

SET [Patients on Follow-Up][Pt Initials] = "ZZZ"

but can the where clause accept an 'OR' operator and can the SET statement
accept a statement like

SET [Forms]![Query Request]![Pt Init] = "ZZZ"

any SQL gurus out there care to opine?

-ted
 
R

Roger Carlson

I'm not certain I follow the reasoning for an Update query. But if you want
to find a specific record, on my website (www.rogersaccesslibrary.com), is a
small Access database sample called "ComboChoosesRecord.mdb" which
illustrates how to do this. Also take a look at
"ImproveFormPerformance.mdb" for a slightly different approach.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
G

Guest

roger,

i guess the approach your mdb tooks would work.

thank you.

-ted

ps: i tried to use the paypal feature but there was a problem when i pressed
the 'continue' button. i repeated this attempt several times in succession
w/o success. i will try yet again today. i'm hoping it's just a transitory
kind of thing.

Roger Carlson said:
I'm not certain I follow the reasoning for an Update query. But if you want
to find a specific record, on my website (www.rogersaccesslibrary.com), is a
small Access database sample called "ComboChoosesRecord.mdb" which
illustrates how to do this. Also take a look at
"ImproveFormPerformance.mdb" for a slightly different approach.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Ted said:
first off, i'm using A2K....

i want to create an update query that will let my users modify records in a
table because when they have to scroll through their usual data entry form it
takes too long to get to the record they need modify and i think a query
would run a little quicker -- that's my assumption.

if you grant my assumption, then what i see as the way to implement it
(since they don't know how to use the Access QBE interface, is by developing
a form which prompts them for the record selection criterion's/criteria's
values along with the updated values to be entered any of the remaining 8 or
9 fields in the underlying table.

let's say that the controls they could potentially use to select a record
are ("Study#" OR "IRB_Number") AND "MedRecNum". the PK in the table consists
of IRBNumber and MedRecNum (although Study# is mapped uniquely to IRBNumber).
there are some users who know that a MedRecNum is mapped to a Study# and
others who are more comfortable thinking of a MedRecNum mapped to its
IRB_Number so i want to try to keep this form general enough so that if they
input the value of one or the other plus the MedRecNum it will identify the
underlying record requiring updating.

assuming that this can be accomplished using SQL, i would want the user to
have the ability to update any or all of the remaining fields in the table
underlying the query.

i know that my WHERE clause in a query can filter for values in a form, e.g.

WHERE ((([Patients on Follow-Up].[Study#] = [Forms]![Query
Request]![Study#])));

and that the SET statement can accept the new values in the underlying
tables's

SET [Patients on Follow-Up][Pt Initials] = "ZZZ"

but can the where clause accept an 'OR' operator and can the SET statement
accept a statement like

SET [Forms]![Query Request]![Pt Init] = "ZZZ"

any SQL gurus out there care to opine?

-ted
 
G

Guest

hi roger,

i've had a chance to look through your said tutorial-database and i figured
it'd be worthwhile to poll you on this one....the example form 'Books 3' lets
the user pick either the ISBN or the book's Title, and finds the book using
either one whilest populating the non-chosen field with the appropriate
entry. the problem i'm working on uses "Study#" and "IRB#" to identify
patients and also requires the patient's "MedRecNum" which is unique within a
Study# (IRB#). if we could go beyond the -OR- of your example to include the
-AND- of the MedRecNum control that would uniquely identify the patient the
user's looking form while giving users who knew either Study# or IRB# (but
not typically both) the ability to get to the record.

-ted


Roger Carlson said:
I'm not certain I follow the reasoning for an Update query. But if you want
to find a specific record, on my website (www.rogersaccesslibrary.com), is a
small Access database sample called "ComboChoosesRecord.mdb" which
illustrates how to do this. Also take a look at
"ImproveFormPerformance.mdb" for a slightly different approach.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Ted said:
first off, i'm using A2K....

i want to create an update query that will let my users modify records in a
table because when they have to scroll through their usual data entry form it
takes too long to get to the record they need modify and i think a query
would run a little quicker -- that's my assumption.

if you grant my assumption, then what i see as the way to implement it
(since they don't know how to use the Access QBE interface, is by developing
a form which prompts them for the record selection criterion's/criteria's
values along with the updated values to be entered any of the remaining 8 or
9 fields in the underlying table.

let's say that the controls they could potentially use to select a record
are ("Study#" OR "IRB_Number") AND "MedRecNum". the PK in the table consists
of IRBNumber and MedRecNum (although Study# is mapped uniquely to IRBNumber).
there are some users who know that a MedRecNum is mapped to a Study# and
others who are more comfortable thinking of a MedRecNum mapped to its
IRB_Number so i want to try to keep this form general enough so that if they
input the value of one or the other plus the MedRecNum it will identify the
underlying record requiring updating.

assuming that this can be accomplished using SQL, i would want the user to
have the ability to update any or all of the remaining fields in the table
underlying the query.

i know that my WHERE clause in a query can filter for values in a form, e.g.

WHERE ((([Patients on Follow-Up].[Study#] = [Forms]![Query
Request]![Study#])));

and that the SET statement can accept the new values in the underlying
tables's

SET [Patients on Follow-Up][Pt Initials] = "ZZZ"

but can the where clause accept an 'OR' operator and can the SET statement
accept a statement like

SET [Forms]![Query Request]![Pt Init] = "ZZZ"

any SQL gurus out there care to opine?

-ted
 
R

Roger Carlson

Well, I guess, I'd create a combo box with the MRN on the left. Then I
would have the Study and IRB combo stacked on the right. The user would
select the MRN, then one or the other of the ones on the right. The
AfterUpdate code would then be modified to include the MRN:

Me.RecordsetClone.FindFirst "[ISBN] = '" & Me![cboISBN] & "' and [MRN] =
'" & Me![cboMRN] & "'"

In an additional florish, you can use the concepts behing the
CascadingComboBox example to limit the choices in the Study and IRB combos
to just those that have the chosen MRN.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Ted said:
hi roger,

i've had a chance to look through your said tutorial-database and i figured
it'd be worthwhile to poll you on this one....the example form 'Books 3' lets
the user pick either the ISBN or the book's Title, and finds the book using
either one whilest populating the non-chosen field with the appropriate
entry. the problem i'm working on uses "Study#" and "IRB#" to identify
patients and also requires the patient's "MedRecNum" which is unique within a
Study# (IRB#). if we could go beyond the -OR- of your example to include the
-AND- of the MedRecNum control that would uniquely identify the patient the
user's looking form while giving users who knew either Study# or IRB# (but
not typically both) the ability to get to the record.

-ted


Roger Carlson said:
I'm not certain I follow the reasoning for an Update query. But if you want
to find a specific record, on my website (www.rogersaccesslibrary.com), is a
small Access database sample called "ComboChoosesRecord.mdb" which
illustrates how to do this. Also take a look at
"ImproveFormPerformance.mdb" for a slightly different approach.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Ted said:
first off, i'm using A2K....

i want to create an update query that will let my users modify records
in
a
table because when they have to scroll through their usual data entry
form
it
takes too long to get to the record they need modify and i think a query
would run a little quicker -- that's my assumption.

if you grant my assumption, then what i see as the way to implement it
(since they don't know how to use the Access QBE interface, is by developing
a form which prompts them for the record selection criterion's/criteria's
values along with the updated values to be entered any of the
remaining 8
or
9 fields in the underlying table.

let's say that the controls they could potentially use to select a record
are ("Study#" OR "IRB_Number") AND "MedRecNum". the PK in the table consists
of IRBNumber and MedRecNum (although Study# is mapped uniquely to IRBNumber).
there are some users who know that a MedRecNum is mapped to a Study# and
others who are more comfortable thinking of a MedRecNum mapped to its
IRB_Number so i want to try to keep this form general enough so that
if
they
input the value of one or the other plus the MedRecNum it will
identify
the
underlying record requiring updating.

assuming that this can be accomplished using SQL, i would want the user to
have the ability to update any or all of the remaining fields in the table
underlying the query.

i know that my WHERE clause in a query can filter for values in a
form,
e.g.
WHERE ((([Patients on Follow-Up].[Study#] = [Forms]![Query
Request]![Study#])));

and that the SET statement can accept the new values in the underlying
tables's

SET [Patients on Follow-Up][Pt Initials] = "ZZZ"

but can the where clause accept an 'OR' operator and can the SET statement
accept a statement like

SET [Forms]![Query Request]![Pt Init] = "ZZZ"

any SQL gurus out there care to opine?

-ted
 
G

Guest

i'm doing something like that. i got the idea after thinking about the
cascading combos (cascading combos -- boy, where did they go on vacation to
coin that one).

thanks and best regards,

-ted


Roger Carlson said:
Well, I guess, I'd create a combo box with the MRN on the left. Then I
would have the Study and IRB combo stacked on the right. The user would
select the MRN, then one or the other of the ones on the right. The
AfterUpdate code would then be modified to include the MRN:

Me.RecordsetClone.FindFirst "[ISBN] = '" & Me![cboISBN] & "' and [MRN] =
'" & Me![cboMRN] & "'"

In an additional florish, you can use the concepts behing the
CascadingComboBox example to limit the choices in the Study and IRB combos
to just those that have the chosen MRN.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Ted said:
hi roger,

i've had a chance to look through your said tutorial-database and i figured
it'd be worthwhile to poll you on this one....the example form 'Books 3' lets
the user pick either the ISBN or the book's Title, and finds the book using
either one whilest populating the non-chosen field with the appropriate
entry. the problem i'm working on uses "Study#" and "IRB#" to identify
patients and also requires the patient's "MedRecNum" which is unique within a
Study# (IRB#). if we could go beyond the -OR- of your example to include the
-AND- of the MedRecNum control that would uniquely identify the patient the
user's looking form while giving users who knew either Study# or IRB# (but
not typically both) the ability to get to the record.

-ted


Roger Carlson said:
I'm not certain I follow the reasoning for an Update query. But if you want
to find a specific record, on my website (www.rogersaccesslibrary.com), is a
small Access database sample called "ComboChoosesRecord.mdb" which
illustrates how to do this. Also take a look at
"ImproveFormPerformance.mdb" for a slightly different approach.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


first off, i'm using A2K....

i want to create an update query that will let my users modify records in
a
table because when they have to scroll through their usual data entry form
it
takes too long to get to the record they need modify and i think a query
would run a little quicker -- that's my assumption.

if you grant my assumption, then what i see as the way to implement it
(since they don't know how to use the Access QBE interface, is by
developing
a form which prompts them for the record selection criterion's/criteria's
values along with the updated values to be entered any of the remaining 8
or
9 fields in the underlying table.

let's say that the controls they could potentially use to select a record
are ("Study#" OR "IRB_Number") AND "MedRecNum". the PK in the table
consists
of IRBNumber and MedRecNum (although Study# is mapped uniquely to
IRBNumber).
there are some users who know that a MedRecNum is mapped to a Study# and
others who are more comfortable thinking of a MedRecNum mapped to its
IRB_Number so i want to try to keep this form general enough so that if
they
input the value of one or the other plus the MedRecNum it will identify
the
underlying record requiring updating.

assuming that this can be accomplished using SQL, i would want the user to
have the ability to update any or all of the remaining fields in the table
underlying the query.

i know that my WHERE clause in a query can filter for values in a form,
e.g.

WHERE ((([Patients on Follow-Up].[Study#] = [Forms]![Query
Request]![Study#])));

and that the SET statement can accept the new values in the underlying
tables's

SET [Patients on Follow-Up][Pt Initials] = "ZZZ"

but can the where clause accept an 'OR' operator and can the SET statement
accept a statement like

SET [Forms]![Query Request]![Pt Init] = "ZZZ"

any SQL gurus out there care to opine?

-ted
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top