Help with complicated query?

[fktx]

I am still new to Access and queries, but I understand the basics of SQL. I
have my table set up in the following format:

StationID Date_Time LocationID
42361 20070805 2
42361 20070807 2
42361 20070808 2
42361 20070809 3
42361 20070810 3
42362 20070105 4
42362 20070106 4

What I want to do is select the starting and ending dates for each
LocationID of each StationID so that I end up with something like this.

StationID LocationID StartDate EndDate
42361 2 20070805 20070808
42361 3 20070809 20070810
42362 4 20070105 20070106

If this were normal programming I would order the dates in ascending order
for each station, and then find where the difference between sequential
LocationIDs were not equal to zero. However, I can't seem to convert that
thought process into an SQL statement. Any help would be greatly appreciated.

Thanks,
FK

 

[KARL DEWEY]

Try this ---
SELECT fktx.StationID, fktx.LocationID, Min(fktx.Date_Time) AS
MinOfDate_Time, Max(fktx_1.Date_Time) AS MaxOfDate_Time
FROM fktx INNER JOIN fktx AS fktx_1 ON (fktx.LocationID = fktx_1.LocationID)
AND (fktx.StationID = fktx_1.StationID)
GROUP BY fktx.StationID, fktx.LocationID;

 

[fktx]

I forgot to mention that I already tried that exact query, but it didn't work
and that's when I came here. It doesn't work because the same LocationID can
occur in more than one group of dates for a single StationID. Let me show
you by changing my original table a little.

StationID Date_Time LocationID
42361 20070805 2
42361 20070807 2
42361 20070808 2
42361 20070809 3
42361 20070810 3
42361 20071202 2 <--- Added line
42361 20071203 2 <--- Added line
42362 20070105 4
42362 20070106 4

Thus, if I perform the query you suggested, I end up with the following:

StationID LocationID StartDate EndDate
42361 2 20070805 20071203
42361 3 20070809 20070810

However, the following results are what I am really after:

StationID LocationID StartDate EndDate
42361 2 20070805 20070808
42361 2 20071202 20071203
42361 3 20070809 20070810

Thus I am trying to find the starting and ending dates for each "group" of
LocationIDs for each StationID. In theory, I need start at the earliest date
for a specific stationID and mark it as the start date for a LocationID.
Then, step through the LocationIDs by date until there's a change to a
different LocationID and mark the previous date as the end date, as well as
mark a new start date for the new LocationID. There's got to be a way to
write this in SQL. Otherwise, I am going to have to begin learning Visual
Basic.

 

[Bob Barrows [MVP]]

I forgot to mention that I already tried that exact query, but it
didn't work
and that's when I came here. It doesn't work because the same
LocationID can occur in more than one group of dates for a single
StationID. Let me show
you by changing my original table a little.

StationID Date_Time LocationID
42361 20070805 2
42361 20070807 2
42361 20070808 2
42361 20070809 3
42361 20070810 3

Is this possible?

42361 20071203 3 <--- Added line

42361 20071202 2 <--- Added line
42361 20071203 2 <--- Added line
42362 20070105 4
42362 20070106 4

Or are we guaranteed to have at most one record per date?

 

[fktx]

No, it's not possible to have 2 different LocationIDs on the same date. We
can only occupy 1 location per date, so we are guaranteed to have at most one
record per date. Hope this helps. Thanks in advance.

 

[Bob Barrows [MVP]]

So the idea is to order them by stationid and date_time, and determine
groups by identifying records where the locationid changes. Do I have it?
Let me go play with this.

 

[Bob Barrows [MVP]]

No, it's not possible to have 2 different LocationIDs on the same
date. We can only occupy 1 location per date, so we are guaranteed to
have at most one record per date. Hope this helps. Thanks in advance.

OK, try this:

1. Create a saved query called NextLocation with this sql:
SELECT s1.StationID, s1.Date_Time, s1.LocationID,
(SELECT Top 1 LocationID
FROM Stations
where stationid=s1.stationid and date_time > s1.date_time
order by date_time) AS NextLocationID
FROM Stations AS s1;

