Calculating trends in data/Notification of data changes

[jcfraun]

I have a PivotTable (i.e., a query) that shows me monthly sales (i.e., sales vs. month/year). Is there a way to program Access to pop up a notification if sales decrease by more than say 10% in a month?

 

[MajP]

Can you explain more how you would like this to work? Things like:
1)When do you get the data?
2)When are the queries run?
3)Is this a multi user environment?
4)Do you just want this for the current month compared to last month, when current month data arrives? Or do you want a summary of every month where there is a change.
5)You can always have a query showing decreases of 10% in a month. If a user can manually pull-up that query is that automated enough.

Sure you can program this, just not sure how you are expecting this to work and how automated this needs to be. For example is it ok that when a user logs in/logs out it runs the check? Or is this a multi user environment that is continually updated, and you want every user machine to check the incoming data periodically?

 

[jcfraun]

This is just for use by one person. A query would be fine - it doesn't really have to be automated. I know how to compare and manipulate fields, but I need to compare and manipulate records. Does that make sense?

My data is roughly in this format:
Keyword Month Year Sales (etc.)

I want to know for each keyword, if sales have increased this month or decreased. I want to show over time what the sales are doing. So, a report with date vs. keyword and a flag to indicate whether sales increased or decreased for the month.

I hope this is enough info. If you need more, let me know. I appreciate the help!

 

[MajP]

There are few absolutes in database design, but Never Ever Ever save date information in anything but a single date field. You have made doing calculations on dates extremely difficult. If you save it in a single field you can format it to show a date or part of that date in any possible format you could think of. Further you can do any type of date calculation. Also I would strongly recommend not using "Month" and "Year" as a field name. These are names of vb functions (i.e. Month(date())).
However, the good news you can either fix it or use the date serial to turn your two fields into a date.

So before starting make a simple query to put this in proper format to work with. This assumes month is (1-12) and year is a number.

qrySales

SELECT 
 tblSales.ID, 
 tblSales.Keyword, 
 DateSerial([lngyear],[intMonth],1) AS MonthYear, 
 tblSales.sales
FROM 
 tblSales
ORDER BY 
 tblSales.Keyword, 
 DateSerial([lngyear],[intMonth],1);

The new data looks like

ID	Keyword	MonthYear  sales
1       ABC     1/1/2010   $10.00
2       ABC     2/1/2010   $10.00
3       ABC     3/1/2010   $11.00
4       CDE     12/1/2009  $100.00
5       CDE     1/1/2010   $90.00
6       CDE     2/1/2010   $110.00
7       CDE     4/1/2010   $115.00

Now there are lots of queries to do this. But you need to use some kind of sub query to return the previous record for each record. Here is a possible method

SELECT 
 CurrentSales.ID, 
 CurrentSales.Keyword, 
 CurrentSales.MonthYear, 
 CurrentSales.sales, 
 (Select Top 1 monthYear from qrySales where CurrentSales.monthyear > qrySales.monthyear AND qrySales.keyword = currentSales.keyword order by MonthYear Desc) AS PreviousMonth, 
 (Select Top 1 Sales from qrySales where CurrentSales.monthyear > qrySales.monthyear AND qrySales.keyword = currentSales.keyword order by MonthYear Desc) AS PreviousSales
FROM 
 qrySales 
AS CurrentSales
WHERE ((Not ((Select Top 1 Sales from qrySales where CurrentSales.monthyear > qrySales.monthyear AND qrySales.keyword = currentSales.keyword order by MonthYear Desc)) Is Null));

This gives me

ID	Keyword	MonthYear	sales	PreviousMonth	PreviousSales
2	ABC	2/1/2010	$10.00   1/1/2010	$10.00
3	ABC	3/1/2010	$11.00   2/1/2010	$10.00
5	CDE	1/1/2010	$90.00   12/1/2009	$100.00
6	CDE	2/1/2010	$110.00  1/1/2010	$90.00
7	CDE	4/1/2010	$115.00  2/1/2010	$110.00

Now you can use the above query to subtract Sales from previous sales and get your change

