Update Query (1 Viewer)

Hello. I'm trying to do an update query in Access 2013 with SQL. I need the Set value to equal the min check number from my sub query from the update statement .


UPDATE [Table1] SET Batch = min[Check Number]
WHERE ((([Table1].[Check Number]) In (Select Top 50 [Check Number] from [Table1]
WHERE [Batch Type ID] = 22 and Batch = '0'
ORDER by [Check Number])));


Any assistance would be much appreciated.

Thanks!

if you quit using sql, and use a query, you get better error messages.
you didnt give us an error message, BUT
i suspect its this:
--- you cannot sum AND update in the same query.---
you must sum the data to a temp table, then run an update query from that.

I'm looking to pull the smallest check number to use as a batch number, not do a sum.


I also tried using a different method. My error message is

"Operation must use an updateable query"

This is the query I used when using the temp table.

UPDATE [Table2] SET Batch = (select min([Table2].[Check Number]) from [Table2])

I would use the query builder but that wasn't working for me either.


Thank you

Sum and max and min....are all used in the SUMMATION mode in a query.

Any assistance would be appreciated. I'm still racking my brain on this.

I would like to update Top 50 records with the smallest check number as the batch number within the Top 50 record set.

The smallest check number will be the batch number for the Top 50 records.

My error message is

"Syntax error (missing operator)
in query expression"

This is the query I used when using the temp table.

UPDATE [Table2] SET Batch = [temptbl].[mincknum] from [table2]
Where [table2].[check number]in (select top 50 [check number] from [table2]

I understand I cant use summation for the update field. Thank you Ranman.

I'm still unable to find a solution to this.


Thank you