Re: Update top n records

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


"Jeff Boyce" wrote:

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

"JMorrell" <JMorrell@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AD64E09D-EF34-4311-8664-833E4C5CCE60@xxxxxxxxxxxxxxxx
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


"Jeff Boyce" wrote:

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

"JMorrell" <JMorrell@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:02820559-7514-4CD6-8E1D-5E51D1BFD6AC@xxxxxxxxxxxxxxxx
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


"Jeff Boyce" wrote:

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


"JMorrell" <JMorrell@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3D8F018B-889A-497A-80D9-CC6B52F99728@xxxxxxxxxxxxxxxx
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


"Jeff Boyce" wrote:

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

"JMorrell" <JMorrell@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8B56D54F-8A37-47F8-921F-D21B8D174D04@xxxxxxxxxxxxxxxx
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 [table] 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,
--
JMorrell












.

Relevant Pages

  • Re: Update top n records
    ... So you are working in SQL statements, not in the query design window? ... "Jeff Boyce" wrote: ... All widgets have a number and are in numerical order in inventory. ...
    (microsoft.public.access.formscoding)
  • Re: Update top n records
    ... Thanks for letting me clarify this; it helps me sort it out in my own head. ... Table Inventory has every widget in inventory with only ... I want to update table Inventory n number widgets with new location (e.g., ... "Jeff Boyce" wrote: ...
    (microsoft.public.access.formscoding)
  • Re: Update top n records
    ... All widgets have a number and are in numerical order in inventory. ... "Jeff Boyce" wrote: ...
    (microsoft.public.access.formscoding)
  • Re: Update top n records
    ... Whenever I'm having problems with the correct syntax, ... to the query design window to see the code. ... "Jeff Boyce" wrote: ... All widgets have a number and are in numerical order in inventory. ...
    (microsoft.public.access.formscoding)
  • Re: Update top n records
    ... widgets now moved to Location5, ... Microsoft Office/Access MVP ... Table Inventory has every widget in inventory with only ... "Jeff Boyce" wrote: ...
    (microsoft.public.access.formscoding)