Update top n records

[Guest]

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

set [field] = "value" where [field] = "value" for first n
records?

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

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

tia,

 

[Jeff Boyce]

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an order only it
appears to understand. What field are you using in your underlying table to
provide a sort order, so that you can select the "top n records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff-

The top n records are numerical and are sorted ascending in the query. It
would be difficult to list all records along with a check box for the user to
select which records are to be updated.

I've even thought of making a temp table to hold the top n records, deleting
them out of the original table, manipulating the fields, then re-adding them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an order only it
appears to understand. What field are you using in your underlying table to
provide a sort order, so that you can select the "top n records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

set [field] = "value" where [field] = "value" for first n
records?

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

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

tia,

 

[Jeff Boyce]

You've described HOW you are trying to do somethign, but I still don't have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that need to be
updated, give a value to be updated to, then select another set of records
to be updated to a different value, then ... repeated until done?

Can you provide a bit more description? A real world example with real or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff-

The top n records are numerical and are sorted ascending in the query. It
would be difficult to list all records along with a check box for the user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n records,
deleting
them out of the original table, manipulating the fields, then re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an order only it
appears to understand. What field are you using in your underlying table
to
provide a sort order, so that you can select the "top n records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

set [field] = "value" where [field] = "value" for first
n
records?

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

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

tia,

 

[Guest]

Thanks for letting me clarify this; it helps me sort it out in my own head.

