Solved

Compare records with last years records

Posted on 2011-09-07
14
170 Views
Last Modified: 2012-05-12
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
Comment
Question by:QMBB
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36500459
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
 

Expert Comment

by:pkromer
ID: 36500837
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36503507
pkromer,

...I am confused as to what your post is trying to convey...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36503542
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
 

Author Comment

by:QMBB
ID: 36504659
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36505203
...Then this is beyond me,...

I'll let you continue with the first two experts...
0
 

Author Comment

by:QMBB
ID: 36505377
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36507320
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36516614
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
 

Author Comment

by:QMBB
ID: 36531257
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36533269
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
 

Author Comment

by:QMBB
ID: 36533352
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36533510
You did not update all of the references.

Wherever you see x.LastSale, replace it with x.date_purchased.
0
 

Author Comment

by:QMBB
ID: 36540017
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 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

13 Experts available now in Live!

Get 1:1 Help Now