Show the first record

[JRPMD]

Hello , I have a table with many numeric fields and I want to make a query that shows the first record of a descending order for each field.
For example ,
Table
Id Field1 Field2 Fied3 Field4
1 19 31 54 90
2 45 66 71 28
3 23 37 45 54
4 78 84 81 67
5 23 14 41 38

Query
Field1 Field2 Field3 Field4
78 84 81 90

I made a query for each field that shows the first record of the descending order .
But I can`t combine them in a new query based on the queries of each field , to see all them in a row. The query assistant can`t make it.
Can you tell me how can I do this?
Thank you very much.

 

[pbaldy]

Start a query and add those 4 fields to the grid. Click on the Totals icon in the ribbon. Change Group By to Max in each field and run the query.

 

[arnelgp]

you need a function ti accomplish that.
see query1 and the function in module1

 

[plog]

you need a function ti accomplish that

Seriously, this needs to stop.

It's as if you slightly glance at the original posters post, totally miss other's valid responses and immediately fire up a module to solve everything via code.

So many of your posts are like this. The answer is not always VBA. I am really starting to question your SQL abilities. I have no doubt if your codung skills, but that's not always the right tool. Every problem isn't a nail waiting for a VBA hammer

jRPMD--pbaldy read and understood your question and provided the correct solution, please use his method.

 

[arnelgp]

I recognised mr.pbaldys answer.
I understand, seriously. Ive tried the total query and it will only return 1 row.
It may be dine through sql but will take more string.
If you cant contribute, its you who need to stop.

 

[isladogs]

Arnel
The OP only wants the first row - see post #1

 

[JRPMD]

you need a function ti accomplish that.
see query1 and the function in module1

Thank you Paul

 

[JRPMD]

Thank you all!
Now I need to add this first records .
I try an expresion in the query grid :
Suma: [Field1]+[Field2]+[Field3]+[Field4] but it doesn't work.

 

[arnelgp]

Create another simple select query base on your first and do the adding there.

 

[Galaxiom]

It may be [f]ine through sql but will take more string.

arnlegp makes a mistake that is very common among developers with limited experience by thinking that the best course is always the one with the least text in the query. Consequently they frequently resort to inefficient query structures and create user defined functions to do what can be done in SQL.

User defined functions perform very poorly compared to native SQL, so wherever practical, user defined functions are best avoided.

 

[arnelgp]

Another mvp comes along...