It’s a very simple db actually, just 2 tables; Location
(RecordID(AutoNumber), LocationNumber(Integer), LocationName(Text)) and
Inventory (RecordID(AutoNumber), WidgetID(Integer), Status(yes/no),
LocationNumber(Integer)). I have a need to reassign widgets from one
location to another location, as well as change the status from “availableâ€
to “unavailable.†I might add that all the “available†widgets are in one
location (“ #1â€). Table Inventory has every widget in inventory with only
the widget status letting me know if it is available to transfer to another
location.

The form allows the user to select the new location (from table Location)
via a list box, and then enter a number in a text box. So, now I have the
new location and the quantity (as seen in breakpoint mode).

I want to update table Inventory n number widgets with new location (e.g.,
from LocationNumber 1 to LocationNumber5) and status to Unavailable. It must
also be noted that it is important to keep the widgets in numerical order.

At times, I will be taking widgets out of inventory by changing their
status, but not changing their location.

Can this be done? Thanks in advance.

--
JMorrell

You've described HOW you are trying to do somethign, but I still don't have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that need to be
updated, give a value to be updated to, then select another set of records
to be updated to a different value, then ... repeated until done?

Can you provide a bit more description? A real world example with real or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff-

The top n records are numerical and are sorted ascending in the query. It
would be difficult to list all records along with a check box for the user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n records,
deleting
them out of the original table, manipulating the fields, then re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an order only it
appears to understand. What field are you using in your underlying table
to
provide a sort order, so that you can select the "top n records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

set [field] = "value" where [field] = "value" for first
n
records?

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

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

tia,

 

[Jeff Boyce]

I am not complete clear on what's happening, but here goes...

It sounds like you want a way to have the user say "show all Location1
widgets now moved to Location5, and their status marked 'unavailable'". Is
that a fair paraphrase?

If so, it seems like you could prompt the user for "FromLocation" and
"ToLocation", and then use an update query to modify any widget with
"Location1" to have "Location5", and also update their status to
"unavailable".

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for letting me clarify this; it helps me sort it out in my own
head.

It's a very simple db actually, just 2 tables; Location
(RecordID(AutoNumber), LocationNumber(Integer), LocationName(Text)) and
Inventory (RecordID(AutoNumber), WidgetID(Integer), Status(yes/no),
LocationNumber(Integer)). I have a need to reassign widgets from one
location to another location, as well as change the status from
"available"
to "unavailable." I might add that all the "available" widgets are in one
location (" #1"). Table Inventory has every widget in inventory with only
the widget status letting me know if it is available to transfer to
another
location.

The form allows the user to select the new location (from table Location)
via a list box, and then enter a number in a text box. So, now I have the
new location and the quantity (as seen in breakpoint mode).

I want to update table Inventory n number widgets with new location (e.g.,
from LocationNumber 1 to LocationNumber5) and status to Unavailable. It
must
also be noted that it is important to keep the widgets in numerical order.

At times, I will be taking widgets out of inventory by changing their
status, but not changing their location.

Can this be done? Thanks in advance.

--
JMorrell

You've described HOW you are trying to do somethign, but I still don't
have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that need to be
updated, give a value to be updated to, then select another set of
records
to be updated to a different value, then ... repeated until done?

Can you provide a bit more description? A real world example with real
or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Jeff-

The top n records are numerical and are sorted ascending in the query.
It
would be difficult to list all records along with a check box for the
user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n records,
deleting
them out of the original table, manipulating the fields, then re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell


:

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an order only
it
appears to understand. What field are you using in your underlying
table
to
provide a sort order, so that you can select the "top n records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

set [field] = "value" where [field] = "value" for
first
n
records?

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

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

tia,

 

[Guest]

Thanks for the quick reply.

My form has only 3 controls; Location list box, quantity text box, and a
button.

The transfer will ALWAYS come from Location #1, but the quantity can be
anything from 1 to 500+. All widgets from Location #1 will ALWAYS have a
status of "available" and go to status "unavailable" to the new location

All widgets have a number and are in numerical order in inventory. I want
to be able to take the next n number of widgets (in their numerical order)
and update their Location and Status via code. I just don't know the syntax
to select the top n records and update them only.

--
JMorrell

I am not complete clear on what's happening, but here goes...

It sounds like you want a way to have the user say "show all Location1
widgets now moved to Location5, and their status marked 'unavailable'". Is
that a fair paraphrase?

If so, it seems like you could prompt the user for "FromLocation" and
"ToLocation", and then use an update query to modify any widget with
"Location1" to have "Location5", and also update their status to
"unavailable".

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for letting me clarify this; it helps me sort it out in my own
head.

It's a very simple db actually, just 2 tables; Location
(RecordID(AutoNumber), LocationNumber(Integer), LocationName(Text)) and
Inventory (RecordID(AutoNumber), WidgetID(Integer), Status(yes/no),
LocationNumber(Integer)). I have a need to reassign widgets from one
location to another location, as well as change the status from
"available"
to "unavailable." I might add that all the "available" widgets are in one
location (" #1"). Table Inventory has every widget in inventory with only
the widget status letting me know if it is available to transfer to
another
location.

The form allows the user to select the new location (from table Location)
via a list box, and then enter a number in a text box. So, now I have the
new location and the quantity (as seen in breakpoint mode).

I want to update table Inventory n number widgets with new location (e.g.,
from LocationNumber 1 to LocationNumber5) and status to Unavailable. It
must
also be noted that it is important to keep the widgets in numerical order.

At times, I will be taking widgets out of inventory by changing their
status, but not changing their location.

Can this be done? Thanks in advance.

--
JMorrell

You've described HOW you are trying to do somethign, but I still don't
have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that need to be
updated, give a value to be updated to, then select another set of
records
to be updated to a different value, then ... repeated until done?

Can you provide a bit more description? A real world example with real
or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks Jeff-

The top n records are numerical and are sorted ascending in the query.
It
would be difficult to list all records along with a check box for the
user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n records,
deleting
them out of the original table, manipulating the fields, then re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell


:

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an order only
it
appears to understand. What field are you using in your underlying
table
to
provide a sort order, so that you can select the "top n records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

set [field] = "value" where [field] = "value" for
first
n
records?

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

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

tia,

 

[Jeff Boyce]

Take a look at Access HELP for the TOP property in a query. If you have
your widgets numbered, you can use a query to sort them by that number, then
use the TOP property to only take the top ##.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the quick reply.

My form has only 3 controls; Location list box, quantity text box, and a
button.

The transfer will ALWAYS come from Location #1, but the quantity can be
anything from 1 to 500+. All widgets from Location #1 will ALWAYS have a
status of "available" and go to status "unavailable" to the new location

All widgets have a number and are in numerical order in inventory. I want
to be able to take the next n number of widgets (in their numerical order)
and update their Location and Status via code. I just don't know the
syntax
to select the top n records and update them only.

--
JMorrell

I am not complete clear on what's happening, but here goes...

It sounds like you want a way to have the user say "show all Location1
widgets now moved to Location5, and their status marked 'unavailable'".
Is
that a fair paraphrase?

If so, it seems like you could prompt the user for "FromLocation" and
"ToLocation", and then use an update query to modify any widget with
"Location1" to have "Location5", and also update their status to
"unavailable".

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for letting me clarify this; it helps me sort it out in my own
head.

It's a very simple db actually, just 2 tables; Location
(RecordID(AutoNumber), LocationNumber(Integer), LocationName(Text)) and
Inventory (RecordID(AutoNumber), WidgetID(Integer), Status(yes/no),
LocationNumber(Integer)). I have a need to reassign widgets from one
location to another location, as well as change the status from
"available"
to "unavailable." I might add that all the "available" widgets are in
one
location (" #1"). Table Inventory has every widget in inventory with
only
the widget status letting me know if it is available to transfer to
another
location.

The form allows the user to select the new location (from table
Location)
via a list box, and then enter a number in a text box. So, now I have
the
new location and the quantity (as seen in breakpoint mode).

I want to update table Inventory n number widgets with new location
(e.g.,
from LocationNumber 1 to LocationNumber5) and status to Unavailable.
It
must
also be noted that it is important to keep the widgets in numerical
order.

At times, I will be taking widgets out of inventory by changing their
status, but not changing their location.

Can this be done? Thanks in advance.

--
JMorrell


:

You've described HOW you are trying to do somethign, but I still don't
have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that need to
be
updated, give a value to be updated to, then select another set of
records
to be updated to a different value, then ... repeated until done?

Can you provide a bit more description? A real world example with
real
or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks Jeff-

The top n records are numerical and are sorted ascending in the
query.
It
would be difficult to list all records along with a check box for
the
user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n records,
deleting
them out of the original table, manipulating the fields, then
re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell


:

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an order
only
it
appears to understand. What field are you using in your underlying
table
to
provide a sort order, so that you can select the "top n records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

set [field] = "value" where [field] = "value" for
first
n
records?

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

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

tia,

 

[Guest]

Right. Been there, Done that... individually.

I can do each action individually, but just haven't figured out the syntax
to do the SELECT and the UPDATE in one statement.


--
JMorrell

Take a look at Access HELP for the TOP property in a query. If you have
your widgets numbered, you can use a query to sort them by that number, then
use the TOP property to only take the top ##.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the quick reply.

My form has only 3 controls; Location list box, quantity text box, and a
button.

The transfer will ALWAYS come from Location #1, but the quantity can be
anything from 1 to 500+. All widgets from Location #1 will ALWAYS have a
status of "available" and go to status "unavailable" to the new location

All widgets have a number and are in numerical order in inventory. I want
to be able to take the next n number of widgets (in their numerical order)
and update their Location and Status via code. I just don't know the
syntax
to select the top n records and update them only.

--
JMorrell

I am not complete clear on what's happening, but here goes...

It sounds like you want a way to have the user say "show all Location1
widgets now moved to Location5, and their status marked 'unavailable'".
Is
that a fair paraphrase?

If so, it seems like you could prompt the user for "FromLocation" and
"ToLocation", and then use an update query to modify any widget with
"Location1" to have "Location5", and also update their status to
"unavailable".

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for letting me clarify this; it helps me sort it out in my own
head.

It's a very simple db actually, just 2 tables; Location
(RecordID(AutoNumber), LocationNumber(Integer), LocationName(Text)) and
Inventory (RecordID(AutoNumber), WidgetID(Integer), Status(yes/no),
LocationNumber(Integer)). I have a need to reassign widgets from one
location to another location, as well as change the status from
"available"
to "unavailable." I might add that all the "available" widgets are in
one
location (" #1"). Table Inventory has every widget in inventory with
only
the widget status letting me know if it is available to transfer to
another
location.

The form allows the user to select the new location (from table
Location)
via a list box, and then enter a number in a text box. So, now I have
the
new location and the quantity (as seen in breakpoint mode).

I want to update table Inventory n number widgets with new location
(e.g.,
from LocationNumber 1 to LocationNumber5) and status to Unavailable.
It
must
also be noted that it is important to keep the widgets in numerical
order.

At times, I will be taking widgets out of inventory by changing their
status, but not changing their location.

Can this be done? Thanks in advance.

--
JMorrell


:

You've described HOW you are trying to do somethign, but I still don't
have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that need to
be
updated, give a value to be updated to, then select another set of
records
to be updated to a different value, then ... repeated until done?

Can you provide a bit more description? A real world example with
real
or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks Jeff-

The top n records are numerical and are sorted ascending in the
query.
It
would be difficult to list all records along with a check box for
the
user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n records,
deleting
them out of the original table, manipulating the fields, then
re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell


:

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an order
only
it
appears to understand. What field are you using in your underlying
table
to
provide a sort order, so that you can select the "top n records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

set [field] = "value" where [field] = "value" for
first
n
records?

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

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

tia,

 

[Jeff Boyce]

So you are working in SQL statements, not in the query design window? Give
the design window a try ... some folks find the update query (with selection
criteria) easier to do from the design window rather than writing raw SQL.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Right. Been there, Done that... individually.

I can do each action individually, but just haven't figured out the syntax
to do the SELECT and the UPDATE in one statement.


--
JMorrell

Take a look at Access HELP for the TOP property in a query. If you have
your widgets numbered, you can use a query to sort them by that number,
then
use the TOP property to only take the top ##.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the quick reply.

My form has only 3 controls; Location list box, quantity text box, and
a
button.

The transfer will ALWAYS come from Location #1, but the quantity can be
anything from 1 to 500+. All widgets from Location #1 will ALWAYS have
a
status of "available" and go to status "unavailable" to the new
location

All widgets have a number and are in numerical order in inventory. I
want
to be able to take the next n number of widgets (in their numerical
order)
and update their Location and Status via code. I just don't know the
syntax
to select the top n records and update them only.

--
JMorrell


:

I am not complete clear on what's happening, but here goes...

It sounds like you want a way to have the user say "show all Location1
widgets now moved to Location5, and their status marked
'unavailable'".
Is
that a fair paraphrase?

If so, it seems like you could prompt the user for "FromLocation" and
"ToLocation", and then use an update query to modify any widget with
"Location1" to have "Location5", and also update their status to
"unavailable".

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for letting me clarify this; it helps me sort it out in my
own
head.

It's a very simple db actually, just 2 tables; Location
(RecordID(AutoNumber), LocationNumber(Integer), LocationName(Text))
and
Inventory (RecordID(AutoNumber), WidgetID(Integer), Status(yes/no),
LocationNumber(Integer)). I have a need to reassign widgets from
one
location to another location, as well as change the status from
"available"
to "unavailable." I might add that all the "available" widgets are
in
one
location (" #1"). Table Inventory has every widget in inventory
with
only
the widget status letting me know if it is available to transfer to
another
location.

The form allows the user to select the new location (from table
Location)
via a list box, and then enter a number in a text box. So, now I
have
the
new location and the quantity (as seen in breakpoint mode).

I want to update table Inventory n number widgets with new location
(e.g.,
from LocationNumber 1 to LocationNumber5) and status to Unavailable.
It
must
also be noted that it is important to keep the widgets in numerical
order.

At times, I will be taking widgets out of inventory by changing
their
status, but not changing their location.

Can this be done? Thanks in advance.

--
JMorrell


:

You've described HOW you are trying to do somethign, but I still
don't
have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that need
to
be
updated, give a value to be updated to, then select another set of
records
to be updated to a different value, then ... repeated until done?

Can you provide a bit more description? A real world example with
real
or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks Jeff-

The top n records are numerical and are sorted ascending in the
query.
It
would be difficult to list all records along with a check box for
the
user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n
records,
deleting
them out of the original table, manipulating the fields, then
re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell


:

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an order
only
it
appears to understand. What field are you using in your
underlying
table
to
provide a sort order, so that you can select the "top n
records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

set [field] = "value" where [field] = "value"
for
first
n
records?

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

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

tia,

 

[Guest]

Tried that first. Whenever I'm having problems with the correct syntax, I go
to the query design window to see the code. In this case I can do one or the
other, but not both in the same query.

this SELECT query works:
SELECT top 3 [tblInventory].Area, [tblInventory].Widget,
[tblInventory].Adult, [tblInventory].Available
FROM [tblInventory]
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0))
ORDER BY [tblInventory].Widget;

