Access update query not pulling first record for updating (4 Viewers)

[squale2]

I am trying to run an update query but when it pulls the data from the REPORT# table it is not the first available record. Is there a way in an update query to tell it to choose the min number? Here is what the query looks like:

UPDATE [User Data] INNER JOIN [REPORT#] ON [User Data].TYPE = [REPORT#].TYPE SET [User Data].[USER NMR Number] = [REPORT#]![REPORT#]
WHERE ((([User Data].[USER NMR Number]) Is Null) AND (([User Data].USERAUTONUMBER)=[Forms]![NMR USER ENTRY]![AUTO NUMBER]) AND (([REPORT#].AVAILABLE)="Y") AND (([REPORT#].Number)>1));

The bold field is the field that I would want to get the smallest available value

Thanks in advance

 

[plog]

I have so many many thoughts. First, let's deal with your actual issue. Why do you want to do this?

Normally, in a relational database data doesn't get moved around, doesn't get stored in multiple places. An UPDATE query is a huge red flag of either an improper design or a lack of understanding of how you can use queries to your advantage. Why can't you just run a SELECT query and reference that query when you need this data? Why must it be stored in a seperate table?

On to your table and fields.

1. Only use alphanumeric characters in field names. # and spaces shouldn't be used in names. It makes coding and querying that much more difficult when you do. Spell out 'number' and eliminate the spaces by using camel case (LikeThis).

2. Reserved words as field names.
3. Too generically named fields.

'Number' is a poor choice for a field name because it is both a reserved word:

Learn about Access reserved words and symbols - Microsoft Support

support.microsoft.com

Those are names used internally by Access and shouldn't be used as names in your database for things you create. Makes coding and querying harder down the line. Also, the world and your database is filled with Numbers, having that as a field name doesn't help people know what it represents. Prefix/suffix it with what that numbers if for (e.g. CustomerNumber, NumberOfSales, etc.)

4. Use the right field type for your fields. Available seems like it can only have 2 values--yes or no. If that is the case you shouldn't use a string as its datatype but a Yes/No field. This ensures the right data gets into your database.

Again, why do you need to save this value via an UPDATE query?

 

[CJ_London]

Is there a way in an update query to tell it to choose the min number?

I agree with everything plog says but to get the min number you either need to sort it , perhaps with a top 1 predicate or use another query to get the minimum number

 

[Pat Hartman]

Welcome. You've gotten a lot of good advice from two of our best on your first try As CJ pointed out, tables and queries are unordered sets. Think of a bag of marbles. When you reach in to get the first one, you get what you get. If you have a record that is "first" logically, you need to order the recordset to tell the query engine how the rows should be ordered as they are presented to you in a query. Physical and Logical order are totally different in database tables. Flat files like Excel and text files have a physical order which may be logical or not but it is fixed and the rows will always be returned in that specific order. There will never be a variance. In theory, the same query run multiple times could produce a recordset in a different order each time. That doesn't normally happen with Access (Jet and ACE) but it could happen with a large table in SQL Server that uses multiple threads to retrieve data.