2. Create a saved query called LocationGroupBreaks with this sql:
SELECT n.StationID, n.Date_Time, n.LocationID
FROM NextLocation AS n
WHERE (((n.LocationID)<>Nz([NextLocationID],0)));

3. Create a saved query called LocationGroups with this sql:
SELECT s.StationID, s.Date_Time, s.LocationID,
(Select min(l.Date_Time)
From LocationGroupBreaks as l Where l.StationID = s.StationID
and l.LocationId = s.LocationId
and l.Date_time >=s.Date_time) AS LocationGroupEndDate
FROM Stations AS s;

4. This sql should give you your desired results (at least it does when I
test it with your sample data):
SELECT g.StationID, g.LocationID,
Min(g.Date_Time) AS StartDate,
g.LocationGroupEndDate AS EndDate
FROM LocationGroups AS g
GROUP BY g.StationID, g.LocationID, g.LocationGroupEndDate
ORDER BY g.StationID, g.LocationGroupEndDate;

 

[fktx]

Thank you for all your help. This looks like it might work, but
unfortunately, I no longer have access to the database files. I'll test the
queries tomorrow morning and let you know then.

FK

No, it's not possible to have 2 different LocationIDs on the same
date. We can only occupy 1 location per date, so we are guaranteed to
have at most one record per date. Hope this helps. Thanks in advance.

OK, try this:

1. Create a saved query called NextLocation with this sql:
SELECT s1.StationID, s1.Date_Time, s1.LocationID,
(SELECT Top 1 LocationID
FROM Stations
where stationid=s1.stationid and date_time > s1.date_time
order by date_time) AS NextLocationID
FROM Stations AS s1;

2. Create a saved query called LocationGroupBreaks with this sql:
SELECT n.StationID, n.Date_Time, n.LocationID
FROM NextLocation AS n
WHERE (((n.LocationID)<>Nz([NextLocationID],0)));

3. Create a saved query called LocationGroups with this sql:
SELECT s.StationID, s.Date_Time, s.LocationID,
(Select min(l.Date_Time)
From LocationGroupBreaks as l Where l.StationID = s.StationID
and l.LocationId = s.LocationId
and l.Date_time >=s.Date_time) AS LocationGroupEndDate
FROM Stations AS s;

4. This sql should give you your desired results (at least it does when I
test it with your sample data):
SELECT g.StationID, g.LocationID,
Min(g.Date_Time) AS StartDate,
g.LocationGroupEndDate AS EndDate
FROM LocationGroups AS g
GROUP BY g.StationID, g.LocationID, g.LocationGroupEndDate
ORDER BY g.StationID, g.LocationGroupEndDate;


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

 

[fktx]

I tried out the queries, and they seem to work great. Thanks again.

No, it's not possible to have 2 different LocationIDs on the same
date. We can only occupy 1 location per date, so we are guaranteed to
have at most one record per date. Hope this helps. Thanks in advance.

OK, try this:

1. Create a saved query called NextLocation with this sql:
SELECT s1.StationID, s1.Date_Time, s1.LocationID,
(SELECT Top 1 LocationID
FROM Stations
where stationid=s1.stationid and date_time > s1.date_time
order by date_time) AS NextLocationID
FROM Stations AS s1;

2. Create a saved query called LocationGroupBreaks with this sql:
SELECT n.StationID, n.Date_Time, n.LocationID
FROM NextLocation AS n
WHERE (((n.LocationID)<>Nz([NextLocationID],0)));

3. Create a saved query called LocationGroups with this sql:
SELECT s.StationID, s.Date_Time, s.LocationID,
(Select min(l.Date_Time)
From LocationGroupBreaks as l Where l.StationID = s.StationID
and l.LocationId = s.LocationId
and l.Date_time >=s.Date_time) AS LocationGroupEndDate
FROM Stations AS s;

4. This sql should give you your desired results (at least it does when I
test it with your sample data):
SELECT g.StationID, g.LocationID,
Min(g.Date_Time) AS StartDate,
g.LocationGroupEndDate AS EndDate
FROM LocationGroups AS g
GROUP BY g.StationID, g.LocationID, g.LocationGroupEndDate
ORDER BY g.StationID, g.LocationGroupEndDate;


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"