this UPDATE query works:
UPDATE [tblInventory]
SET [tblInventory].Area = 99, [tblInventory].Available = -1
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0));

but this combination throws a syntax error:
SELECT top 3 [tblInventory].Area, [tblInventory].Widget,
[tblInventory].Adult, [tblInventory].Available
FROM [tblInventory]
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0))
ORDER BY [tblInventory].Prox,
UPDATE [tblInventory]
SET [tblInventory].Area = 99, [tblInventory].Available = -1
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0));

the error is:
Syntax error (missing operator) in query expression ‘UPDATE[tblInventory]
SET [tblInventory].Hall = 99’.

have not been able to work past the error.

Thanks for being my sounding board. I'll keep digging.

--
JMorrell

So you are working in SQL statements, not in the query design window? Give
the design window a try ... some folks find the update query (with selection
criteria) easier to do from the design window rather than writing raw SQL.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Right. Been there, Done that... individually.

I can do each action individually, but just haven't figured out the syntax
to do the SELECT and the UPDATE in one statement.


--
JMorrell

Take a look at Access HELP for the TOP property in a query. If you have
your widgets numbered, you can use a query to sort them by that number,
then
use the TOP property to only take the top ##.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the quick reply.

My form has only 3 controls; Location list box, quantity text box, and
a
button.