SELECT 
 qryCurrentPrevious.ID, 
 qryCurrentPrevious.Keyword, 
 qryCurrentPrevious.sales, 
 qryCurrentPrevious.MonthYear, 
 qryCurrentPrevious.PreviousMonth, 
 qryCurrentPrevious.PreviousSales, 
 [PreviousSales]-[Sales] AS SalesChange, 
 ([PreviousSales]-[Sales])/[PreviousSales] AS PercentChange
FROM 
 qryCurrentPrevious;

data looks like

Keyword	sales	MonthYear	PreviousMonth	PreviousSales	SalesChange	PercentChange
ABC	$10.00	2/1/2010	1/1/2010	$10.00          $0.00        0.00%
ABC	$11.00	3/1/2010	2/1/2010	$10.00          ($1.00)     -10.00%
CDE	$90.00	1/1/2010	12/1/2009	$100.00         $10.00       10.00%
CDE	$110.00	2/1/2010	1/1/2010	$90.00          ($20.00)    -22.22%
CDE	$115.00	4/1/2010	2/1/2010	$110.00         ($5.00)     -4.55%

 

[jcfraun]

Thank you, thank you!

I very much appreciate the tip about the date. This is not a problem as it starts as an actual date and I split it for a reason I'm not remembering. I can just go back and "unsplit" it. I always appreciate a good database tip, though, as I'm fairly new to this.

 

[MajP]

That is good, that saves you a step.
I showed my dates in the results as
2 ABC 2/1/2010 $10.00 1/1/2010 $10.00
But without any code and just formatting this could be shown
2 ABC Feb 2010 $10.00 Jan 2010 $10.00

 

[jcfraun]

Along these same lines, is there a way to use this methodology to get not just previous sales, but only sales from the previous month (monthyear - 1 month) and then sales from the previous month (monthyear - 2 months) and then sales from the previous month (monthyear - 3 months),etc.

So, in this example:
Keyword LatestMonthSales LastMonthSales TwoMonthsAgoSales ThreeMonthsAgoSales etc.
ABC 0 11 10 10 0
CDE 115 0 110 90 100

 

[MajP]

I was hoping someone smarter in sql will chime in. I am pretty good with Access coding, but not so good in the sql. There is a way to do this with subqueries, but I could not get it to work. But the logic is the same as selecting the second 10 records from a table (records 11-20)

select top 10 *
from orders
where orderid not in (select top 10 orderid from orders order by orderid)
order by orderid

So the second month is the top month less than the current month, and the second month previous is the top month less than the current month and not in the top month less than the previous month.

So here is my Barnie style solution.

Build a public function just to make life easier

Public Function getPrevMnth(varDate As Date) As Date
  If IsDate(varDate) Then
    getPrevMnth = DateAdd("M", -1, varDate)
    getPrevMnth = DateSerial(Year(getPrevMnth), Month(getPrevMnth), 1)
  End If
End Function

use in query

SELECT 
 CurrentMonth.KeyWord, 
 CurrentMonth.MonthYear AS CurrentMonth, 
 CurrentMonth.Sales AS CurrentSales, 
 [1Prior].MonthYear AS 1MnthPrev, 
 [1Prior].Sales AS SalesMnthPrev, 
 [2Prior].MonthYear AS 2MnthPrev, 
 [2Prior].Sales AS Sales2MnthPrev, 
 [3Prior].MonthYear AS 3MnthPrev, 
 [3Prior].Sales AS Sales3MnthPrev
FROM 
 ((tblSales AS CurrentMonth 
INNER JOIN 
 tblSales AS 1Prior 
ON 
 CurrentMonth.KeyWord = [1Prior].KeyWord) 
INNER JOIN 
 tblSales AS 2Prior 
ON 
 [1Prior].KeyWord = [2Prior].KeyWord) 
INNER JOIN 
 tblSales AS 3Prior 
ON 
 [2Prior].KeyWord = [3Prior].KeyWord
WHERE ((([1Prior].MonthYear)=getPrevMnth([currentMonth].[MonthYear])) 
 AND (([2Prior].MonthYear)=getPrevMnth([1Prior].[MonthYear])) 
AND   (([3Prior].MonthYear)=getPrevMnth([2Prior].[MonthYear])));