Solved

Compare records with last years records

Posted on 2011-09-07
14
180 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 
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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

734 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