The transfer will ALWAYS come from Location #1, but the quantity can be
anything from 1 to 500+. All widgets from Location #1 will ALWAYS have
a
status of "available" and go to status "unavailable" to the new
location

All widgets have a number and are in numerical order in inventory. I
want
to be able to take the next n number of widgets (in their numerical
order)
and update their Location and Status via code. I just don't know the
syntax
to select the top n records and update them only.

--
JMorrell


:

I am not complete clear on what's happening, but here goes...

It sounds like you want a way to have the user say "show all Location1
widgets now moved to Location5, and their status marked
'unavailable'".
Is
that a fair paraphrase?

If so, it seems like you could prompt the user for "FromLocation" and
"ToLocation", and then use an update query to modify any widget with
"Location1" to have "Location5", and also update their status to
"unavailable".

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for letting me clarify this; it helps me sort it out in my
own
head.

It's a very simple db actually, just 2 tables; Location
(RecordID(AutoNumber), LocationNumber(Integer), LocationName(Text))
and
Inventory (RecordID(AutoNumber), WidgetID(Integer), Status(yes/no),
LocationNumber(Integer)). I have a need to reassign widgets from
one
location to another location, as well as change the status from
"available"
to "unavailable." I might add that all the "available" widgets are
in
one
location (" #1"). Table Inventory has every widget in inventory
with
only
the widget status letting me know if it is available to transfer to
another
location.

The form allows the user to select the new location (from table
Location)
via a list box, and then enter a number in a text box. So, now I
have
the
new location and the quantity (as seen in breakpoint mode).

I want to update table Inventory n number widgets with new location
(e.g.,
from LocationNumber 1 to LocationNumber5) and status to Unavailable.
It
must
also be noted that it is important to keep the widgets in numerical
order.

