List Quarters Between Two Dates

[Dhamdard]

Hi,

I have project [StartDate] and [EndDate] in a table A. How can I run a query that lists down quarters in rows or columns. For example:

Start Date: 8/1/2018
End Date: 11/30/2019

Expect Result:

Q3 2018
Q4 2018
Q1 2019
Q2 2019
Q3 2019
Q4 2019

Thanks for the help.
Dhamdard

 

[June7]

Are your quarters based on calendar year?

DatePart("Q", date value)

Year(date value)

Format(date value, "\Qq yyyy")


If you want to list all the quarters that occur between those two dates, will have to use VBA writing records to a table.

 

[plog]

You cannot. That's not how queries work.

Queries must be based on data sources that contain all the data you want to generate. It cannot generate data that is not in the underlying datasource.

What's the big picture of your project? What does this set of data do for you?

 

[Dhamdard]

@June7: Yup. Start date should determine the quarter in calendar year.

@plog: The project tracks progress towards objectives. Data comes in regularly. To display progress cumulatively for each quarter, I want the database to automatically generate a list of quarters between two dates. Then I will work through it to show cumulative progress for each quarter for each objective.

Hope this helps.

Thanks
DHamdard

 

[plog]

You would then need a table that has all quarters that you want to report on. I suggest you use a date field for that and input the first date of the quarter (e.g. 1/1/2018, 4/1/2018, 7/1/2018, etc.) that way you can use all of Access's built in data functions (https://www.techonthenet.com/access/functions/index.php).

You would then use that as the data source for your query.

 

[Dhamdard]

Thanks, plog. I still believe that there is a way for it. Query could give you that list and then another query has to be run on the first query to create a table for it which will eliminate the manual entry of quarters between two dates. When I find the solution, I will post it here.

 

[June7]

Actually, a query can generate a list of dates between two given dates. Requires a dataset of integers which can be generated by referencing MSysObjects table (because this table can normally be relied on to have enough records), otherwise build a table with 10 records with one field holding the values 0 through 9.

Unfortunately, it executes slowly as is normally the case with Cartesian relationship.

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT DISTINCT Format([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, "\Qq yyyy") AS QtrYear, 
Format([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, "yyyy q") AS YearQtr
FROM MSysObjects AS Ones, MSysObjects AS Tens, MSysObjects AS Hundreds
WHERE [StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1 Between [StartDate]-1 And [EndDate]
ORDER BY Format([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, "yyyy q");

Since this is 'found' code, don't ask me to explain it any further. I'll let you analyze.

I did modify to use Format function and added the ORDER BY clause.

 

[Dhamdard]

Yeah, this works very slow, but thanks. I found the following language, but I am struggling with how to make it work for my table in a query. My table name is Tab_Projects.

Declare @start Date = '1-Apr-2011'
Declare @end date ='1-Jul-2012'
;With cte
As
( Select @start date1
Union All
Select DateAdd(Month,3,date1) From cte where date1 < @end
) Select Datename(month,date1) + ' ' + Datename(year,date1) From cte

 

[June7]

And what programming language is that built in? JAVA, PHP, etc?

In Access, to do what you want requires a Cartesian relationship query or VBA writing records to table.

 

[Dhamdard]

It's T-SQL language. I am trying to make it work in Access SQL Query.

 

[MarkK]

Do you have data in each quarter? Like a date, that falls in each quarter? If so you can use the DatePart() function, and group by the result, like....

SELECT DatePart("q",YourDate) AS Qr, Year(YourDate) AS Yr
FROM tYourData
GROUP BY DatePart("q",YourDate), Year(YourDate);

But again, the data must exist.
Mark

 

[Dhamdard]

Thanks, Mark. This will give me the quarter and the year for every single case. I want to ask the query to run a loop. Look at the start date and the end date of a project, and then list all quarters that fall within the start and end dates.

 

[plog]

Perhaps a 3rd voice of reason will sway him?

 

[GinaWhipp]

Well, you can do this in a crosstab query. Have a look at...
https://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/index.html

You will need to adapt for quarters by changing the Column Headings to 1, 2, 3, 4. Hmm, the query will probably need a little adjustment. Then I would use a Public Variable to hold the dates, should give you what you want.

 

[June7]

Query in Access cannot 'loop'. I think Cartesian relation is closest you will get for vertical output.

I did not think about columnar approach. How many years/quarters could there be in a single output? Query has limit of 255 fields. Think that should be enough. But how useful will that be? Will you want to print a report?

That FMS site CROSSTAB example restricts output to a single year. A 'rolling' dynamic date range is more complicated. Review https://access-programmers.co.uk/forums/showthread.php?t=242545 and http://www.access.hookom.net/DynamicMthlyCrosstabRpt.htm

 

[jdraw]

Dhamdard,
Perhaps you could mock up what you think the output should look like--just a few records so we get some idea of the date range in your project(s).
Don't think you could do it with a query for multiple years, but you might be able to use a function. But I'm only guessing since the number of quarters may be too large?? and we really don't know where this is all going nor who would use the output and for what exactly.

 

[MarkK]

Look at the start date and the end date of a project, and then list all quarters that fall within the start and end dates.

This is called a WHERE clause.

SELECT DatePart("q",YourDate) AS Qr, Year(YourDate) AS Yr
FROM tYourData
[COLOR="Blue"]WHERE YourDate > [pStartDate] AND YourDate <= [pEndDate][/COLOR]
GROUP BY DatePart("q",YourDate), Year(YourDate);

Mark

 

[GinaWhipp]

@June7,

Initial question asked for *rows* or *columns*. I opted for columns since I know that can be done.

The FMS CROSSTAB does NOT restrict to a single year. I modified it to do rolling quarters starting at the initial date entered though you could *stop* it with an ending date (took all of 10 minutes), so it is definitely a rolling dynamic range.

 

[June7]

@GinaWjipp,

Okay, but doesn't it limit to 12 month columns - or for quarters, 4 quarter columns? If the date range encompassed 50 quarters, how would CROSSTAB generate 50 quarter columns? The OP doesn't provide any data that would be aggregated under those quarter headers.

 

[GinaWhipp]

@June7,

No it does limit 12 month columns or 4 quarter columns, you simply need a YEAR field. That said, you will have to take into consideration that query have a limit though the OP indicated no more than eight quarters in the first post so that should not be a problem.

To explain further, each year has for quarters. So, while the Column Headings display 1 thru 4 repeatedly, the data is actually from the quarter\year. You can then use dynamic data in controls on your report to make sure your labels match up to the data.

That example that FMS provided is quite flexible but it does require a little tinkering if not using it *right out of the box*.