Query for different date criteria (1 Viewer)

[Tor_Fey]

Good Afternoon All;

Is it possible to run a query on a date field with different criteria in one query?

I have a date filed in my main table which needs to work on the following date criteria in a report:

01/04/2017 to 30/06/2017 - Quarter 1
01/07/2017 to 30/09/2017 - Quarter 2
01/10/2017 to 31/12/2017 - Quarter 3
01/01/2018 to 31/03/2018 - Quarter 4

If I build a query for each quarter individually it works fine, but I am unsure how to join them together. I need to create a report that can report on all 4 quarters as above, is this possible?

In each individual query I have the record_id and the date criteria written like this:

between 01/04/2017 and 30/06/2017
between 01/07/2017 and 30/09/2017
between 31/12/2017 and 31/12/2017
between 01/01/2018 and 31/03/2018

Ideally I need my report to show count the number of records for each quarter and then give a total count at the end.

Your help once again is much appreciated.

Kind Regards
Tor Fey

 

[plog]

You are using standard quarters, so just use the DatePart function (https://www.techonthenet.com/access/functions/date/datepart.php). Pass it a date, it tells you what quarter the date is in.

No need for criteria, no need for 4 queries. Just 1 query that groups on the quarter.

 

[Tor_Fey]

Good Morning plog;

Is there no way to write it as an expression in the query?
For example:

quarter1: between ([date] "01/04/2017") & ([date] "30/06/2017")

Kind Regards
Tor Fey

 

[Tor_Fey]

Morning All;

I have attempted to write the query as per attached screenshot, but it doesn't display any data.

Regards
Tor Fey:banghead:

 

[Minty]

If you look at your criteria they are all on one line, so you are effectively saying they all have to be true to get a row returned. (it's using an AND in the where clause)

Moving each criteria to a separate row will change that to an OR. I've no idea if that will get what you want though.

 

[Tor_Fey]

Hi Minty;

This is now working as you suggested; thank you, but...... it is showing data for quarter1, quarter3 and quarter4; but in actual fact I just need it to show data for the quarters that fall within the specific date ranges.

Is there a way to stop this?

Kind Regards
Tor Fey

If you look at your criteria they are all on one line, so you are effectively saying they all have to be true to get a row returned. (it's using an AND in the where clause)

Moving each criteria to a separate row will change that to an OR. I've no idea if that will get what you want though.

 

[Minty]

Perhaps you could post up some sample data and a sample of what your are trying to get out from that sample data.

Put enough data in to cover all eventualities.

 

[arnelgp]

Create an Aggregate or Total Query:

Field:	|TF_Ben_ID	|TF_Ben_FileRef	|TF_BenSurname	|Quarter1: IIf(DatePart("q",[TF_Ben_Date_Interview_under_Caution])=1 And Year([TF_Ben_Date_Interview_under_Caution])=2017,1,Null)
Table:
Total:	|Group		|Group		|Group		|Count

now complete the other Quarter by substituting

DatePart([date_Field])=1

With

DatePart([date_Field])=2
DatePart([date_Field])=3
DatePart([date_Field])=4