I need to add a unique ID field to my "SELECT DISTINCT" query but then the results change

[michael_m_jones]

I'm a novice and I am self taught and as a result, I'm probably doing everything wrong.

The long and short is that I made a query to find unique records based on 3 fields: [FN] (File Name), [D] Date, and [DT] (Time)
However, I only want the first record of each hour so I added the [D] and [DT] together and format them so that they only have hours and no minutes

"yyyy/m/dd hh"

I then use SELECT DISTINCT to get only the first record in each hour.

I also have a where clause which I use to further refine the query based on checkboxes being checked, then I order it all based on the same 3 pieces of data [FN] (File Name), [D] Date, and [DT] (Time).



The query is great it does just what I want it to. The issue that I have is that I can't then use the query to pull the related records from the tables because there is no unique ID since the results of a SELECT DISTINCT query are only unique to the hour field and not the minute field. Ideally, I'd like to include a field called MSOCallSub_GeneralInfo_tbl.MRL_ID and one called MSOCallSub_GeneralInfo_tbl.ID and in the results of the query. Any advice that you could give would be appreciated. I'm sure that I've done an awful job of explaining this an have left out many critical pieces of data, so please ask as many questions as you need to. And Below if the full Query in SQL format.

SELECT DISTINCT MSO_RecorderLogs_tbl.FN, CStr(Format([MSOCallSub_GeneralInfo_tbl]![D]+[MSOCallSub_GeneralInfo_tbl]![DT],"yyyy/m/dd hh")) AS First_Time_Hours_and_Date
FROM MSO_RecorderLogs_tbl INNER JOIN MSOCallSub_GeneralInfo_tbl ON MSO_RecorderLogs_tbl.MLR_ID = MSOCallSub_GeneralInfo_tbl.MRL_ID
WHERE (((MSOCallSub_GeneralInfo_tbl.F)<0)) OR (((MSOCallSub_GeneralInfo_tbl.C)<0)) OR (((MSOCallSub_GeneralInfo_tbl.S)<0)) OR (((MSOCallSub_GeneralInfo_tbl.B)<0)) OR (((MSOCallSub_GeneralInfo_tbl.Y)<0)) OR (((MSOCallSub_GeneralInfo_tbl.MF)<0) AND ((MSO_RecorderLogs_tbl.SD) Between #1/1/2016# And #12/31/2016#))
ORDER BY MSO_RecorderLogs_tbl.FN DESC , CStr(Format([MSOCallSub_GeneralInfo_tbl]![D]+[MSOCallSub_GeneralInfo_tbl]![DT],"yyyy/m/dd hh")) DESC;

[ranman256]

CreAte an autonumber field for your table. THIS is the unique ID.
if you want YOUR version with the date, you can add it via a query and assemble it as a virtual field.
this would be an extra field for fun, not needed for the unique id.

[CJ_London]

I don't quite understand what you are trying to do - perhaps some example data and what the expected outcome should be will make it clearer. I should point out that it is normal practice to store date and time as one field.

One thing I don't understand is if the time part includes minutes (and seconds?) and you are trying to get a the first record, but are not using the minutes part of the time, how do you decide which of the potentially 60 (minutes) or 3600 (seconds) records in the first hour is the first one?

date time fields are a decimal number. The date part is before the decimal point (6th Dec is 42710, the previous day is 42709 etc) the time part is the number of seconds so far today divided by the number of seconds in the day (86400) - so 9am is 0.375, 1pm is 0.541 - actually expressed to 10 decimal places.

[michael_m_jones]

I've got a unique ID field in the table but I can't include that field in the query because it changes the results of the query.

[Bulzie]

Not sure if this is what you need but I would leave the time with minutes so you can get the first one for each hour. To test I created table MSO_RecorderLogs_tbl with fields (ID, D, DT, FN). Create Query1 or paste the Select code below into the Query Designer to see the visual. This should give you the first record for each hour. Then you link the Date and Time from Query1 back to the table to get the ID and FN.

Query1:
SELECT MSO_RecorderLogs_tbl.D, DatePart("h",[DT]) AS vHour, Min(MSO_RecorderLogs_tbl.DT) AS MinOfDT
FROM MSO_RecorderLogs_tbl
GROUP BY MSO_RecorderLogs_tbl.D, DatePart("h",[DT]);

Query2
SELECT MSO_RecorderLogs_tbl.ID, MSO_RecorderLogs_tbl.D, MSO_RecorderLogs_tbl.DT, MSO_RecorderLogs_tbl.FN
FROM Query1 INNER JOIN MSO_RecorderLogs_tbl ON (Query1.D = MSO_RecorderLogs_tbl.D) AND (Query1.MinOfDT = MSO_RecorderLogs_tbl.DT);

[michael_m_jones]

I don't quite understand what you are trying to do - perhaps some example data and what the expected outcome should be will make it clearer. I should point out that it is normal practice to store date and time as one field.

One thing I don't understand is if the time part includes minutes (and seconds?) and you are trying to get a the first record, but are not using the minutes part of the time, how do you decide which of the potentially 60 (minutes) or 3600 (seconds) records in the first hour is the first one?

date time fields are a decimal number. The date part is before the decimal point (6th Dec is 42710, the previous day is 42709 etc) the time part is the number of seconds so far today divided by the number of seconds in the day (86400) - so 9am is 0.375, 1pm is 0.541 - actually expressed to 10 decimal places.

Thanks for replying to my post. You bring up several good points and I will post an example of what I am trying to do and perhaps you will be better able to understand

I know that date and time fields are not typically separate but there was a request from the user to make them that way and I can't change it now.

Example Data:

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

You asked how I am getting the first call in each hour if I am removing the minutes. The answer is that I am not, I realized that after reading your post. What I am doing is pulling one record (randomly) from each hour which is not what I want to do.

The core of what I need to do is select the first record of each hour for a given date for a given file name. It sounds so simple when I say that but I just can't figure out how to make a query that does that.

[Bulzie]

As long as the time values are unique, my queries above should work. Basically in first query you want to add Date, vHour: Datepart("h", [DT]) and DT(full time value). Then you GroupBy on Date and vHour and "Min" on DT which should give you the first record for each hour. Then in 2nd query, link the 1st query to the table on Date and DT to get the other fields.

[michael_m_jones]

Not sure if this is what you need but I would leave the time with minutes so you can get the first one for each hour. To test I created table MSO_RecorderLogs_tbl with fields (ID, D, DT, FN). Create Query1 or paste the Select code below into the Query Designer to see the visual. This should give you the first record for each hour. Then you link the Date and Time from Query1 back to the table to get the ID and FN.

Query1:
SELECT MSO_RecorderLogs_tbl.D, DatePart("h",[DT]) AS vHour, Min(MSO_RecorderLogs_tbl.DT) AS MinOfDT
FROM MSO_RecorderLogs_tbl
GROUP BY MSO_RecorderLogs_tbl.D, DatePart("h",[DT]);

Query2
SELECT MSO_RecorderLogs_tbl.ID, MSO_RecorderLogs_tbl.D, MSO_RecorderLogs_tbl.DT, MSO_RecorderLogs_tbl.FN
FROM Query1 INNER JOIN MSO_RecorderLogs_tbl ON (Query1.D = MSO_RecorderLogs_tbl.D) AND (Query1.MinOfDT = MSO_RecorderLogs_tbl.DT);

This is great baring one issue I need the first record per hour per date per file name. I posted this example data on another post but here it is again. FYI, I did get all your queries to work with just a small bit of tweaking so thanks for that.

Example Data:

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

[Bulzie]

Then just add FileName as the first column in the first query and GroubBy on it. Then in 2nd query link that back to the table along with the date and time.

[michael_m_jones]

Then just add FileName as the first column in the first query and GroubBy on it. Then in 2nd query link that back to the table along with the date and time.

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

[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

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

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.

[Bulzie]

Actually looks like you want to group by Date first, then Filename so in my example it needs to be grouped with Date first, then FileName, not the other way. So would be in this order:
Date, FN, vHour: DatePart("h",[DT]), DT ("GroupBy" on first 3, "Min" on DT)

Query1:
SELECT MSO_RecorderLogs_tbl.D, MSO_RecorderLogs_tbl.FN, DatePart("h",[DT]) AS vHour, Min(MSO_RecorderLogs_tbl.DT) AS MinOfDT
FROM MSO_RecorderLogs_tbl
GROUP BY MSO_RecorderLogs_tbl.D, MSO_RecorderLogs_tbl.FN, DatePart("h",[DT]);

QUERY2:
SELECT MSO_RecorderLogs_tbl.ID, MSO_RecorderLogs_tbl.D, MSO_RecorderLogs_tbl.DT, MSO_RecorderLogs_tbl.FN
FROM Query1 INNER JOIN MSO_RecorderLogs_tbl ON (Query1.FN = MSO_RecorderLogs_tbl.FN) AND (Query1.MinOfDT = MSO_RecorderLogs_tbl.DT) AND (Query1.D = MSO_RecorderLogs_tbl.D);

The 2nd query should have the same number of records as the first query, you are just linking to it to get the other data fields for those specific records.

And on your first query in your example above, why are you joining that other table?