At times, I will be taking widgets out of inventory by changing
their
status, but not changing their location.

Can this be done? Thanks in advance.

--
JMorrell


:

You've described HOW you are trying to do somethign, but I still
don't
have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that need
to
be
updated, give a value to be updated to, then select another set of
records
to be updated to a different value, then ... repeated until done?

Can you provide a bit more description? A real world example with
real
or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks Jeff-

The top n records are numerical and are sorted ascending in the
query.
It
would be difficult to list all records along with a check box for
the
user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n
records,
deleting
them out of the original table, manipulating the fields, then
re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell


:

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an order
only
it
appears to understand. What field are you using in your
underlying
table
to
provide a sort order, so that you can select the "top n
records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

set [field] = "value" where [field] = "value"
for
first
n
records?

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

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

tia,

 

[Jeff Boyce]

The first of those has
ORDER BY [tblInventory].Widget
while the third has
ORDER BY [tblInventory].Prox

Why the difference?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Tried that first. Whenever I'm having problems with the correct syntax, I
go
to the query design window to see the code. In this case I can do one or
the
other, but not both in the same query.

this SELECT query works:
SELECT top 3 [tblInventory].Area, [tblInventory].Widget,
[tblInventory].Adult, [tblInventory].Available
FROM [tblInventory]
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0))
ORDER BY [tblInventory].Widget;

this UPDATE query works:
UPDATE [tblInventory]
SET [tblInventory].Area = 99, [tblInventory].Available = -1
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0));

but this combination throws a syntax error:
SELECT top 3 [tblInventory].Area, [tblInventory].Widget,
[tblInventory].Adult, [tblInventory].Available
FROM [tblInventory]
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0))
ORDER BY [tblInventory].Prox,
UPDATE [tblInventory]
SET [tblInventory].Area = 99, [tblInventory].Available = -1
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0));

the error is:
Syntax error (missing operator) in query expression 'UPDATE[tblInventory]
SET [tblInventory].Hall = 99'.

have not been able to work past the error.

Thanks for being my sounding board. I'll keep digging.

--
JMorrell

So you are working in SQL statements, not in the query design window?
Give
the design window a try ... some folks find the update query (with
selection
criteria) easier to do from the design window rather than writing raw
SQL.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Right. Been there, Done that... individually.

I can do each action individually, but just haven't figured out the
syntax
to do the SELECT and the UPDATE in one statement.


--
JMorrell


:

Take a look at Access HELP for the TOP property in a query. If you
have
your widgets numbered, you can use a query to sort them by that
number,
then
use the TOP property to only take the top ##.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the quick reply.

My form has only 3 controls; Location list box, quantity text box,
and
a
button.

The transfer will ALWAYS come from Location #1, but the quantity can
be
anything from 1 to 500+. All widgets from Location #1 will ALWAYS
have
a
status of "available" and go to status "unavailable" to the new
location

All widgets have a number and are in numerical order in inventory.
I
want
to be able to take the next n number of widgets (in their numerical
order)
and update their Location and Status via code. I just don't know
the
syntax
to select the top n records and update them only.

--
JMorrell


:

I am not complete clear on what's happening, but here goes...

It sounds like you want a way to have the user say "show all
Location1
widgets now moved to Location5, and their status marked
'unavailable'".
Is
that a fair paraphrase?

If so, it seems like you could prompt the user for "FromLocation"
and
"ToLocation", and then use an update query to modify any widget
with
"Location1" to have "Location5", and also update their status to
"unavailable".

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for letting me clarify this; it helps me sort it out in my
own
head.

It's a very simple db actually, just 2 tables; Location
(RecordID(AutoNumber), LocationNumber(Integer),
LocationName(Text))
and
Inventory (RecordID(AutoNumber), WidgetID(Integer),
Status(yes/no),
LocationNumber(Integer)). I have a need to reassign widgets from
one
location to another location, as well as change the status from
"available"
to "unavailable." I might add that all the "available" widgets
are
in
one
location (" #1"). Table Inventory has every widget in inventory
with
only
the widget status letting me know if it is available to transfer
to
another
location.

The form allows the user to select the new location (from table
Location)
via a list box, and then enter a number in a text box. So, now I
have
the
new location and the quantity (as seen in breakpoint mode).

I want to update table Inventory n number widgets with new
location
(e.g.,
from LocationNumber 1 to LocationNumber5) and status to
Unavailable.
It
must
also be noted that it is important to keep the widgets in
numerical
order.

At times, I will be taking widgets out of inventory by changing
their
status, but not changing their location.

Can this be done? Thanks in advance.

--
JMorrell


:

You've described HOW you are trying to do somethign, but I still
don't
have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that
need
to
be
updated, give a value to be updated to, then select another set
of
records
to be updated to a different value, then ... repeated until
done?

Can you provide a bit more description? A real world example
with
real
or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks Jeff-

