Originally Posted by
michael_m_jones
I tried this but it doesn't change the results of the query. I need the query to look at the file name then at the date then at the time and pick the first record of each hour per day per file name. What this query does not capture as it currently is, is if there are two different filenames with the same date and records within the same hour the query needs to select 2 records one for the first file name and one for the second even though they have the same date and are within the same hour for the record. Does that make sense?
Please look at my example data here and see if that makes sense.
ID FileName Date Time
1 B012345 6/1/16 11:11
2 B012345 6/1/16 11:16
3 B012345 6/1/16 11:23
4 B012345 6/1/16 15:16
5 B012345 6/1/16 15:22
6 A012345 6/1/16 11:16
7 A012345 6/2/16 11:16
8 A012345 6/2/16 11:22
What I want my query to return is the first call in each hour for a given date for a given file name, See Below.
ID FileName Date Time
1 B012345 6/1/16 11:11
4 B012345 6/1/16 15:16
6 A012345 6/1/16 11:16
7 A012345 6/2/16 11:16
After playing around with the code I believe that I have something that works to return the first call every hour for a given date and a given file name and it was as you suggested as simple as "add FileName as the first column in the first query and GroubBy on it." Now however, I am running into issues with the second query.
My first Query returns 828 records which seems plausible
Code:
SELECT MSO_RecorderLogs_tbl.FN, MSOCallSub_GeneralInfo_tbl.D, DatePart("h",[DT]) AS vHour, Min(MSOCallSub_GeneralInfo_tbl.DT) AS MinOfDTFROM MSO_RecorderLogs_tbl INNER JOIN MSOCallSub_GeneralInfo_tbl ON MSO_RecorderLogs_tbl.MLR_ID = MSOCallSub_GeneralInfo_tbl.MRL_ID
GROUP BY MSO_RecorderLogs_tbl.FN, MSOCallSub_GeneralInfo_tbl.D, DatePart("h",[DT]);
My second Query returns 1138 records which is not possible as the second query should be limited by the number of records of the first query
Code:
SELECT MSOCallSub_GeneralInfo_tbl.ID, MSOCallSub_GeneralInfo_tbl.D, MSOCallSub_GeneralInfo_tbl.DT, MSO_RecorderLogs_tbl.FN
FROM MSO_RecorderLogs_tbl INNER JOIN (FIRSTCALLPERHOUR INNER JOIN MSOCallSub_GeneralInfo_tbl ON (FIRSTCALLPERHOUR.MinOfDT = MSOCallSub_GeneralInfo_tbl.DT) AND (FIRSTCALLPERHOUR.D = MSOCallSub_GeneralInfo_tbl.D)) ON MSO_RecorderLogs_tbl.MLR_ID = MSOCallSub_GeneralInfo_tbl.MRL_ID;
As always any help would be much appreciated.