Update top n records

[JMorrell]

please excuse the cross posting (in Forms Coding) but I'm not finding the
results I need for my problem.

My form is based on a query of just 1 table and has 3 controls; a combo box
with 1 bound column, an unbound text box for a number, and an "update"
button.

Is it possible to update a table with the following pseudo code?

update TOP N

set [field] = "value" where [field] = "value"

From the form's controls, the user can select these values and then click
"update."

The single query should be a SELECT followed by an UPDATE, all in one
statement.

That's what I want to do but I'm at a loss as to the syntax.

tia,

 

[Michel Walsh]

Hi,


In Jet, yes. Make a query:

SELET TOP 2 * FROM table ORDER BY someField


and save it. Next, make another query, an UPDATE query, using that query you
just saved. TOP N select query, in Jet, are updateable.



Hoping it may help,
Vanderghast, Access MVP

 

[Barry Gilbert]

The Top predicate cannot be used directly in an Update query, so you'll have
to derive the Top values in a sub-query. Also, because the Top value will be
dynamic, I don't think you can use static SQL, meaning you'll have to build a
query in VBA and execute it there.

Air code:

Dim strSQL as String
strSQL = "Update MyTable Set [MyField] = '" & txtNewValue & "' WHERE MyField
In(Select Top " & Me.txtNum & " From MyTable OrderBy MyField)"
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

This could live in the OnClick of the command button.

Barry

 

[Barry Gilbert]

The Top predicate cannot be used directly in an Update query, so you'll have
to derive the Top values in a sub-query. Also, because the Top value will be
dynamic, I don't think you can use static SQL, meaning you'll have to build a
query in VBA and execute it there.

Air code:

Dim strSQL as String
strSQL = "Update MyTable Set [MyField] = '" & txtNewValue & "' WHERE MyField
In(Select Top " & Me.txtNum & " From MyTable OrderBy MyField)"
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

This could live in the OnClick of the command button.

Barry

 

[JMorrell]

thanks for the tip. I tried your suggestion and with the following, I get an
error:

UPDATE [tblInventory] SET [tblInventory].Hall = 99, [tblInventory].Available
= -1
WHERE ((([tblInventory].Hall)=1))
In(SELECT TOP 3 [tblInventory].Hall, [tblInventory].Prox,
[tblInventory].Adult, [tblInventory].Available
FROM [tblInventory]
WHERE ((([tblInventory].Hall)=1) AND (([tblInventory].Available)=0))
ORDER BY [tblInventory].Prox);

You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field. (Error 3306)

no matter where I put the EXISTS reserved word, I get a syntax error...
frustrating!
++++
I'm wondering if a FOR NEXT loop might be what I need.

tia,
--
JMorrell

The Top predicate cannot be used directly in an Update query, so you'll have
to derive the Top values in a sub-query. Also, because the Top value will be
dynamic, I don't think you can use static SQL, meaning you'll have to build a
query in VBA and execute it there.

Air code:

Dim strSQL as String
strSQL = "Update MyTable Set [MyField] = '" & txtNewValue & "' WHERE MyField
In(Select Top " & Me.txtNum & " From MyTable OrderBy MyField)"
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

This could live in the OnClick of the command button.

Barry

please excuse the cross posting (in Forms Coding) but I'm not finding the
results I need for my problem.

My form is based on a query of just 1 table and has 3 controls; a combo box
with 1 bound column, an unbound text box for a number, and an "update"
button.

Is it possible to update a table with the following pseudo code?

update TOP N

set [field] = "value" where [field] = "value"

From the form's controls, the user can select these values and then click
"update."

The single query should be a SELECT followed by an UPDATE, all in one
statement.

 

[JMorrell]

Sure, it makes sense to me. I have to ask how to save it though.

I had planned on the ON CLICK event routine to do the work. I don't think
I've ever had to "save" a data set that way.

--
JMorrell

Hi,


In Jet, yes. Make a query:

SELET TOP 2 * FROM table ORDER BY someField


and save it. Next, make another query, an UPDATE query, using that query you
just saved. TOP N select query, in Jet, are updateable.



Hoping it may help,
Vanderghast, Access MVP

please excuse the cross posting (in Forms Coding) but I'm not finding the
results I need for my problem.

My form is based on a query of just 1 table and has 3 controls; a combo
box
with 1 bound column, an unbound text box for a number, and an "update"
button.

Is it possible to update a table with the following pseudo code?

update TOP N

set [field] = "value" where [field] = "value"

From the form's controls, the user can select these values and then click
"update."