The top n records are numerical and are sorted ascending in
the
query.
It
would be difficult to list all records along with a check box
for
the
user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n
records,
deleting
them out of the original table, manipulating the fields, then
re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell


:

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an
order
only
it
appears to understand. What field are you using in your
underlying
table
to
provide a sort order, so that you can select the "top n
records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
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

set [field] = "value" where [field] =
"value"
for
first
n
records?

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

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

tia,

 

[Guest]

No difference, my error in trying to keep it all straight while here. Prox =
real world; widget = just here. Thought I would try to lessen the confusion
by using "widget."
--
JMorrell

The first of those has
ORDER BY [tblInventory].Widget
while the third has
ORDER BY [tblInventory].Prox

Why the difference?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Tried that first. Whenever I'm having problems with the correct syntax, I
go
to the query design window to see the code. In this case I can do one or
the
other, but not both in the same query.

this SELECT query works:
SELECT top 3 [tblInventory].Area, [tblInventory].Widget,
[tblInventory].Adult, [tblInventory].Available
FROM [tblInventory]
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0))
ORDER BY [tblInventory].Widget;

this UPDATE query works:
UPDATE [tblInventory]
SET [tblInventory].Area = 99, [tblInventory].Available = -1
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0));

but this combination throws a syntax error:
SELECT top 3 [tblInventory].Area, [tblInventory].Widget,
[tblInventory].Adult, [tblInventory].Available
FROM [tblInventory]
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0))
ORDER BY [tblInventory].Prox,
UPDATE [tblInventory]
SET [tblInventory].Area = 99, [tblInventory].Available = -1
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0));

the error is:
Syntax error (missing operator) in query expression 'UPDATE[tblInventory]
SET [tblInventory].Hall = 99'.

have not been able to work past the error.

Thanks for being my sounding board. I'll keep digging.

--
JMorrell

So you are working in SQL statements, not in the query design window?
Give
the design window a try ... some folks find the update query (with
selection
criteria) easier to do from the design window rather than writing raw
SQL.

Regards

Jeff Boyce
Microsoft Office/Access MVP



Right. Been there, Done that... individually.

I can do each action individually, but just haven't figured out the
syntax
to do the SELECT and the UPDATE in one statement.


--
JMorrell


:

Take a look at Access HELP for the TOP property in a query. If you
have
your widgets numbered, you can use a query to sort them by that
number,
then
use the TOP property to only take the top ##.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the quick reply.

My form has only 3 controls; Location list box, quantity text box,
and
a
button.

The transfer will ALWAYS come from Location #1, but the quantity can
be
anything from 1 to 500+. All widgets from Location #1 will ALWAYS
have
a
status of "available" and go to status "unavailable" to the new
location

All widgets have a number and are in numerical order in inventory.
I
want
to be able to take the next n number of widgets (in their numerical
order)
and update their Location and Status via code. I just don't know
the
syntax
to select the top n records and update them only.

--
JMorrell


:

I am not complete clear on what's happening, but here goes...

It sounds like you want a way to have the user say "show all
Location1
widgets now moved to Location5, and their status marked
'unavailable'".
Is
that a fair paraphrase?

If so, it seems like you could prompt the user for "FromLocation"
and
"ToLocation", and then use an update query to modify any widget
with
"Location1" to have "Location5", and also update their status to
"unavailable".

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for letting me clarify this; it helps me sort it out in my
own
head.

It's a very simple db actually, just 2 tables; Location
(RecordID(AutoNumber), LocationNumber(Integer),
LocationName(Text))
and
Inventory (RecordID(AutoNumber), WidgetID(Integer),
Status(yes/no),
LocationNumber(Integer)). I have a need to reassign widgets from
one
location to another location, as well as change the status from
"available"
to "unavailable." I might add that all the "available" widgets
are
in
one
location (" #1"). Table Inventory has every widget in inventory
with
only
the widget status letting me know if it is available to transfer
to
another
location.

The form allows the user to select the new location (from table
Location)
via a list box, and then enter a number in a text box. So, now I
have
the
new location and the quantity (as seen in breakpoint mode).

I want to update table Inventory n number widgets with new
location
(e.g.,
from LocationNumber 1 to LocationNumber5) and status to
Unavailable.
It
must
also be noted that it is important to keep the widgets in
numerical
order.

At times, I will be taking widgets out of inventory by changing
their
status, but not changing their location.

Can this be done? Thanks in advance.

--
JMorrell


:

You've described HOW you are trying to do somethign, but I still
don't
have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that
need
to
be
updated, give a value to be updated to, then select another set
of
records
to be updated to a different value, then ... repeated until
done?

Can you provide a bit more description? A real world example
with
real
or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks Jeff-

The top n records are numerical and are sorted ascending in
the
query.
It
would be difficult to list all records along with a check box
for
the
user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n
records,
deleting
them out of the original table, manipulating the fields, then
re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell


:

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an
order
only
it
appears to understand. What field are you using in your
underlying
table
to
provide a sort order, so that you can select the "top n
records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
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?

 

