Solved

How can I compare two month totals and show those that are 10% lower than previous month?

Posted on 2014-09-15
3
122 Views
Last Modified: 2014-09-17
The attached query lists each month totals for Cntr.  I want the query to show only months that the Cntr totals were 10% lower than previous month's.
qryComparisonYTD.docx
0
Comment
Question by:softsupport
  • 2
3 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40324405
can you please show some sample data. It is unclear what values are in your qryClaimComparisonYTD.[ClaimDate] column for each row
0
 

Author Comment

by:softsupport
ID: 40325674
Sample data attached.  Query lists total for each month.  I want to compare current months totals to the previous month and show if totals are 10% lower.
dataComparisonYTD.xlsx
0
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40326904
Thanks for the sample data. It really helped. This query will give you the desired result:
SELECT Prev.[Center_Center Id], Prev.[Center Name], 
Prev.[CenterClaim_Center Id], Prev.ClaimDate, 
Prev.CenterTotalClaimed, Curr.ClaimDate, 
Curr.CenterTotalClaimed
FROM qryMealsClaimComparisonYTD AS Prev INNER JOIN qryMealsClaimComparisonYTD AS Curr 
ON (Prev.[CenterClaim_Center Id] = Curr.[CenterClaim_Center Id]) AND (Prev.[Center_Center Id] = Curr.[Center_Center Id])
WHERE ((([Prev].[ClaimDate]+1)=DateAdd("m",-1,[Curr].[ClaimDate]+1)) AND ((Curr.CenterTotalClaimed)<=[Prev].[CenterTotalClaimed]*0.9));

Open in new window

You can adjust the percentage by modifying the last parameter "[CenterTotalClaimed]*0.9". At the moment it is "*0.9" but you can make it 0.8 for 20%
This is the results of this query:
Query Result
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now