Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-21-2018, 07:42 PM   #1
JRPMD
Newly Registered User
 
Join Date: Nov 2012
Posts: 50
Thanks: 29
Thanked 0 Times in 0 Posts
JRPMD is on a distinguished road
Show the first record

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.

JRPMD is offline   Reply With Quote
Old 04-21-2018, 08:08 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,105
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Show the first record

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 04-21-2018, 10:35 PM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,392
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Show the first record

you need a function ti accomplish that.
see query1 and the function in module1
Attached Files
File Type: zip TEST.zip (18.3 KB, 13 views)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
JRPMD (04-22-2018)
Old 04-22-2018, 05:45 AM   #4
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,348
Thanks: 10
Thanked 2,270 Times in 2,222 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Show the first record

Quote:
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.
plog is offline   Reply With Quote
The Following 4 Users Say Thank You to plog For This Useful Post:
Frothingslosh (04-23-2018), Galaxiom (04-22-2018), JRPMD (04-22-2018), pbaldy (04-22-2018)
Old 04-22-2018, 06:08 AM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,392
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Show the first record

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
JRPMD (04-22-2018)
Old 04-22-2018, 06:12 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,354
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Show the first record

Arnel
The OP only wants the first row - see post #1
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
JRPMD (04-22-2018)
Old 04-22-2018, 06:58 AM   #7
JRPMD
Newly Registered User
 
Join Date: Nov 2012
Posts: 50
Thanks: 29
Thanked 0 Times in 0 Posts
JRPMD is on a distinguished road
Thumbs up Re: Show the first record

Quote:
Originally Posted by arnelgp View Post
you need a function ti accomplish that.
see query1 and the function in module1
Thank you Paul

JRPMD is offline   Reply With Quote
Old 04-22-2018, 07:21 AM   #8
JRPMD
Newly Registered User
 
Join Date: Nov 2012
Posts: 50
Thanks: 29
Thanked 0 Times in 0 Posts
JRPMD is on a distinguished road
Re: Show the first record

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.
JRPMD is offline   Reply With Quote
Old 04-22-2018, 07:31 AM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,392
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Show the first record

Create another simple select query base on your first and do the adding there.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-22-2018, 03:40 PM   #10
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,601
Thanks: 89
Thanked 1,492 Times in 1,408 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Show the first record

Quote:
Originally Posted by arnelgp View Post
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.
Galaxiom is offline   Reply With Quote
Old 04-22-2018, 06:00 PM   #11
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,392
Thanks: 68
Thanked 2,699 Times in 2,584 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Show the first record

Another mvp comes along...

__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 04-22-2018 at 06:06 PM.
arnelgp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] open form and create new record and show with record id megatronixs Forms 4 02-22-2017 06:39 AM
[SOLVED] Running an Update query on record and have Form show updated record DLe Modules & VBA 4 01-28-2015 06:03 PM
T-SQL to show first record in a grouping - how to do a First record Rx_ SQL Server 1 08-27-2014 11:33 AM
Only show actual records, don't show new record?? scooble Forms 2 12-09-2010 04:13 AM
show a specific record in a subform, after creating the record from another form mac_freelancer Forms 0 02-06-2007 07:29 PM




All times are GMT -8. The time now is 08:48 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World