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: 371
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

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
nuclear_nick
post Mar 6 2018, 08:48 AM
Post#2



Posts: 1,519
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.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
GroverParkGeorge
post Mar 6 2018, 08:50 AM
Post#3


UA Admin
Posts: 32,787
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: 371
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")

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
GroverParkGeorge
post Mar 6 2018, 09:00 AM
Post#5


UA Admin
Posts: 32,787
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: 371
Joined: 6-October 08
From: indiana, usa


The process is working now.
Thank you very much.
Russ

--------------------
Thanks,
Russ Neuman, Insurance designations = FLMI, ASC, ALMI
Certified MS-Office Specialist
Go to the top of the page
 
GroverParkGeorge
post Mar 6 2018, 09:18 AM
Post#7


UA Admin
Posts: 32,787
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,519
Joined: 5-February 06
From: Ohio, USA


You're welcome, Russ. Good luck!

Thanks for the backing, GPG!

uarulez2.gif

smile.gif

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 05:22 PM