The single query should be a SELECT followed by an UPDATE, all in one
statement.

That's what I want to do but I'm at a loss as to the syntax.

tia,

 

[Michel Walsh]

Hi,


To save the first query? technically, just ... make it and then save it,
from the query designer. You can then refer to it as if it was a table.

Hoping it may help,
Vanderghast, Access MVP

Sure, it makes sense to me. I have to ask how to save it though.

I had planned on the ON CLICK event routine to do the work. I don't think
I've ever had to "save" a data set that way.

--
JMorrell

Hi,


In Jet, yes. Make a query:

SELET TOP 2 * FROM table ORDER BY someField


and save it. Next, make another query, an UPDATE query, using that query
you
just saved. TOP N select query, in Jet, are updateable.



Hoping it may help,
Vanderghast, Access MVP

please excuse the cross posting (in Forms Coding) but I'm not finding
the
results I need for my problem.

My form is based on a query of just 1 table and has 3 controls; a combo
box
with 1 bound column, an unbound text box for a number, and an "update"
button.

Is it possible to update a table with the following pseudo code?

update TOP N

set [field] = "value" where [field] = "value"

From the form's controls, the user can select these values and then
click
"update."

The single query should be a SELECT followed by an UPDATE, all in one
statement.

That's what I want to do but I'm at a loss as to the syntax.

tia,

 

[JMorrell]

Well yea.... sorry, just had a senior moment.

The query I need will be run from an OnClick event. I thought you meant to
save the resulting data set from the SELECT TOP n values statement. If I
could do that, it would work. I'm not sure Access has that capability.

thanks for your help.
--
JMorrell

Hi,


To save the first query? technically, just ... make it and then save it,
from the query designer. You can then refer to it as if it was a table.

Hoping it may help,
Vanderghast, Access MVP

Sure, it makes sense to me. I have to ask how to save it though.

I had planned on the ON CLICK event routine to do the work. I don't think
I've ever had to "save" a data set that way.

--
JMorrell

Hi,


In Jet, yes. Make a query:

SELET TOP 2 * FROM table ORDER BY someField


and save it. Next, make another query, an UPDATE query, using that query
you
just saved. TOP N select query, in Jet, are updateable.



Hoping it may help,
Vanderghast, Access MVP

please excuse the cross posting (in Forms Coding) but I'm not finding
the
results I need for my problem.

My form is based on a query of just 1 table and has 3 controls; a combo
box
with 1 bound column, an unbound text box for a number, and an "update"
button.

Is it possible to update a table with the following pseudo code?

update TOP N

set [field] = "value" where [field] = "value"

From the form's controls, the user can select these values and then
click
"update."

The single query should be a SELECT followed by an UPDATE, all in one
statement.

That's what I want to do but I'm at a loss as to the syntax.

tia,

 

[Michel Walsh]

Hi,


A (saved or not) query can call a saved query, or a table, in the FROM
clause, but does not allow to use a "recordset". So, if you simply save the
first query


SELET TOP 2 * FROM table ORDER BY someField

in the database, a little bit as you save "VBA code" , in design mode, then,
in the onClick event, you can use a VBA line of code, something like:


CurrentDb.Execute "UPDATE savedQueryName SET fieldName = " & newValue & "
WHERE someconditionIfNecessary"


You don't need to use any recordset, proceeding this way.



Hoping it may help,
Vanderghast, Access MVP

Well yea.... sorry, just had a senior moment.

The query I need will be run from an OnClick event. I thought you meant
to
save the resulting data set from the SELECT TOP n values statement. If I
could do that, it would work. I'm not sure Access has that capability.

thanks for your help.
--
JMorrell

Hi,


To save the first query? technically, just ... make it and then save it,
from the query designer. You can then refer to it as if it was a table.

Hoping it may help,
Vanderghast, Access MVP

Sure, it makes sense to me. I have to ask how to save it though.

I had planned on the ON CLICK event routine to do the work. I don't
think
I've ever had to "save" a data set that way.

--
JMorrell


:

Hi,


In Jet, yes. Make a query:

SELET TOP 2 * FROM table ORDER BY someField


and save it. Next, make another query, an UPDATE query, using that
query
you
just saved. TOP N select query, in Jet, are updateable.



Hoping it may help,
Vanderghast, Access MVP

please excuse the cross posting (in Forms Coding) but I'm not
finding
the
results I need for my problem.

My form is based on a query of just 1 table and has 3 controls; a
combo
box
with 1 bound column, an unbound text box for a number, and an
"update"
button.

