We need to know when a service code has been used for a person for N consecutive months. The day part of the date may not be the same from month to month (eg. 7/1 and 8/5), so we're not sure if DateAdd will work. Also the N part of the N consecutive months is different for each service code (e.g. some are every four months, some are every six months). We have a table of entries with Name, SC, and UseDate, which looks like:
Joe F55 7/1/04 Joe F55 8/5/04
This would be two consecutive months. We also have a table of ServiceCodes and the number of consecutive months to look for, which looks like:
F55 6 F50 4
We are working in Access, and need to make a report listing names and codes that exceed the number of consecutive months. We want to base the report on a query, but are not sure how to write the query.
Requires Free Membership to View
Great question!
The following solution involves a theta self-join. If that sounds strange, we will break it down, take it slowly, and see how it works from the ground up.
Before getting to the SQL, let's do a quick review of joins. Consider the numbers 1 through 9. Join these numbers to themselves, and you get 9 * 9 = 81 different combinations. That's the typical cross join we're all familiar with. It's also a self-join. In fact, a self-join can be a cross join, inner join, or any other type of join. In this case, we're interested primarily in a certain type of inner join.
In the following diagram, there's an X where the value down the left is equal to the value across the top. This illustrates an equi-join:
1 2 3 4 5 6 7 8 9 1 X . . . . . . . . 2 . X . . . . . . . 3 . . X . . . . . . 4 . . . X . . . . . 5 . . . . X . . . . 6 . . . . . X . . . 7 . . . . . . X . . 8 . . . . . . . X . 9 . . . . . . . . x
An equi-join is a special type of inner join. It uses equality of values as the join condition. The cross join, by comparison, is also a type of inner join, but it has no join condition, so every value is joined with every value, and there would be an X in every spot in that diagram.
A theta join uses some other condition, not equality, in order to join rows. In the following diagram, there's an X where the value down the left is equal to or greater than the value across the top. This "triangle" appearance illustrates a theta join:
1 2 3 4 5 6 7 8 9 1 X . . . . . . . . 2 X X . . . . . . . 3 X X X . . . . . . 4 X X X X . . . . . 5 X X X X X . . . . 6 X X X X X X . . . 7 X X X X X X X . . 8 X X X X X X X X . 9 X X X X X X X X X
In the following diagram, there's an X where the value down the left is equal to or greater than the value across the top, but not greater than 2 greater. The triangle has been "cut off" and so this illustrates a different theta join:
1 2 3 4 5 6 7 8 9 1 X . . . . . . . . 2 X X . . . . . . . 3 X X X . . . . . . 4 . X X X . . . . . 5 . . X X X . . . . 6 . . . X X X . . . 7 . . . . X X X . . 8 . . . . . X X X . 9 . . . . . . X X X
In general, there are a zillion ways you can construct a theta join. We'll be using a version of the theta join in the last diagram.
Please proceed to N consecutive rows, Part 2 of 4.
This was first published in September 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation