Re: Update top n records

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"Jeff Boyce" wrote:

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



"JMorrell" <JMorrell@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D9CEED0A-BD6B-4057-9636-EFA1BBFB22D8@xxxxxxxxxxxxxxxx
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















.

Quantcast