Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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.
0
QMBB
Asked:
QMBB
  • 5
  • 4
  • 4
  • +1
1 Solution
 
Patrick MatthewsCommented:
QMBB,

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
0
 
pkromerCommented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
pkromer,

...I am confused as to what your post is trying to convey...
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Jeffrey CoachmanMIS LiasonCommented:
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...
0
 
QMBBAuthor Commented:
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.

0
 
Jeffrey CoachmanMIS LiasonCommented:
...Then this is beyond me,...

I'll let you continue with the first two experts...
0
 
QMBBAuthor Commented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Patrick MatthewsCommented:
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:

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 x

Open in new window


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:

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;

Open in new window


0
 
QMBBAuthor Commented:
matthewspatrick,

Regarding the first query: I replaced x.LastSale with my table and field name Orders_Web.date_purchased, tried to run it but Access couldn't find table Sales. Is this designed to run off one table? What am i missing?
0
 
Patrick MatthewsCommented:
I see now that I made a bonehead error, and thus I was failing to retrieve the values from the prior year.  That is fixed here:

SELECT x.LastSale, Now() AS Now, 
    DateDiff("n", x.LastSale, Now()) AS WaitNow, 
    DMax("SaleDate", "Sales", "[SaleDate] <= #" & DateAdd("yyyy", -1, Now()) & "#") AS YearAgoStart, 
    DMin("SaleDate", "Sales", "[SaleDate] > #" & DateAdd("yyyy", -1, Now()) & "#") AS YearAgoEnd, 
    DateDiff("n", DMax("SaleDate", "Sales", "[SaleDate] <= #" & DateAdd("yyyy", -1, Now()) & "#"), 
        DMin("SaleDate", "Sales", "[SaleDate] > #" & DateAdd("yyyy", -1, Now()) & "#")) AS WaitYearAgo
FROM (SELECT Max(s1.SaleDate) AS LastSale 
    FROM Sales AS s1 
    WHERE s1.SaleDate <= Now())  AS x;

Open in new window


Please see the attached file for a working sample.

And yes, I am assuming that there is just one table for sales.  Having a current year table and a prior year table makes no sense.
Q-27296442.mdb
0
 
QMBBAuthor Commented:
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
0
 
Patrick MatthewsCommented:
You did not update all of the references.

Wherever you see x.LastSale, replace it with x.date_purchased.
0
 
QMBBAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now