Query to Get 7 Days Prior Data (1 Viewer)

[JithuAccess]

Hello Guys,

What is the Query Syntax to find a Week's prior Data?

In my Table I have a field "Max Date" and there is Value From 01-Jan-2021 to 31-Jan-2021. I want to create a Query to get the data only from 25-Jan-2021 to 31-Jan-2021.

I have created a query and in the Criteria of MaxDate I put like Date()-7 but this will get the Data between 02-Mar-2021 to 09-Mar-2021. Then I put like this in the Criteria "MaxDate-7" and it says Data Type Mismatch in the Criteria Expression.

Could you please let me know how to solve this?

Thanks

 

[theDBguy]

Hi. You could maybe use DMax() to find the latest date from the table and then subtract 7 days from it.

 

[JithuAccess]

Hi. You could maybe use DMax() to find the latest date from the table and then subtract 7 days from it.

Thanks a lot for your reply. This is actually storing the Total Hours spend working on a Particular File. There are Fields Like Start Date 1, Start Time 1, End Date 1 and End Date 1 Up to 30 Days. With a Union Query I found the Latest Date worked on a file. Now I want to calculate Total Hours spend a week on a file. For Example if I spend 4 Hours each on 25-Jan-2021 to 30-Jan-2021 and 8 Hours on 31-Jan-2021 I want to find the Total Hours spend on that week as 24:00

Thank You

 

[theDBguy]

Thanks a lot for your reply. This is actually storing the Total Hours spend working on a Particular File. There are Fields Like Start Date 1, Start Time 1, End Date 1 and End Date 1 Up to 30 Days. With a Union Query I found the Latest Date worked on a file. Now I want to calculate Total Hours spend a week on a file. For Example if I spend 4 Hours each on 25-Jan-2021 to 30-Jan-2021 and 8 Hours on 31-Jan-2021 I want to find the Total Hours spend on that week as 24:00

Thank You

Have you tried using a Totals query with the Sum() function?

 

[JithuAccess]

Have you tried using a Totals query with the Sum() function?

Thank You for your reply. I guess if I use the Sum() function it will returns me the Total Hours spend on the File. But I want to find the Sum of Records of MaxDate Minus 7

For 30 Days the Total sum let us assume as 120 Hours and for Last 7 Days Before the Max Date (31-Jan-2021 - 7 which is Between 25-Jan-2021 and 31-Jan-2021) is 20, I want to find just those 20 Hours, Not 120 Hours.

Apologies if this is confusing.

Thanks

 

[theDBguy]

Thank You for your reply. I guess if I use the Sum() function it will returns me the Total Hours spend on the File. But I want to find the Sum of Records of MaxDate Minus 7

For 30 Days the Total sum let us assume as 120 Hours and for Last 7 Days Before the Max Date (31-Jan-2021 - 7 which is Between 25-Jan-2021 and 31-Jan-2021) is 20, I want to find just those 20 Hours, Not 120 Hours.

Apologies if this is confusing.

Thanks

Hi. Not sure you're picking up on what I'm trying to tell you. If you have a list of records for the whole month and you use Sum() on them, you should get the total hours for the whole month. But, if you use DMax() to get the latest date and then use it with DMax()-7 to filter your list of data, then you should only have enough records for the last week of the month data. And when you use the Sum() function on those records, you should get less hours than what you got before without the filter.

 

[JithuAccess]

Hi. Not sure you're picking up on what I'm trying to tell you. If you have a list of records for the whole month and you use Sum() on them, you should get the total hours for the whole month. But, if you use DMax() to get the latest date and then use it with DMax()-7 to filter your list of data, then you should only have enough records for the last week of the month data. And when you use the Sum() function on those records, you should get less hours than what you got before without the filter.

Thanks a lot.

This is my Query

This is my Query Result

Now I am getting the sum of all 28 Days. To find the Total of Last 7 Days, what change I have to make in my query.

Thanks

 

[theDBguy]

I was thinking something more along these lines.

SELECT Sum([TotalHours]) AS Total FROM [QueryName] WHERE [LatestDate] Between DMax("LatestDate","QueryName})-7 And DMax("LatestDate","QueryName")

 

[JithuAccess]

I was thinking something more along these lines.

SELECT Sum([TotalHours]) AS Total FROM [QueryName] WHERE [LatestDate] Between DMax("LatestDate","QueryName})-7 And DMax("LatestDate","QueryName")

Many Thanks for your Help. This is my Query

SELECT Sum([TotalHours]) AS Total FROM [Copy Of Copy Of Query15] WHERE [LatestDate] Between DMax("[LatestDate]","[Copy Of Copy Of Query15]"-7) And Dmax("[LatestDate"],"[Copy Of Copy Of Query15]");

But I am getting an Error like:

Thanks

 

[theDBguy]

Sorry, not sure what happened. Looks like the code I posted replaced a double quote with a curly bracket. Let's try it again.

SELECT Sum([TotalHours]) AS Total FROM [QueryName] WHERE [LatestDate] Between DMax("LatestDate","QueryName")-7 And DMax("LatestDate","QueryName")

 

[JithuAccess]

Sorry, not sure what happened. Looks like the code I posted replaced a double quote with a curly bracket. Let's try it again.

SELECT Sum([TotalHours]) AS Total FROM [QueryName] WHERE [LatestDate] Between DMax("LatestDate","QueryName")-7 And DMax("LatestDate","QueryName")

It Works Perfect!!!


Thanks a lot For your Help Sir

 

[theDBguy]

It Works Perfect!!!


Thanks a lot For your Help Sir

Okay, congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

Although theDBGuy seems to have solved the problem, you might want a more generic solution that will give you data for ANY 7 day period.

I would add an unbound textbox to the form. Set its format to date so that Access knows you will be entering a date and act appropriately. You can set the default value for this unbound control to dmax("LatestDate", "QueryName") Then your query would be:

BetweenForms!yourForm!yourcontrol -7 And Forms!yourForm!yourControl

That allows you to use the default of the latest 7 days or by changing the contents of the control to some other date, select data for the seven days ending at that date.