Choose 2nd group of data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to get the data for the second facility in the
list. I want to be able to get the rest of the data
associated with this record. For example I want to
ignore Denver 80266, and Saint Cloud 56387, and get the
data from the first Minneapolis 55401. Each group is
identified by the unique number that starts with
4099830...

Any ideas?
TIA
Mike

Facility FacName Oper Planetcode ScanDT
80266 DENVER (CO) P&DC 271 4099830003163
2/7/2004 9:04:38 PM
80266 DENVER (CO) P&DC 271 4099830003163
2/7/2004 9:04:38 PM
55401 MINNEAPOLIS (MN) P&DC 893 4099830003163
2/9/2004 4:03:59 PM
55401 MINNEAPOLIS (MN) P&DC 893 4099830003163
2/9/2004 4:03:59 PM
56387 SAINT CLOUD (MN) P&DF 918 4099830003163
2/10/2004 12:09:06 AM
56387 SAINT CLOUD (MN) P&DF 918 4099830003163
2/10/2004 12:09:06 AM
56387 SAINT CLOUD (MN) P&DF 919 4099830003163
2/10/2004 3:23:06 AM
56387 SAINT CLOUD (MN) P&DF 919 4099830003163
2/10/2004 3:23:06 AM
 
Hi,


Clearly, we need some sort of

SELECT DISTINCT Facility FROM tableName


but after that, what data determine the "ordering" in that list? You surely
know that data, in a table, may move, change of position, in relation with
other records, so we cannot relay on their actual "position" in some list.



Hoping it may help,
Vanderghast, Access MVP
 
Right, I understand that the data will move. The
ordering is by the Planetcode ID, Then by the ScanDT
which is the Date & Time. What I'm trying to do is get
the elapsed time from the first data record at Denver to
the next destination wherever it happens to be. Simple
enough to do if I can sonehow select the next
destination. And sometimes it even goes to a second
destination. The planetcode identifies a single
mailpiece moving through the distribution network. I
want to see how long it takes each mail piece to get to
it's first destination.
Thanks
Mike
-----Original Message-----
Hi,


Clearly, we need some sort of

SELECT DISTINCT Facility FROM tableName


but after that, what data determine the "ordering" in that list? You surely
know that data, in a table, may move, change of position, in relation with
other records, so we cannot relay on their
Click to expand...
actual "position" in some list.
 
Hi,


So, for a given PlanetCodeID, I have to find the "next" record (the one
immediately following the date_time):


SELECT a.PlanetCodeID, MIN(a.facility), a.ScanDT, MIN(b.facility), b.ScanDT
FROM ( myTable As a INNER JON myTable As b
ON a.PlanetCodeID=b.PlanetCodeID AND b.ScanDT > a.ScanDT
) INNER JOIN myTable As c
ON a.PlanetCodeID=c.PlanetCodeID and c.ScanDT > a.ScanDT
GROUP BY a.PlanetCodeID, a.ScanDT, b.ScanDT
HAVING b.ScanDT=MIN(c.ScanDT)



Note that I assume the Date and Time is UTC, or GTM, or zulu, so I don't
have to take into account the time zone offset, neither daylight changes.
Since any SELECT expression should be based on a GROUP, or on an aggregate,
use MIN( ) to retrieve any extra info, like I did for the facility ID.


The query may become prohibitive on large tables. In this case, we may
investigate the use of correlated sub-queries, or restrict myTable to a
saved query like: SELECT * FROM myTable WHERE PlantetCodeID= xxx ;


Hoping it may help,
Vanderghast, Access MVP
 
Yes, it did help. Although I don't completely understand
how it all works. I was able work with it a little. I
excluded the starting facility with <>"80266". Then I
was able to get the minimum value using another query.

Is there someway this can be done in the Query grid?
Probably using cascading queries? So I can get a better
understanding of what you did.
Thanks Much
Mike
 
Hi,


You can use the following, but it may be a little bit slower:


SELECT a.PlanetCodeID, MIN(a.facility), a.ScanDT, MIN(b.facility), b.ScanDT
FROM ( myTable As a INNER JON myTable As b
ON a.PlanetCodeID=b.PlanetCodeID
) INNER JOIN myTable As c
ON a.PlanetCodeID=c.PlanetCodeID

WHERE b.ScanDT > a.ScanDT AND c.ScanDT > a.ScanDT

GROUP BY a.PlanetCodeID, a.ScanDT, b.ScanDT

HAVING b.ScanDT=MIN(c.ScanDT)



Clearly, b and c have their records limited such that ScanDT > a.ScanDT.

Since we only group by on a.ScanDT and b.ScanDT, then c.ScanDT is free and
take all the possible values. Now, taking the min values of c.ScanDT would
simply return the first ScanDT > a.ScanDT. The idea is thus to remove, from
those groups, those where b.ScanDT is not equal to that MIN value (the min
still occur only on those >a.ScanDT).




Hoping it may help,
Vanderghast, Access MVP
 
Thank your for all your help. I will study this until I
do understand it!

Thanks Greatly
Mike
 
Back
Top