SELECT a specific DATE RANGE in Query

[taimysho0]

june, here is some test data from an excel sheet, contains 2 IDS. let me know if this is sufficient. thanks for the attempt, and next time ill be sure to add to the original thread, i had marked it as solved prior to the change in how we are now calculating the records so thats why i started another thread. thanks again!testdata00.txt

[June7]

Think I got something. This query will calculate a date value that can be used as the common value to group records.

SELECT AMR_AIS_Num, GAS_READ_DATE, Format(IIf(Format([GAS_READ_DATE],"hhnnss")="000000",[GAS_READ_DATE]-1,[GAS_READ_DATE]),"mm/dd/yyyy") AS GroupDate
FROM tblMonthly_Link;

Now use that query in the count query to determine which groups don't have 24 records.
SELECT AMR_AIS_Num, GroupDate
FROM Query1
GROUP BY AMR_AIS_Num, GroupDate;
HAVING Count(GroupDate)<24;

Can also use the constructed field for search/filter criteria.

[taimysho0]

WOW! works great June! i appreciate all the effort. My last question is, sometimes there is no hour interval data for a given day or several days. Currently, the query only looks through where there are at least one hour interval of data for a particular day. If there is no data for lets say 5/4 through 5/8, the query will skip it, even though 24 hours of interval data will be missing for those 4 days.

Is there a way for the query to know how many days are in a given month? and if it is missing a day in that particular month, it can pick up those missing intervals as well?? THANKS!

[taimysho0]

y the way what does the "nn" in "hhnnss" stand for? i would think it would be for minutes, but why "n"?

[June7]

'm' already means month so THEY decided to use 'n' for minutes.

Accounting for missing days would be complex query or use VBA. I will look at query idea this weekend.

[taimysho0]

k thanks june! I was thinking of maybe first creating a query that spits out the number of days for that particular month as rows (ex: "1" = 5/01/2012, "2" = 05/02/2012). (pic attached as an example, the function in the header should calculate the number of days in the current month).

from here, we can go back to the Query 1 in the posts above, then do a format of the Date column that shows the hourly intervals to only include the day (ex: display "3" for 5/3/2012"). Then, query this with the query that outputs the picture attached. Then, just see which days dont exist. From there, we can THEN do the count query which includes rows that have <24 hour intervals OR is null in the column of the query that matches up the days of the current month with the day of the formatted date column.





**the only problem is, i cant get the query to work right where it will display the day number of the current month as rows. When i try to do a "count", it just counts all the days as a whole number. for example, for may, instead of displaying 1,2,3,4,5... as rows, it just displays one row with "31" which is how many days are in May

[June7]

That sounds good. Not sure how you will get the day sequence for all days of month. That is like a query that assigns a row number by group. I have done that before and it requires a subquery that gets value from previous record. They can run very slow. https://www.accessforums.net/queries...ing-23908.html

Give it a try and let me know how it goes.

[taimysho0]

ok june, i tried doing this yesterday but am stuck on this part, maybe you can help.
1)i started off by creating a table with a "month" and "days" column. I listed the name of every month and under the days, listed out the days of that month in date format. I then queried this so that it will format the "days" column into a day number, so for example 5/2/2012 will just display a "5".



2) then, i grab the query that spits out this image below, and connect the two common columns ("month" and "day")



3) my sql is provided below, but the problem is its not working right, its displaying an "error" in the ID column for all the rows. The idea for this query below is that for every ID, it will show all the months and days i have data for. If im missing a day, it should display a null cell (because my qryMonthsandDays should have a day for every day of the month, if the table im querying does not have that particular day, it should display a null is the idea). The problem is i think im missing an ID column in my qryMonthsandDays table that i can pair up with the qryMonthlytoGLDS table, do you think i would need this?? i feel like this is close but not quite there. any help would be much appreciated.


here is my sql of the two queries in a join to find where there are nulls in the month and day of all the IDs im looking for:

