Query Table1 date to Table2 column1 begin date range to column2 end date range (1 Viewer)

shardez · Mar 10, 2014

Hi, I have a table of data with a column "completion date". I have another table defining "fiscal month" column with 'begin date" column and "end date" column.

Do I have to use SQL to create a query to report back which fiscal month the completion date falls in?

pbaldy · Mar 10, 2014

Your options include, probably in descending order of efficiency, a query with a non-equi join, a subquery with criteria and a DLookup() with criteria. Which to use probably depends on how/where you need the result.

plog · Mar 10, 2014

No, you simply would write a VBA function to take a date and return the Fiscal Month.

My gut tells me you don't need your fiscal month table at all. I bet your fiscal month is calculable. Can you tell me the rules for determining a fiscal month?

shardez · Mar 10, 2014

The fiscal months are based on four 5 week months beginning with first fiscal month June begins Monday, 5/27/13 and ends Sunday 6/30/13, then two four week months follow then 5 week month to begin the next quarter, etc.. The fiscal year ends 5/25/14.

shardez · Mar 10, 2014

month period tablecompl date range1compl date range2fiscal month completion4/29/20135/26/201312/20135/27/20136/30/201301/20147/1/20137/28/201302/20147/29/20138/25/201303/20148/26/20139/29/201304/20149/30/201310/27/201305/201410/28/201311/24/201306/201411/25/201312/29/201307/201412/30/20131/26/201408/20141/27/20142/23/201409/20142/24/20143/30/201410/2014

David R · Mar 10, 2014

That might be more readable if you used the

Code:

 tag instead of [font].

shardez · Mar 10, 2014

Thank you. But I don't know how to do that.

David R · Mar 10, 2014

The [#] button in the post editor will wrap your data in the tag so it's equally spaced and easier to read. I have taken a stab at reparsing your data by hand, see below and fix as necessary.

Code:

        [B]month period table[/B]
compl date range1  compl date range2  fiscal month completion
 4/29/2013             5/26/2013            12/2013
 5/27/2013             6/30/2013            01/2014
 7/1/2013              7/28/2013            02/2014
 7/29/2013             8/25/2013            03/2014
 8/26/2013             9/29/2013            04/2014 
 9/30/2013            10/27/2013            05/2014 
10/28/2013            11/24/2013            06/2014
11/25/2013            12/29/2013            07/2014
12/30/2013             1/26/2014            08/2014
 1/27/2014             2/23/2014            09/2014
 2/24/2014             3/30/2014            10/2014

shardez · Mar 10, 2014

cool. Thanks.

David R · Mar 10, 2014

It looks to me like the 'rule' might actually be that the workweek with X/1/201Y is the full week that starts a fiscal month, and your fiscal calendar starts June 1. Would that be true in other years, or is it a quirk of 2013/4?

Also what happens if the 1st is a Saturday/Sunday?

shardez · Mar 10, 2014

The fiscal week/month/year always begins on Mondays and ends on Sundays. The first day of the current fiscal year was Monday, 5/27/13 and June is the first month of our fiscal year. The last day of fiscal June was Sunday, 6/30/13. This current fiscal month of March began on 2/24/14 and ends on 3/30/14 (the 5 week month of the last quarter).

Hope I've answered your question.

Query Table1 date to Table2 column1 begin date range to column2 end date range (1 Viewer)

shardez

New member

pbaldy

Wino Moderator

plog

Banishment Pending

shardez

New member

shardez

New member

David R

I know a few things...

shardez

New member

David R

I know a few things...

shardez

New member

David R

I know a few things...

shardez

New member

Similar threads

Users who are viewing this thread