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
> Sort Not Working On Make Table Query, Access 2010    
 
   
techexpressinc
post Mar 6 2018, 08:43 AM
Post#1



Posts: 394
Joined: 6-October 08
From: indiana, usa


I do not understand why. Is there another parameter I need to add to the SQL code to make it work. I have Order by "ComplianceRepCompletedItem" and that is not the order in the output table.
It is the order in the query feed to the query making the table, and list in query making the table.
Here is the SQL code on the make table query:
QUOTE
SELECT Q_NDT_DAILY_REPORT_PREMT.ComplianceRepCompletedItem, Q_NDT_DAILY_REPORT_PREMT.WORKTYPE3, Q_NDT_DAILY_REPORT_PREMT.WorkType, Q_NDT_DAILY_REPORT_PREMT.[Testing Elected], Q_NDT_DAILY_REPORT_PREMT.PlanNbrCurrentYR, Q_NDT_DAILY_REPORT_PREMT.SolicitationMailed, Q_NDT_DAILY_REPORT_PREMT.DateMailedInvited, Q_NDT_DAILY_REPORT_PREMT.ContactEmail, Q_NDT_DAILY_REPORT_PREMT.CompFirstToSuspense, Q_NDT_DAILY_REPORT_PREMT.CompOutSuspense, Q_NDT_DAILY_REPORT_PREMT.CompSecondToSuspenseDate, Q_NDT_DAILY_REPORT_PREMT.CompSecondOutOfSuspense, Q_NDT_DAILY_REPORT_PREMT.CompThirdToSuspenseDate, Q_NDT_DAILY_REPORT_PREMT.CompThirdOutOfSuspense, Q_NDT_DAILY_REPORT_PREMT.CompFourthToSuspenseDate, Q_NDT_DAILY_REPORT_PREMT.CompFourthOutOfSuspense, Q_NDT_DAILY_REPORT_PREMT.CompFifthToSuspenseDate, Q_NDT_DAILY_REPORT_PREMT.CompFifthOutofSuspense, Q_NDT_DAILY_REPORT_PREMT.[Date Questionnaire Received], Q_NDT_DAILY_REPORT_PREMT.[Date Census Received], Q_NDT_DAILY_REPORT_PREMT.[InSuspense-1], Q_NDT_DAILY_REPORT_PREMT.[InSuspense-2], Q_NDT_DAILY_REPORT_PREMT.[InSuspense-3], Q_NDT_DAILY_REPORT_PREMT.[InSuspense-4], Q_NDT_DAILY_REPORT_PREMT.[InSuspense-5], Q_NDT_DAILY_REPORT_PREMT.InSuspense, Q_NDT_DAILY_REPORT_PREMT.BillingType, Q_NDT_DAILY_REPORT_PREMT.DateToAudit, Q_NDT_DAILY_REPORT_PREMT.DateFromAudit INTO T_NDT_DAILY_REPORT_ALL
FROM Q_NDT_DAILY_REPORT_PREMT
WHERE (((Q_NDT_DAILY_REPORT_PREMT.DateMailedInvited) Is Null) AND ((Q_NDT_DAILY_REPORT_PREMT.[Date Questionnaire Received]) Is Not Null) AND ((Q_NDT_DAILY_REPORT_PREMT.[Date Census Received]) Is Not Null))
ORDER BY Q_NDT_DAILY_REPORT_PREMT.ComplianceRepCompletedItem;


Any good pointers will be great I have lost 4 hours on this and am baffled.

Thank you
Russ
Go to the top of the page
 
nuclear_nick
post Mar 6 2018, 08:48 AM
Post#2



Posts: 1,659
Joined: 5-February 06
From: Ohio, USA


I hardly ever, if ever, depend on the table to do any sorting for me. That's the query's job, or the report's job, because at that point it doesn't matter how it went in, just how it comes out.

So this being part of a 'make-table' scenario... save yourself some headache and speed, and don't try to order things going INTO a table.

That is, if I understand things correctly.
Go to the top of the page
 
GroverParkGeorge
post Mar 6 2018, 08:50 AM
Post#3


UA Admin
Posts: 34,074
Joined: 20-June 02
From: Newcastle, WA


I concur with Nick's comments and recommendation.

Tables are inherently not ordered. Or to be it another way, there is no inherent sort order in tables in a relational database.

In order to apply a particular sort, you do that with a query which has an Order By clause.
Go to the top of the page
 
techexpressinc
post Mar 6 2018, 08:52 AM
Post#4



Posts: 394
Joined: 6-October 08
From: indiana, usa


So you are thinking putting them in the table in order is hopeless.

And when I pull the rows from the table put a sort parameter.

QUOTE
Set rs = CurrentDb.OpenRecordset("T_NDT_DAILY_REPORT_ALL")
Go to the top of the page
 
GroverParkGeorge
post Mar 6 2018, 09:00 AM
Post#5


UA Admin
Posts: 34,074
Joined: 20-June 02
From: Newcastle, WA


Not hopeless, simply useless.

Yes, open the recordset with the sort order you want.

Set rs = CurrentDb.OpenRecordset(" SELECT * FROM T_NDT_DAILY_REPORT_ALL ORDER BY T_NDT_DAILY_REPORT_ALL.ComplianceRepCompletedItem")

Or you could create a query based on T_NDT_DAILY_REPORT_ALL with the required sort order and open the recordset based on that sorted query, as well. It's up to you which way seems easier to maintain.
This post has been edited by GroverParkGeorge: Mar 6 2018, 09:04 AM
Go to the top of the page
 
techexpressinc
post Mar 6 2018, 09:14 AM
Post#6



Posts: 394
Joined: 6-October 08
From: indiana, usa


The process is working now.
Thank you very much.
Russ
Go to the top of the page
 
GroverParkGeorge
post Mar 6 2018, 09:18 AM
Post#7


UA Admin
Posts: 34,074
Joined: 20-June 02
From: Newcastle, WA


Continued success with your project.
Go to the top of the page
 
nuclear_nick
post Mar 6 2018, 09:50 AM
Post#8



Posts: 1,659
Joined: 5-February 06
From: Ohio, USA


You're welcome, Russ. Good luck!

Thanks for the backing, GPG!

uarulez2.gif

smile.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2018 - 02:25 PM