Compare records with last years records
I have a snapshot of our Orders table from Jan.1 2010 to Jan. 1 2011.
What we need is to look at the time difference between orders compared to a year ago…. If, on this date last year at this time (or closest to it), we had 2 orders within 25 minutes, we need to at least have the same scenario today. If the time between orders is greater this year, then the query returns a result.
Example: On 1/3/2010 at 9:54:32 AM, we had an order. The next order on that date was at 10:20:14 AM. I am thinking that the query would run every 15 minutes (via a form timer I already have built, more later) and it would look in that snapshot table to find the closest time period to now. In the above case, the query would know that its start time is 9:54:32 AM and its end time is 10:20:14. Then, it would look at our CURRENT orders table and see if the time difference (in that case roughly 26 minutes) between latest orders is greater than what is in the snapshot table. If it is, a result is returned.
I don’t care what result the query returns… I have a stored procedure which gets run every 15 minutes on a form timer. That procedure checks the result set of the query. If the query is NOT empty, the procedure sends an email notifying a staff member that “Frequency of orders has decreased”.
I know this is convoluted… please ask for any clarification you may need. Thanks very much.
What we need is to look at the time difference between orders compared to a year ago…. If, on this date last year at this time (or closest to it), we had 2 orders within 25 minutes, we need to at least have the same scenario today. If the time between orders is greater this year, then the query returns a result.
Example: On 1/3/2010 at 9:54:32 AM, we had an order. The next order on that date was at 10:20:14 AM. I am thinking that the query would run every 15 minutes (via a form timer I already have built, more later) and it would look in that snapshot table to find the closest time period to now. In the above case, the query would know that its start time is 9:54:32 AM and its end time is 10:20:14. Then, it would look at our CURRENT orders table and see if the time difference (in that case roughly 26 minutes) between latest orders is greater than what is in the snapshot table. If it is, a result is returned.
I don’t care what result the query returns… I have a stored procedure which gets run every 15 minutes on a form timer. That procedure checks the result set of the query. If the query is NOT empty, the procedure sends an email notifying a staff member that “Frequency of orders has decreased”.
I know this is convoluted… please ask for any clarification you may need. Thanks very much.
Yeah, I know. It's a goofy request. But the boss wants it, so there it is. Hopefully I can flesh it out in a manner that's a bit more sensible, but probably not. If anybody has any ideas, I would gladly pay 1000 points for this but I don't know if that's possible.
pkromer,
...I am confused as to what your post is trying to convey...
...I am confused as to what your post is trying to convey...
QMBB.
...I just always get nervous when someone says: "or closest to it"
Perhaps you should just post some sample data.
Then post a graphic representation of the *exact* results expected, based on this data...
...I just always get nervous when someone says: "or closest to it"
Perhaps you should just post some sample data.
Then post a graphic representation of the *exact* results expected, based on this data...
ASKER
matthewspatrick, boaq2000:
Agreed, the dates would be different days, and that would throw things off. The "pkromer" comment above was me signing in from an old system with an old account.... It does seem like an almost impossible thing to pull off, but if it can be solved, I've found in the past that EE's experts can do it.
My snapshot table from last year has a field called date_purchased. In that field is:
1/1/2010 12:19:44 AM
1/1/2010 1:05:57 AM
1/1/2010 5:53:51 AM
1/1/2010 8:04:22 AM
1/1/2010 8:30:22 AM
1/1/2010 9:27:12 AM
1/1/2010 10:13:15 AM
1/1/2010 10:32:53 AM
1/1/2010 11:37:03 AM
etc through the rest of the year.
My current table has the same feild and data structure.
I want to compare the two tables every half hour and find the closest match to the order times in the two, and then calculate whether or not the time difference between orders placed is greater than last year. If it is, I use my existing routine to send an email.
Per matthewspatrick's comment, I would also need a table with last year's days corresponding to the dates.... that way, the email could say something like "Last year on this date was a Saturday" so the recipient could factor that into thier analysis.
Agreed, the dates would be different days, and that would throw things off. The "pkromer" comment above was me signing in from an old system with an old account.... It does seem like an almost impossible thing to pull off, but if it can be solved, I've found in the past that EE's experts can do it.
My snapshot table from last year has a field called date_purchased. In that field is:
1/1/2010 12:19:44 AM
1/1/2010 1:05:57 AM
1/1/2010 5:53:51 AM
1/1/2010 8:04:22 AM
1/1/2010 8:30:22 AM
1/1/2010 9:27:12 AM
1/1/2010 10:13:15 AM
1/1/2010 10:32:53 AM
1/1/2010 11:37:03 AM
etc through the rest of the year.
My current table has the same feild and data structure.
I want to compare the two tables every half hour and find the closest match to the order times in the two, and then calculate whether or not the time difference between orders placed is greater than last year. If it is, I use my existing routine to send an email.
Per matthewspatrick's comment, I would also need a table with last year's days corresponding to the dates.... that way, the email could say something like "Last year on this date was a Saturday" so the recipient could factor that into thier analysis.
...Then this is beyond me,...
I'll let you continue with the first two experts...
I'll let you continue with the first two experts...
ASKER
Thanks, I do understand. I may have to tell the powers that be "no can do". I will give you some points, though, regardless how this turns out. Thanks for taking a look.
No points for me if my post does not help in solving the issue.
Assigning Points marks the post as the "Solution".
So if my post is not a solution, another member searching here for the same issue will wonder why you "accepted" it.
Make sense.
If you want to give me something, just send me an email and say "Thanks"
Pleas leave the Q open for a few more days for the other Experts to reply.
One long-shot would be to click the "request Attention" link and ask that the Excel zone be added to this Q.
I know that this is an Access question, but sometimes solutions to puzzles like this lend themselves more to Excel, than Access.
JeffCoachman
Assigning Points marks the post as the "Solution".
So if my post is not a solution, another member searching here for the same issue will wonder why you "accepted" it.
Make sense.
If you want to give me something, just send me an email and say "Thanks"
Pleas leave the Q open for a few more days for the other Experts to reply.
One long-shot would be to click the "request Attention" link and ask that the Excel zone be added to this Q.
I know that this is an Access question, but sometimes solutions to puzzles like this lend themselves more to Excel, than Access.
JeffCoachman
There is no need to have separate tables for this year and last year; in fact, that is probably a bad idea.
The following query is working for me:
I also tried the following (trying to avoid using the performance-killing domain aggregate functions), which looks like it should have worked, but Access balked at the syntax:
The following query is working for me:
SELECT x.LastSale, Now() AS Now, DateDiff("n", x.LastSale, Now()) AS WaitNow,
DMax("SaleDate", "Sales", "[SaleDate] <= #" & Now() & "#") AS YearAgoStart,
DMin("SaleDate", "Sales", "[SaleDate] > #" & Now() & "#") AS YearAgoEnd,
DateDiff("n", DMax("SaleDate", "Sales", "[SaleDate] <= #" & Now() & "#"),
DMin("SaleDate", "Sales", "[SaleDate] > #" & Now() & "#")) AS WaitYearAgo
FROM
(SELECT Max(s1.SaleDate) AS LastSale
FROM Sales s1
WHERE s1.SaleDate <= Now()) AS xI also tried the following (trying to avoid using the performance-killing domain aggregate functions), which looks like it should have worked, but Access balked at the syntax:
SELECT x.LastSale, Now() AS Now, DateDiff("n", x.LastSale, Now()) AS WaitNow, y.YearAgoStart,
z.YearAgoEnd, DateDiff("n", y.YearAgoStart, z.YearAgoEnd) AS WaitYearAgo
FROM
(SELECT Max(s1.SaleDate) AS LastSale
FROM Sales s1
WHERE s1.SaleDate <= Now()) AS x,
(SELECT Max(s2.SaleDate) AS YearAgoStart
FROM Sales s2
WHERE s2.SaleDate <= DateAdd("yyyy", -1, Now()) AS y,
(SELECT Min(s3.SaleDate) AS YearAgoEnd
FROM Sales s3
WHERE s3.SaleDate > DateAdd("yyyy", -1, Now()) AS z;ASKER
matthewspatrick,
Regarding the first query: I replaced x.LastSale with my table and field name Orders_Web.date_purchased,
Regarding the first query: I replaced x.LastSale with my table and field name Orders_Web.date_purchased,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Downloaded your example, looked, altered my code to this:
SELECT x.LastSale, Now() AS Now,
DateDiff("n", x.LastSale, Now()) AS WaitNow,
DMax("date_purchased", "Orders_Web", "[date_purchased] <= #" & DateAdd("yyyy", -1, Now()) & "#") AS YearAgoStart,
DMin("date_purchased", "Orders_Web", "[date_purchased] > #" & DateAdd("yyyy", -1, Now()) & "#") AS YearAgoEnd,
DateDiff("n", DMax("date_purchased", "Orders_Web", "[date_purchased] <= #" & DateAdd("yyyy", -1, Now()) & "#"),
DMin("date_purchased", "Orders_Web", "[date_purchased] > #" & DateAdd("yyyy", -1, Now()) & "#")) AS WaitYearAgo
FROM (SELECT Max(s1.date_purchased) AS date_purchased
FROM Orders_Web AS s1
WHERE s1.date_purchased<= Now()) AS x;
got a box asking for x.LastSale
SELECT x.LastSale, Now() AS Now,
DateDiff("n", x.LastSale, Now()) AS WaitNow,
DMax("date_purchased", "Orders_Web", "[date_purchased] <= #" & DateAdd("yyyy", -1, Now()) & "#") AS YearAgoStart,
DMin("date_purchased", "Orders_Web", "[date_purchased] > #" & DateAdd("yyyy", -1, Now()) & "#") AS YearAgoEnd,
DateDiff("n", DMax("date_purchased", "Orders_Web", "[date_purchased] <= #" & DateAdd("yyyy", -1, Now()) & "#"),
DMin("date_purchased", "Orders_Web", "[date_purchased] > #" & DateAdd("yyyy", -1, Now()) & "#")) AS WaitYearAgo
FROM (SELECT Max(s1.date_purchased) AS date_purchased
FROM Orders_Web AS s1
WHERE s1.date_purchased<= Now()) AS x;
got a box asking for x.LastSale
You did not update all of the references.
Wherever you see x.LastSale, replace it with x.date_purchased.
Wherever you see x.LastSale, replace it with x.date_purchased.
ASKER
Pretty damn amazing. Thank you very, very much. I may have some further tweaking to do with this, so I will post another question if so, but you came up with exactly what I asked for. Thanks again.
With respect, this strikes me as an odd request.
For one thing, comparing data against the same date from the prior year ignores the fact that the two dates are different weekdays, and as such that may not be such a valid comparison.
For another, if your orders are, on average, 25 or so minutes apart, we are not talking about a high-volume occurrence, and so you'll need to e very careful about making any inferences based on differences in waiting time.
Patrick