[Jeff Boyce]

Darn! I hoped we'd found the glitch!!

Regards

Jeff Boyce
Microsoft Office/Access MVP

No difference, my error in trying to keep it all straight while here.
Prox =
real world; widget = just here. Thought I would try to lessen the
confusion
by using "widget."
--
JMorrell

The first of those has
ORDER BY [tblInventory].Widget
while the third has
ORDER BY [tblInventory].Prox

Why the difference?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Tried that first. Whenever I'm having problems with the correct
syntax, I
go
to the query design window to see the code. In this case I can do one
or
the
other, but not both in the same query.

this SELECT query works:
SELECT top 3 [tblInventory].Area, [tblInventory].Widget,
[tblInventory].Adult, [tblInventory].Available
FROM [tblInventory]
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0))
ORDER BY [tblInventory].Widget;

this UPDATE query works:
UPDATE [tblInventory]
SET [tblInventory].Area = 99, [tblInventory].Available = -1
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0));

but this combination throws a syntax error:
SELECT top 3 [tblInventory].Area, [tblInventory].Widget,
[tblInventory].Adult, [tblInventory].Available
FROM [tblInventory]
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0))
ORDER BY [tblInventory].Prox,
UPDATE [tblInventory]
SET [tblInventory].Area = 99, [tblInventory].Available = -1
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0));

the error is:
Syntax error (missing operator) in query expression
'UPDATE[tblInventory]
SET [tblInventory].Hall = 99'.

have not been able to work past the error.

Thanks for being my sounding board. I'll keep digging.

--
JMorrell


:

So you are working in SQL statements, not in the query design window?
Give
the design window a try ... some folks find the update query (with
selection
criteria) easier to do from the design window rather than writing raw
SQL.

Regards

Jeff Boyce
Microsoft Office/Access MVP



Right. Been there, Done that... individually.

I can do each action individually, but just haven't figured out the
syntax
to do the SELECT and the UPDATE in one statement.


--
JMorrell


:

Take a look at Access HELP for the TOP property in a query. If you
have
your widgets numbered, you can use a query to sort them by that
number,
then
use the TOP property to only take the top ##.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the quick reply.

My form has only 3 controls; Location list box, quantity text
box,
and
a
button.

The transfer will ALWAYS come from Location #1, but the quantity
can
be
anything from 1 to 500+. All widgets from Location #1 will
ALWAYS
have
a
status of "available" and go to status "unavailable" to the new
location

All widgets have a number and are in numerical order in
inventory.
I
want
to be able to take the next n number of widgets (in their
numerical
order)
and update their Location and Status via code. I just don't know
the
syntax
to select the top n records and update them only.

--
JMorrell


:

I am not complete clear on what's happening, but here goes...

It sounds like you want a way to have the user say "show all
Location1
widgets now moved to Location5, and their status marked
'unavailable'".
Is
that a fair paraphrase?

If so, it seems like you could prompt the user for
"FromLocation"
and
"ToLocation", and then use an update query to modify any widget
with
"Location1" to have "Location5", and also update their status to
"unavailable".

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for letting me clarify this; it helps me sort it out in
my
own
head.

It's a very simple db actually, just 2 tables; Location
(RecordID(AutoNumber), LocationNumber(Integer),
LocationName(Text))
and
Inventory (RecordID(AutoNumber), WidgetID(Integer),
Status(yes/no),
LocationNumber(Integer)). I have a need to reassign widgets
from
one
location to another location, as well as change the status
from
"available"
to "unavailable." I might add that all the "available"
widgets
are
in
one
location (" #1"). Table Inventory has every widget in
inventory
with
only
the widget status letting me know if it is available to
transfer
to
another
location.

The form allows the user to select the new location (from
table
Location)
via a list box, and then enter a number in a text box. So,
now I
have
the
new location and the quantity (as seen in breakpoint mode).

I want to update table Inventory n number widgets with new
location
(e.g.,
from LocationNumber 1 to LocationNumber5) and status to
Unavailable.
It
must
also be noted that it is important to keep the widgets in
numerical
order.

At times, I will be taking widgets out of inventory by
changing
their
status, but not changing their location.

Can this be done? Thanks in advance.

--
JMorrell


:

You've described HOW you are trying to do somethign, but I
still
don't
have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that
need
to
be
updated, give a value to be updated to, then select another
set
of
records
to be updated to a different value, then ... repeated until
done?

Can you provide a bit more description? A real world example
with
real
or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
Thanks Jeff-

The top n records are numerical and are sorted ascending in
the
query.
It
would be difficult to list all records along with a check
box
for
the
user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n
records,
deleting
them out of the original table, manipulating the fields,
then
re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell


:

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an
order
only
it
appears to understand. What field are you using in your
underlying
table
to
provide a sort order, so that you can select the "top n
records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
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?

 

[Fairfield]

Tried that first. Whenever I'm having problems with the correct syntax, I go
to the query design window to see the code. In this case I can do one or the
other, but not both in the same query.

