CODEWITHSUNDEEP
sqldatems-accessms-access-2016
ViktorAlexiev
ViktorAlexiev

Reputation: 39

How to get the count of a given record from a 2-week period before a current date in a MS Access database

This is part of the data. The full table has more rows and columns:

1

This is how my dates are structured in my table. They are shortText type, because I import my data from a txt file. So later on I use DateSerial function to turn the different year, month and day records into a date to compare the dates easier. And I use the VAL function on the separate records to turn them from shortText to numbers, because the DateSerial function accepts only numeric values.

I want to get the count of how many times a record named Meteo_Rain mm/hr is > 0 for a 2 week period before my current date

SELECT 
    DateSerial(Val(VEC_data.[Date&Time_YYYY]), Val(VEC_data.[Date&Time_mm]), Val(VEC_data.[Date&Time_DD])) AS Expr1, 
    COUNT(VEC_data.[Meteo_Rain,mm/hr]) AS [CountOfMeteo_Rain,mm/hr]
FROM 
    VEC_data
WHERE 
    (DateSerial(VAL(VEC_data.[Date&Time_YYYY]), VAL(VEC_data.[Date&Time_mm]), VAL(VEC_data.[Date&Time_DD])) 
        BETWEEN DateAdd("d", -14, DateSerial(Forms!graph_input!enter_year, VAL(VEC_data.[Date&Time_mm]), VAL(VEC_data.[Date&Time_DD]))) 
            AND DateSerial(Forms!graph_input!enter_year, VAL(VEC_data.[Date&Time_mm]), VAL(VEC_data.[Date&Time_DD]))) 
    AND Val(VEC_data.[Meteo_Rain,mm/hr]) > 0
GROUP BY 
    DateSerial(Val(VEC_data.[Date&Time_YYYY]), Val(VEC_data.[Date&Time_mm]), Val(VEC_data.[Date&Time_DD]))
ORDER BY 
    DateSerial(Val(VEC_data.[Date&Time_YYYY]), Val(VEC_data.[Date&Time_mm]), Val(VEC_data.[Date&Time_DD]));

This is the result from the query:

enter image description here

It basically returns the count of the record only form the current date.

This query returns a list of what dates are compared. The dates compared are

current_date BETWEEN start_date AND end_date

SELECT DISTINCT 
    (DateSerial(Val([VEC_data].[Date&Time_YYYY]), Val([VEC_data].[Date&Time_mm]), Val([VEC_data].[Date&Time_DD]))) AS [Current_date], 
    DateAdd("d", -14, DateSerial([Forms]![graph_input]![enter_year], Val([VEC_data].[Date&Time_mm]), Val([VEC_data].[Date&Time_DD]))) AS start_date, 
    DateSerial([Forms]![graph_input]![enter_year], Val([VEC_data].[Date&Time_mm]), Val([VEC_data].[Date&Time_DD])) AS end_date
FROM 
    VEC_data
WHERE 
    (((Val([VEC_data].[Meteo_Rain,mm/hr]))>0));

enter image description here

How can I remake the first query so it counts the Meteo_Rain,mm/hr records in all the dates from the 2 week period?

Upvotes: 0

Views: 55

Answers (0)

Related Questions