SELECT qryMonthlyToGLDSLink.[AMR AIS #], qryMonthsandDays.Month, qryMonthsandDays.Days
FROM qryMonthsandDays LEFT JOIN qryMonthlyToGLDSLink ON (qryMonthsandDays.Days = qryMonthlyToGLDSLink.Day) AND (qryMonthsandDays.Month = qryMonthlyToGLDSLink.Month);

[June7]

Do you need to know if day is missing for the AMR_AIS_Num and month and year combination? If so, will need a column for AMR_AIS_Num and a full set of dates for each. Then build query that pulls the date part from each date/time, GROUP BY on AMR_AIS_NUM and date part. Join that query to the all dates table linking on the AMR_AIS_NUM and date fields. Maintaining the all dates table sounds like a headache but I don't see any other way to identify exactly which dates are missing.

If you don't need to know exact missing dates but just which months are missing days, like the query that identifies dates that don't have 24 hours, this will identify which AMR_AIS_NUM and mon/year are missing days:

Query1
SELECT tblMonthly_Link.AMR_AIS_NUM, Month([GAS_READ_DATE]) & "/" & Year([Gas_READ_DATE]) AS MOYR, Day((DateSerial(Year([GAS_READ_DATE]),Month([GAS_READ_DATE])+1,0))) AS MonthDays, CDate(Format(IIf(Format([GAS_READ_DATE],"hhnnss")="000000",[GAS_READ_DATE]-1,[GAS_READ_DATE]),"mm/dd/yyyy")) AS GroupDate
FROM tblMonthly_Link
GROUP BY tblMonthly_Link.AMR_AIS_NUM, Month([GAS_READ_DATE]) & "/" & Year([Gas_READ_DATE]), Day((DateSerial(Year([GAS_READ_DATE]),Month([GAS_READ_DATE])+1,0))), CDate(Format(IIf(Format([GAS_READ_DATE],"hhnnss")="000000",[GAS_READ_DATE]-1,[GAS_READ_DATE]),"mm/dd/yyyy"));

Query2
SELECT Query1.AMR_AIS_NUM, Query1.MOYR, Query1.MonthDays, Count(Query1.GroupDate) AS CountOfDays
FROM Query1
GROUP BY Query1.AMR_AIS_NUM, Query1.MOYR, Query1.MonthDays
HAVING (((Count(Query1.GroupDate))<[MonthDays]));

[taimysho0]

hello, yes i will need to know which days of any given month is missing (normally the end result should only display what is missing for the current month, and once we move onto the next month we wouldnt have to worry about whats missing in the previous month). therefore it looks like i have to go through the hard route of what you mentioned above. so just to understand you clearly, i have to add an ID column in my monthanddays query correct? and pretty much all the ids i have need to have its own set of months and dates correct?

[June7]

By ID do you mean the AMR_AIS_NUM? Yes.

Suggest a VBA procedure to populate the monthanddays table. It could be a 'temp' table that holds only records needed for the month being analyzed.

[taimysho0]

yes, the amr_ais_num. ok ill try vb although im not too good with it..not sure how i would set that up. but thanks for all the help anyways june

[June7]

The VBA could be a sub with arguments for month and year. The code would use the month value to calculate how many days are in the month. Then a loop structure would save records. The save can be to an editable recordset opened by the sub or an UPDATE direct to table. Something like:

Sub CreateRecs(intMonth As Integer, intYear As Integer)
    Dim intDays As Integer, i As Integer, strAMR As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    CurrentDb.Execute "DELETE FROM MonthAndDays"
    rs.Open "SELECT DISTINCT AMR_AIS_NUM FROM tblMonthly_Link;", CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic
    intDays = Day(DateSerial(intYear, intMonth + 1, 0))
    While Not rs.EOF
        strAMR = rs!AMR_AIS_NUM
        For i = 1 To intDays
            CurrentDb.Execute "INSERT INTO MonthAndDays(AMR_AIS_NUM, Dates) VALUES('" & strAMR & "', #" & DateSerial(intYear, intMonth, i) & "#)"
        Next
        rs.MoveNext
    Wend
End Sub

[taimysho0]

june, thanks for all the help. i went ahead and just separated the two queries. i made one to find rows <24 and one that finds missing dates. i think its less headache this way. thanks again!