Is it possible to update a table with the following pseudo code?

update TOP N

set [field] = "value" where [field] = "value"

From the form's controls, the user can select these values and then
click
"update."

The single query should be a SELECT followed by an UPDATE, all in
one
statement.

That's what I want to do but I'm at a loss as to the syntax.

tia,

 

[JMorrell]

Thank you for the reply and my apologies for not attending to this group as
often as I should.

The top n value can be anywhere from 1 to 1,000. I'm not clear how to save
such a query in design mode where my n value is a variable. I think I can
muddle my way through the rest.

This is a side project I'm working on so it's a good thing there's no rush.

tia,
--
JMorrell

Hi,


A (saved or not) query can call a saved query, or a table, in the FROM
clause, but does not allow to use a "recordset". So, if you simply save the
first query


SELET TOP 2 * FROM table ORDER BY someField

in the database, a little bit as you save "VBA code" , in design mode, then,
in the onClick event, you can use a VBA line of code, something like:


CurrentDb.Execute "UPDATE savedQueryName SET fieldName = " & newValue & "
WHERE someconditionIfNecessary"


You don't need to use any recordset, proceeding this way.



Hoping it may help,
Vanderghast, Access MVP

Well yea.... sorry, just had a senior moment.

The query I need will be run from an OnClick event. I thought you meant
to
save the resulting data set from the SELECT TOP n values statement. If I
could do that, it would work. I'm not sure Access has that capability.

thanks for your help.
--
JMorrell

Hi,


To save the first query? technically, just ... make it and then save it,
from the query designer. You can then refer to it as if it was a table.

Hoping it may help,
Vanderghast, Access MVP

Sure, it makes sense to me. I have to ask how to save it though.

I had planned on the ON CLICK event routine to do the work. I don't
think
I've ever had to "save" a data set that way.

--
JMorrell


:

Hi,


In Jet, yes. Make a query:

SELET TOP 2 * FROM table ORDER BY someField


and save it. Next, make another query, an UPDATE query, using that
query
you
just saved. TOP N select query, in Jet, are updateable.



Hoping it may help,
Vanderghast, Access MVP

please excuse the cross posting (in Forms Coding) but I'm not
finding
the
results I need for my problem.

My form is based on a query of just 1 table and has 3 controls; a
combo
box
with 1 bound column, an unbound text box for a number, and an
"update"
button.

Is it possible to update a table with the following pseudo code?

update TOP N

set [field] = "value" where [field] = "value"

From the form's controls, the user can select these values and then
click
"update."

The single query should be a SELECT followed by an UPDATE, all in
one
statement.

That's what I want to do but I'm at a loss as to the syntax.

tia,

 

[Michel Walsh]

Hi,


In this case, you may have to change the SQL statement of the saved query,
through its SQL property (through VBA), or type the text of a query like:



Dim strSQL As string
strSQL= "UPDATE tableName SET fieldname=FORMS!formName!controlName WHERE
primarykey IN( SELECT TOP " & n & " primaryKey FROM tableName ORDER BY
someField )"

DoCmd.RunSQL strSQL



where primarykey is the field name that is the primary key of your table,
somefield is the field name that defines the "ordering",
fieldname is the field name to be update by the value actually in
the control controlName of the open form formName.

In the SQL statement I made, all caps word are keywords to be typed exactly
as they are. Other names are to be replaced to suit your case.



Hoping it may help,
Vanderghast, Access MVP

Thank you for the reply and my apologies for not attending to this group
as
often as I should.

The top n value can be anywhere from 1 to 1,000. I'm not clear how to
save
such a query in design mode where my n value is a variable. I think I can
muddle my way through the rest.

This is a side project I'm working on so it's a good thing there's no
rush.

tia,
--
JMorrell

Hi,


A (saved or not) query can call a saved query, or a table, in the FROM
clause, but does not allow to use a "recordset". So, if you simply save
the
first query


SELET TOP 2 * FROM table ORDER BY someField

in the database, a little bit as you save "VBA code" , in design mode,
then,
in the onClick event, you can use a VBA line of code, something like:


CurrentDb.Execute "UPDATE savedQueryName SET fieldName = " & newValue &
"
WHERE someconditionIfNecessary"


You don't need to use any recordset, proceeding this way.



Hoping it may help,
Vanderghast, Access MVP

Well yea.... sorry, just had a senior moment.

The query I need will be run from an OnClick event. I thought you
meant
to
save the resulting data set from the SELECT TOP n values statement. If
I
could do that, it would work. I'm not sure Access has that capability.

