List Quarters Between Two Dates

[June7]

Well, curious to see the SQL statement that displays Column Headings 1 through 4 repeatedly.

 

[GinaWhipp]

You can create yourself if you download the example and tinker.

 

[GinaWhipp]

Oops, mistake Column Headings are numbered one thru whatever. (Sorry about that did not remember that until I went and looked at the query.) It's the data that is showing 1 thru 4 and then 1 thru 4 or whatever you start on thru 4 a revolving 4 quarters. So if you start one quarter 2 the data will display 2 , 3, 4, 1 (of following year) and will follow that rotation.

 

[June7]

I can get 12 month or 4 quarter columns. I don't see any way to have 50 quarter headings, or however many quarters a project encompasses with the IN clause. The posted example date range has 6 quarters. So I can get 2 rows and 4 quarter columns.

I am able to CROSSTAB the query I posted earlier and I get 6 quarter columns. Still runs slow.

 

[GinaWhipp]

I'm not sure where you are getting 50 quarters. I see a start date of 4.2011 and an end date of 7.2012, that's not 50 quarters. I can get that to run fairly quickly. Did I miss the post about 50 quarters?

That said, the OP only wanted the dates to create a table? Or is there data? I guess should they come back and explain a bit more no point in worrying about it.

 

[June7]

The 50 quarters is theoretical, pulled out of the air - a 'what if'. In post 1 OP states the table has Project records with fields for StartDate and EndDate. Wants to list all the quarters between those two dates. I presume a Project could have a start and end that encompasses any number of quarter periods. And each project will be different.

I have no idea how to make the query work with the fields in table to provide the beginning and ending dates.

 

[GinaWhipp]

Okay, well then I will wait to hear back because if the dates and just show in a query, i.e. a crosstab then that easy enough but I feel it's more than that.

 

[Dhamdard]

Thank you for the useful hints. However, I must clarify what I expect to do. It's a new project. It starts on Aug 1, 2018 and ends on Dec 30, 2019. Both start date and end dates are entered in a form. Form name is Tab_Project and field names are StartDate and EndDate.

Now, I want to design a query that automatically runs all quarters between the start and end date. I did the following:

Select Format(Tab_Project.StartDate,"q - yyyy")
from Tab_Project
UNION ALL SELECT Format(DateAdd("m",3,Tab_Project.StartDate),"q - yyyy")
FROM Tab_Project
WHERE Tab_Project.StartDate<Tab_Project.EndDate;

But it shows the thrid quarter and the fourth quarter because project starts in Aug which falls within 3rd quarter. Below is a snapshot of the resultant query:

Expr1000
3 - 2018
4 - 2018

I want to see all quarters between the start and end dates. What should I do differently with the SQL language above so that the query lists all quarters between the two dates?

 

[GinaWhipp]

Hmm, no UNION query needed.

Step 1
In your database create a table named tblCount and add one field named dNumber - Long and make it a Primary Key. Start with the number 0 and continue adding until you get to 1724. (Okay, tedious but worth it so just keep lugging away till you get to that number. )

Step 2
Create a SELECT query (mine is named qryGetProjectDays) based off of the Table that has the Project Start Date and End date and drop tblCount in and do NOT create any joins. You need one field...

TheDays: DateAdd("d",[dNumber],[pStartDate])

and add <=[pEndDate] to the Criteria section of that one field
(example of what you should have below)...

SELECT DateAdd("d",[dNumber],[pStartDate]) AS TheDays
FROM tblYourTable, tblCount
WHERE (((DateAdd("d",[dNumber],[pStartDate]))<=[pEndDate]))
ORDER BY DateAdd("d",[dNumber],[pStartDate]);

Note: When you preview the query provided you have entered a Start Date and and End Date you will see a record for every day in the Project.

Step 3
Create a new SELECT query and place qryGetProjectDays (or whatever you named your query) in your new SELECT query. Place...

CDate(Format([TheDays],"q-yyyy"))

on the Field line below (example below). You should now see all Quarters between two dates.