this SELECT query works:
SELECT top 3 [tblInventory].Area, [tblInventory].Widget,
[tblInventory].Adult, [tblInventory].Available
FROM [tblInventory]
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0))
ORDER BY [tblInventory].Widget;

this UPDATE query works:
UPDATE [tblInventory]
SET [tblInventory].Area = 99, [tblInventory].Available = -1
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0));

but this combination throws a syntax error:
SELECT top 3 [tblInventory].Area, [tblInventory].Widget,
[tblInventory].Adult, [tblInventory].Available
FROM [tblInventory]
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0))
ORDER BY [tblInventory].Prox,
UPDATE [tblInventory]
SET [tblInventory].Area = 99, [tblInventory].Available = -1
WHERE ((([tblInventory].Area)=1) AND (([tblInventory].Available)=0));

the error is:
Syntax error (missing operator) in query expression 'UPDATE[tblInventory]
SET [tblInventory].Hall = 99'.

have not been able to work past the error.

Thanks for being my sounding board. I'll keep digging.

--
JMorrell

So you are working in SQL statements, not in the query design window? Give
the design window a try ... some folks find the update query (with selection
criteria) easier to do from the design window rather than writing raw SQL.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Right. Been there, Done that... individually.

I can do each action individually, but just haven't figured out the syntax
to do the SELECT and the UPDATE in one statement.


--
JMorrell


:

Take a look at Access HELP for the TOP property in a query. If you have
your widgets numbered, you can use a query to sort them by that number,
then
use the TOP property to only take the top ##.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for the quick reply.

My form has only 3 controls; Location list box, quantity text box, and
a
button.

The transfer will ALWAYS come from Location #1, but the quantity can be
anything from 1 to 500+. All widgets from Location #1 will ALWAYS have
a
status of "available" and go to status "unavailable" to the new
location

All widgets have a number and are in numerical order in inventory. I
want
to be able to take the next n number of widgets (in their numerical
order)
and update their Location and Status via code. I just don't know the
syntax
to select the top n records and update them only.

--
JMorrell


:

I am not complete clear on what's happening, but here goes...

It sounds like you want a way to have the user say "show all Location1
widgets now moved to Location5, and their status marked
'unavailable'".
Is
that a fair paraphrase?

If so, it seems like you could prompt the user for "FromLocation" and
"ToLocation", and then use an update query to modify any widget with
"Location1" to have "Location5", and also update their status to
"unavailable".

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for letting me clarify this; it helps me sort it out in my
own
head.

It's a very simple db actually, just 2 tables; Location
(RecordID(AutoNumber), LocationNumber(Integer), LocationName(Text))
and
Inventory (RecordID(AutoNumber), WidgetID(Integer), Status(yes/no),
LocationNumber(Integer)). I have a need to reassign widgets from
one
location to another location, as well as change the status from
"available"
to "unavailable." I might add that all the "available" widgets are
in
one
location (" #1"). Table Inventory has every widget in inventory
with
only
the widget status letting me know if it is available to transfer to
another
location.

The form allows the user to select the new location (from table
Location)
via a list box, and then enter a number in a text box. So, now I
have
the
new location and the quantity (as seen in breakpoint mode).

I want to update table Inventory n number widgets with new location
(e.g.,
from LocationNumber 1 to LocationNumber5) and status to Unavailable.
It
must
also be noted that it is important to keep the widgets in numerical
order.

At times, I will be taking widgets out of inventory by changing
their
status, but not changing their location.

Can this be done? Thanks in advance.

--
JMorrell


:

You've described HOW you are trying to do somethign, but I still
don't
have
a very clear picture of WHAT you want to accomplish.

Are you saying that you want the user to select records that need
to
be
updated, give a value to be updated to, then select another set of
records
to be updated to a different value, then ... repeated until done?

Can you provide a bit more description? A real world example with
real
or
invented data can help clarify the WHAT...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thanks Jeff-

The top n records are numerical and are sorted ascending in the
query.
It
would be difficult to list all records along with a check box for
the
user
to
select which records are to be updated.

I've even thought of making a temp table to hold the top n
records,
deleting
them out of the original table, manipulating the fields, then
re-adding
them
back to the origina table. Not a pleasant thought.

any and all suggestions are appreciated.
--
JMorrell


:

Define "first"...

Access stores data in "buckets" (AKA, "Tables"), but in an order
only
it
appears to understand. What field are you using in your
underlying
table
to
provide a sort order, so that you can select the "top n
records"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

set [field] = "value" where [field] = "value"
for
first
n
records?

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

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

tia,

 

[Fairfield]

Hi,
Did you ever get this solved?
I'm having the exact same problem trying to combine an UPDATE with a
TOP in MS Access 2003.
Thanks,
Fairfield

 

[David F Cox]

this works:

UPDATE Table1 SET Table1.qty = 99
WHERE (((Table1.pets) In (SELECT TOP 5 x.pets FROM table1 AS x ORDER BY
x.pets));