thanks for your help.
--
JMorrell


:

Hi,


To save the first query? technically, just ... make it and then save
it,
from the query designer. You can then refer to it as if it was a
table.

Hoping it may help,
Vanderghast, Access MVP

Sure, it makes sense to me. I have to ask how to save it though.

I had planned on the ON CLICK event routine to do the work. I don't
think
I've ever had to "save" a data set that way.

--
JMorrell


:

Hi,


In Jet, yes. Make a query:

SELET TOP 2 * FROM table ORDER BY someField


and save it. Next, make another query, an UPDATE query, using that
query
you
just saved. TOP N select query, in Jet, are updateable.



Hoping it may help,
Vanderghast, Access MVP

please excuse the cross posting (in Forms Coding) but I'm not
finding
the
results I need for my problem.

My form is based on a query of just 1 table and has 3 controls; a
combo
box
with 1 bound column, an unbound text box for a number, and an
"update"
button.

Is it possible to update a table with the following pseudo code?

update TOP N

set [field] = "value" where [field] =
"value"

From the form's controls, the user can select these values and
then
click
"update."

The single query should be a SELECT followed by an UPDATE, all in
one
statement.

That's what I want to do but I'm at a loss as to the syntax.

tia,

 

[JMorrell]

Thank you for your help in this.

After a couple of fat-fingered typos, it works just like I imagined it to.

You're a genius!

--
JMorrell

Hi,


In this case, you may have to change the SQL statement of the saved query,
through its SQL property (through VBA), or type the text of a query like:



Dim strSQL As string
strSQL= "UPDATE tableName SET fieldname=FORMS!formName!controlName WHERE
primarykey IN( SELECT TOP " & n & " primaryKey FROM tableName ORDER BY
someField )"

DoCmd.RunSQL strSQL



where primarykey is the field name that is the primary key of your table,
somefield is the field name that defines the "ordering",
fieldname is the field name to be update by the value actually in
the control controlName of the open form formName.

In the SQL statement I made, all caps word are keywords to be typed exactly
as they are. Other names are to be replaced to suit your case.



Hoping it may help,
Vanderghast, Access MVP

Thank you for the reply and my apologies for not attending to this group
as
often as I should.

The top n value can be anywhere from 1 to 1,000. I'm not clear how to
save
such a query in design mode where my n value is a variable. I think I can
muddle my way through the rest.

This is a side project I'm working on so it's a good thing there's no
rush.

tia,
--
JMorrell

Hi,


A (saved or not) query can call a saved query, or a table, in the FROM
clause, but does not allow to use a "recordset". So, if you simply save
the
first query


SELET TOP 2 * FROM table ORDER BY someField

in the database, a little bit as you save "VBA code" , in design mode,
then,
in the onClick event, you can use a VBA line of code, something like:


CurrentDb.Execute "UPDATE savedQueryName SET fieldName = " & newValue &
"
WHERE someconditionIfNecessary"


You don't need to use any recordset, proceeding this way.



Hoping it may help,
Vanderghast, Access MVP





Well yea.... sorry, just had a senior moment.

The query I need will be run from an OnClick event. I thought you
meant
to
save the resulting data set from the SELECT TOP n values statement. If
I
could do that, it would work. I'm not sure Access has that capability.

thanks for your help.
--
JMorrell


:

Hi,


To save the first query? technically, just ... make it and then save
it,
from the query designer. You can then refer to it as if it was a
table.

Hoping it may help,
Vanderghast, Access MVP

Sure, it makes sense to me. I have to ask how to save it though.

I had planned on the ON CLICK event routine to do the work. I don't
think
I've ever had to "save" a data set that way.

--
JMorrell


:

Hi,


In Jet, yes. Make a query:

SELET TOP 2 * FROM table ORDER BY someField


and save it. Next, make another query, an UPDATE query, using that
query
you
just saved. TOP N select query, in Jet, are updateable.



Hoping it may help,
Vanderghast, Access MVP

please excuse the cross posting (in Forms Coding) but I'm not
finding
the
results I need for my problem.

My form is based on a query of just 1 table and has 3 controls; a
combo
box
with 1 bound column, an unbound text box for a number, and an
"update"
button.

Is it possible to update a table with the following pseudo code?

update TOP N

set [field] = "value" where [field] =
"value"

From the form's controls, the user can select these values and
then
click
"update."

The single query should be a SELECT followed by an UPDATE, all in
one
statement.

That's what I want to do but I'm at a loss as to the syntax.

tia,