Show the first record (1 Viewer)

JRPMD · Apr 22, 2018

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 · Apr 22, 2018

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 · Apr 22, 2018

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

plog · Apr 22, 2018

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 · Apr 22, 2018

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 · Apr 22, 2018

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

JRPMD · Apr 22, 2018

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

Thank you Paul

JRPMD · Apr 22, 2018

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 · Apr 22, 2018

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

Galaxiom · Apr 23, 2018

arnelgp said:
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 · Apr 23, 2018

Another mvp comes along...

Show the first record (1 Viewer)

JRPMD

Registered User.

pbaldy

Wino Moderator

arnelgp

..forever waiting... waiting for jellybean!

Attachments

plog

Banishment Pending

arnelgp

..forever waiting... waiting for jellybean!

isladogs

MVP / VIP

JRPMD

Registered User.

JRPMD

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Galaxiom

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread