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
> Crazy Sequencing Query Problem, Access 2016    
 
   
andywal6
post Apr 1 2020, 08:32 AM
Post#1



Posts: 179
Joined: 11-January 08



Hi All

I am constructing a Route manager for delivery drivers.
It plans the route to a sequence set in a form that shows all deliveries for a driver for the day.

My first query selects all the destinations for that day and the driver, and this works fine, all in sequence as per 'tblOrderDetails.DeliverySequence' .
The first query is named 'Driver3QueryAddresses'

CODE
SELECT tblOrderDetails.IAddressPC, DeliveryDrivers.DriverName, tblOrderDetails.DeliveryDate, tblOrderDetails.DeliverySequence
FROM DeliveryDrivers INNER JOIN tblOrderDetails ON DeliveryDrivers.ID = tblOrderDetails.Driver
WHERE (((DeliveryDrivers.DriverName)=[Forms]![DayDeliveryTeeCards]![Driver3]) AND ((tblOrderDetails.DeliveryDate)=[Forms]![DayDeliveryTeeCards]![Date]))
ORDER BY tblOrderDetails.DeliverySequence;


My second query looks for the distinct Postcodes which are fed to a Mapping App to create a map with the route shown, and feed them into a satnav system.

This step is necessary as there may be more than one order at a single address postcode in the first query, and we don't want multiple map points at the same place.

CODE
SELECT DISTINCT Driver3QueryAddresses.IAddressPC
FROM Driver3QueryAddresses;


My problem is the output from the second query is not in the same sequence as in the first query.

First query sequence is: 1,2,3,4,5,6
Output From second is: 6,4,5,2,3,1

Any thoughts, anyone.
Thank you for reading.

Andy.......

Go to the top of the page
 
Daniel_Stokley
post Apr 1 2020, 09:14 AM
Post#2



Posts: 340
Joined: 22-December 14
From: Grand Junction, CO, USA


Hello Andy,

First thing I noticed is that unlike your first query, the second query does not have an ORDER BY clause. Maybe the following will help:

CODE
SELECT DISTINCT IAddressPC, DeliverySequence
FROM Driver3QueryAddresses Order by DeliverySequence;



Go to the top of the page
 
andywal6
post Apr 1 2020, 09:25 AM
Post#3



Posts: 179
Joined: 11-January 08



Hi Daniel
Unfortunately, because the DeliverySequence field has a different value in each record in your example,
The DISTINCT cannot work on the IAddressPC field and will return all records.
Many thanks

Stay Safe.

Andy...........
Go to the top of the page
 
Daniel_Stokley
post Apr 1 2020, 09:45 AM
Post#4



Posts: 340
Joined: 22-December 14
From: Grand Junction, CO, USA


OK. Maybe someone else here at UA will be able to help you. uarulez2.gif

Stay safe smile.gif


Go to the top of the page
 
JHolm
post Apr 1 2020, 10:43 AM
Post#5



Posts: 147
Joined: 7-July 15
From: BC Canada


Untested, but does something like:

CODE
SELECT IAddressPC, Min(DeliverySequence) AS DeliverySeq
FROM Driver3QueryAddresses
GROUP BY IAddressPC
ORDER BY Min(DeliverySequence);


work for you?

Jeff
Go to the top of the page
 
andywal6
post Apr 1 2020, 11:30 AM
Post#6



Posts: 179
Joined: 11-January 08



Thank you Jeff
That works perfectly
I must admit, I would have gone round in circles until I had pulled all my hair out before coming up with that solution.
Many thanks

Keep Safe (We need guys like you)

Andy.............
Go to the top of the page
 
JHolm
post Apr 1 2020, 11:59 AM
Post#7



Posts: 147
Joined: 7-July 15
From: BC Canada


Happy to help.

I get to work from home these days. Aside from the occasional grocery shopping excursion, we're staying home.

Hope you're able to keep safe too.

Cheers,

Jeff
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 07:28 PM