SELECT CDate(Format([TheDays],"q-yyyy")) AS ProjectQuarters
FROM qryGetProjectDays
GROUP BY CDate(Format([TheDays],"q-yyyy"));

I should add I so busy focused on the CROSSTAB query I forgot I had already done this some time ago for forecasting.

 

[June7]

Fast way to add 1724 records:

Sub AddRecs()
Dim x As Integer
For x = 1 To 1724
     CurrentDb.Execute "INSERT INTO tblCount(dNumber) VALUES(" & x & ")"
Next
End Sub

STEP 2 is a Cartesian relation query. Hope this one is faster.

 

[GinaWhipp]

Decided to upload a sample...

 

[MarkK]

Fast way to add 1724 records:

Sub AddRecs()
Dim x As Integer
For x = 1 To 1724
     CurrentDb.Execute "INSERT INTO tblCount(dNumber) VALUES(" & x & ")"
Next
End Sub

Faster way--like way faster--inside a transaction with a QueryDef...

    Const SQL As String = _
        "INSERT INTO Table1(data) VALUES( p0 )"
    
    Dim i As Integer
    
    DBEngine.BeginTrans
    With CurrentDb.CreateQueryDef("", SQL)
        For i = 1 To 1724
            .Parameters(0) = i
            .Execute
        Next
        .Close
    End With
    DBEngine.CommitTrans

And here's code that does the timings, and you might be impressed by the speed improvement for the latter approach...

[SIZE="1"]Sub AddRecs()
    Const SQL As String = _
        "INSERT INTO Table1(data) VALUES( p0 )"
    
    Dim clock As Single
    Dim i As Integer
    
    clock = Timer
    DBEngine.BeginTrans
    With CurrentDb.CreateQueryDef("", SQL)
        For i = 1 To 1724
            .Parameters(0) = i
            .Execute
        Next
        .Close
    End With
    DBEngine.CommitTrans
    Debug.Print Format(Timer - clock, "0.000") & " in a transaction with a querydef"
    
    
    clock = Timer
    Dim x As Integer
    For x = 1 To 1724
        CurrentDb.Execute "INSERT INTO table1(data) VALUES(" & x & ")"
    Next
    Debug.Print Format(Timer - clock, "0.000") & " without a transaction or a querydef"
End Sub
[/SIZE]

Mark

 

[GinaWhipp]

@MarkK

Okay to post on my Blog with credit of course?

    Const SQL As String = _
        "INSERT INTO Table1(data) VALUES( p0 )"
    
    Dim i As Integer
    
    DBEngine.BeginTrans
    With CurrentDb.CreateQueryDef("", SQL)
        For i = 1 To 1724
            .Parameters(0) = i
            .Execute
        Next
        .Close
    End With
    DBEngine.CommitTrans

 

[MarkK]

@Gina, yeah, no problem.
Mark

 

[Dhamdard]

The following SQL view in Access produced two values (3 2018, 4 2018) 3 is third quarter, and 4 is the fourth quarter.

/Code:
SELECT Format(Tab_Project.StartDate,"q - yyyy") AS "Total Quarters"
FROM Tab_Project
UNION ALL SELECT Format(DateAdd("m",3,Tab_Project.StartDate),"q - yyyy") AS "Start Date"
FROM Tab_Project
WHERE (((Tab_Project.StartDate)<[Tab_Project].[EndDate]));
\Code

Project start date is Aug 1 2018 and the end date is Dec 30 2019. What is missing is that I want to see all the remaining quarters upto Dec 30 2019.

 

[GinaWhipp]

I uploaded a sample. I also explained how to do it. You need two queries, one to produce the days and the other to give you the quarters.

 

[Dhamdard]

@GinaWhipp. Mall Gracia. I saw that and it works. I will definitely use it. But I still believe that a simple query language can do it too. That's why I posted the language again.

 

[GinaWhipp]

It can't because as everyone has already said it NEEDS data (the days) in order to get the quarters. No way for the query to produce what it does't have.

 

[MarkK]

This is almost certainly true for every record.

WHERE (((Tab_Project.StartDate)<[Tab_Project].[EndDate]));

No?
Mark