UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Get Only 1 Result / Row From Multiple Results In Queries..?, Access 2013    
 
   
Steven Conner
post Aug 14 2019, 05:09 AM
Post#1



Posts: 27
Joined: 9-August 19



Hi Everyone

I made a query and got some results after running the database.
However ' I need queries to show me only 1 Result / Row even though
it gives me all the results.

Attached is a snap shot of an example I got " days " as 36 which I did on query.

Since it returns the lowest value which is 36 days, I am partially
right only as I need only 1 result from any of the 36 days.
Access is showing me 7 results of 36 days.

Does anyone know how I to get for only 1 result instead of 7 results?

Please help me with the expression and where should I type the expression into which
field? "Criteria " " Or "

Should I create another empty field for an expression?
If so , Can anyone help me with the formulae/ expression?

Thank You
S Conner
This post has been edited by Steven Conner: Aug 14 2019, 05:12 AM
Attached File(s)
Attached File  6211CA92_CD36_46F1_A9BE_6A3688B0DB38.jpeg ( 178.14K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Aug 14 2019, 05:15 AM
Post#2


UtterAccess VIP
Posts: 10,028
Joined: 25-October 10
From: Gulf South USA


Hi: Please show us the SQL you used to produce the results you posted. Thanks.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
jleach
post Aug 14 2019, 05:25 AM
Post#3


UtterAccess Editor
Posts: 10,092
Joined: 7-December 09
From: St Augustine, FL


You could do a subsequent sort somehow, then SELECT TOP 1 to get the first in the list.

Or you could reduce the amount of fields so an appropriate GROUP BY returns the relevant information.

It really depends on what criteria you require for the selection: does it matter which of the "36" rows gets selected? Your task would be to determine how you can positively identify the correct record you need, tell SQL how to organize it, and go from there.

(as noted by Joe, the SQL you used to come up with this would be quite helpful)


--------------------
Go to the top of the page
 
Steven Conner
post Aug 14 2019, 05:25 AM
Post#4



Posts: 27
Joined: 9-August 19



Hi Joe

Thanks for the reply.

I am currently not in office and will post the SQL for you tomorrow.

Thanks in advance.
Best Regards
S . Conner
Go to the top of the page
 
RJD
post Aug 14 2019, 06:04 AM
Post#5


UtterAccess VIP
Posts: 10,028
Joined: 25-October 10
From: Gulf South USA


Okay. I'll check back later and take a look at the SQL you post. Then we can go from there.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Steven Conner
post Aug 14 2019, 09:53 PM
Post#6



Posts: 27
Joined: 9-August 19



Hi Joe and Company

Below is the SQL from my queries.

SELECT [All Data].[Aircraft Reg], [All Data].TSN, [All Data].CSN, [All Data].[Serial Number1], [All Data].[Available Cycles], [All Data].Days, Min([All Data].Project) AS MinOfProject
FROM [All Data]
GROUP BY [All Data].[Aircraft Reg], [All Data].TSN, [All Data].CSN, [All Data].[Serial Number1], [All Data].[Available Cycles], [All Data].Days
HAVING ((([All Data].[Aircraft Reg])="BBB"))
ORDER BY Min([All Data].Project);

I am also attaching pics for my design view too.

Thanks for the help.
Regards
Steven Conner.
This post has been edited by Steven Conner: Aug 14 2019, 09:55 PM
Attached File(s)
Attached File  Pic.PNG ( 255.14K )Number of downloads: 5
 
Go to the top of the page
 
June7
post Aug 14 2019, 11:04 PM
Post#7



Posts: 808
Joined: 25-January 16



And what does raw data look like? Do all 7 36-day records really have the same date? Do you want to see the serial number that has the minimum date? If serial number is not important, then don't include it in query and only 1 36-day record will return.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Steven Conner
post Aug 14 2019, 11:44 PM
Post#8



Posts: 27
Joined: 9-August 19



Hi June7

Yes , All the 7 Serial Numbers has the same DATE and CSN , that is why the calculation is giving 7 item as 36 Days.

The driver of this data is any SERIAL NUMBER that has the same CSN , the likelihood of getting more than 1 is obvious.

But I need queries to choose only 1 result even though the 7

My objective is to show for only 1 result with all the rows fields shown.
Example, if Access choose , Serial Number P136968-02 , then all its rows need to be shown too as I need to export this 1 result into a FORM / REPORT , but currently it is showing 7 results which I do not want.

The FORM / REPORT will be another subject , but I need to get my Queries right first.

It does not matter which Serial Number , so long as ACCESS choose any SERIAL Number from the 7 results.
It can be P136968-02
P136481-02
P136965-01
P137840-01
etc.....

So long it return only 1 result. I need not need to see all the other 6 results , as any SERIAL Number will do from the 7 results.

Thank you
S Conner
This post has been edited by Steven Conner: Aug 14 2019, 11:53 PM
Go to the top of the page
 
June7
post Aug 15 2019, 12:05 AM
Post#9



Posts: 808
Joined: 25-January 16



Then just use Min or Max or First or Last on the serial number field.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
RJD
post Aug 15 2019, 12:14 AM
Post#10


UtterAccess VIP
Posts: 10,028
Joined: 25-October 10
From: Gulf South USA


Hi: The repeated lines are caused by you including [Serial Number1] in the GROUP BY. Remove that from GROUP BY and, as June7 said, use something like Min or Max on that field in the SELECT area ...

SELECT [All Data].[Aircraft Reg], [All Data].TSN, [All Data].CSN, Min([All Data].[Serial Number1]), [All Data].[Available Cycles], [All Data].Days, Min([All Data].Project) AS MinOfProject
FROM [All Data]
GROUP BY [All Data].[Aircraft Reg], [All Data].TSN, [All Data].CSN, [All Data].[Available Cycles], [All Data].Days
HAVING ((([All Data].[Aircraft Reg])="BBB"))
ORDER BY Min([All Data].Project);

See how that works now ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Steven Conner
post Aug 15 2019, 12:28 AM
Post#11



Posts: 27
Joined: 9-August 19



Hi June7

I think I did not fully explained properly what I want , My sincere apologies.

The queries calculate all the rows for the CSN and returns a DAYS into each and every SERIAL Number.

However , I need only 1 results that has the lowest value and that is 7 results having 36 days.
Those more than 7 days , I need not want queries show also including the 6 results( 36 days ).

So all in all , Queries needs only to show me 1 result even though there are 390 items / row.
From the 390 item , I need queries to show only 1 result.

Please see the second pics.

Once again , sorry for not explaining fully.

Thank You.
Best Regards,
S. Conner



Attached File(s)
Attached File  PIC2.PNG ( 266.38K )Number of downloads: 3
 
Go to the top of the page
 
Steven Conner
post Aug 15 2019, 01:26 AM
Post#12



Posts: 27
Joined: 9-August 19



Hi All

I got the results as what i wanted.

A great thanks to all for your input.

I managed to use some June7 and Joe input combined to get the results , especially Joe for the SQL and June7 for the Min / Max

I type 1 from the DESIGN TAB , RETURN : 1 and it gives me exactly 1 result out of the 390 items.

This is most helpful .

Once again Thanks to all for your help.

Attached is final pictures of the solution.

Best Regards,
S Conner
Attached File(s)
Attached File  Pic3.PNG ( 83.33K )Number of downloads: 4
 
Go to the top of the page
 
RJD
post Aug 15 2019, 09:34 AM
Post#13


UtterAccess VIP
Posts: 10,028
Joined: 25-October 10
From: Gulf South USA


Ah. Now I understand your requirement. Glad you got that resolved.

June7 and I were happy to assist. And Jack had mentioned Top 1 before.

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st September 2019 - 06:09 AM