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
> Custom Sort, Access 2013    
 
   
Hotrod1952
post Nov 21 2019, 09:11 AM
Post#1



Posts: 12
Joined: 21-November 19



Hi - I have a query with 9 columns What I need is the Query to sort By Date Ascending Then if a duplicate Asset number appears on the list I need that grouped directly below the earliest Date.
Example

Column 1 Column 2 Column 3-9
DATE AssetNo XXX
1-1-97 0704
1-2-97 5513
2-1-97 5513
1-8-97 0042
1-9-97 2213
4-1-19 2213
2-1-97 8619

I am thinking too hard here?
This post has been edited by Hotrod1952: Nov 21 2019, 09:12 AM
Go to the top of the page
 
Jeff B.
post Nov 21 2019, 09:20 AM
Post#2


UtterAccess VIP
Posts: 10,489
Joined: 30-April 10
From: Pacific NorthWet


Are you saying you want to group by date (sorted Ascending) and then group by [AssetNo] (?ascending)?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Hotrod1952
post Nov 21 2019, 09:46 AM
Post#3



Posts: 12
Joined: 21-November 19



Date 1st then AssetNo Yes - Unless an AssetNo has 2 records - then the 1st date an AssetNo appears it sorts the next record of that same asset with the first record of that asset. I am sorting work orders - oldest first. If an asset appears on the query results with two work orders then I want them grouped together so I can schedule the work on the same machine at one time. My work order list can get upwards of 200 work orders long. Query is for a Combo box drop down.
This post has been edited by Hotrod1952: Nov 21 2019, 09:51 AM
Go to the top of the page
 
RJD
post Nov 21 2019, 11:24 AM
Post#4


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


Hi ... and Welcome to UtterAccess!: Not really too complicated, I think. I assumed that there would be only one duplicate (it may not matter, but that was my assumption). And came up with this ...

SELECT AssetDate, AssetNo
FROM tblMyRecords
ORDER BY CDate(DMin("AssetDate","tblMyRecords","AssetNo='" & [AssetNo] & "'")), AssetDate, AssetNo;

Of course, change the table name to your own. And I changed the date field name, since Date is a reserved word and can cause problems in some cases.

See if this is close to your requirements.

See the attached demo if needed.

HTH
Joe
Attached File(s)
Attached File  CustomSort.zip ( 18.29K )Number of downloads: 4
 

--------------------
"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
 
Hotrod1952
post Nov 21 2019, 12:37 PM
Post#5



Posts: 12
Joined: 21-November 19



Closer but there are more than 1 duplicate.
Go to the top of the page
 
RJD
post Nov 21 2019, 12:54 PM
Post#6


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


Okay. You say "closer" but could you describe what is not correct in the output?

And perhaps you could post a db with a table of data to work with (zipped)? Or perhaps add records to the demo I posted to show a more realistic situation, along with what the results should look like?

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
 
Hotrod1952
post Nov 21 2019, 02:15 PM
Post#7



Posts: 12
Joined: 21-November 19



Yes Sorry for that reply - I was trying to tweek what you sent. Attached is the modified database you sent with my data inserted and fields matching the ones I have in use.

Attached File  CustomSort.zip ( 27.2K )Number of downloads: 4


And it is working! I must have typed something wrong when I typed in what you sent the first time. Thank You!
This post has been edited by Hotrod1952: Nov 21 2019, 02:15 PM
Go to the top of the page
 
RJD
post Nov 21 2019, 02:32 PM
Post#8


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


Hi: Good, glad you got that sorted. However, in reviewing the results of what you posted, I think you would do better to swap sorts 2 and 3. This will group the AssetIDs together better when there are duplicate dates with different AssetIDs. See the revised code below, and the revised db attached.

SELECT DateRequired AS Expr1, AssetID AS Expr2
FROM tblMyRecords
ORDER BY CDate(DMin("DateRequired","tblMyRecords","AssetID='" & [AssetID] & "'")), AssetID, DateRequired;

Not sure why you aliased the two SELECT fields, but I left it as you posted.

HTH
Joe
Attached File(s)
Attached File  CustomSort_Rev1.zip ( 22.67K )Number of downloads: 6
 

--------------------
"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    9th July 2020 - 02:11 AM