Help with complicated query?

  • Thread starter Thread starter fktx
  • Start date Start date
F

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
 
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;
 
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.
 
fktx said:
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?
 
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.
 
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.
 
fktx said:
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;
 
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

Bob Barrows said:
fktx said:
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"
 
I tried out the queries, and they seem to work great. Thanks again.

Bob Barrows said:
fktx said:
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"
 
Back
Top