Query modification

[endri81]

Hi,
By using the following query

SELECT District.Id, District.Emer, Data_SDP.[Data e fillimit te tremujorit], Format$([Data e fillimit te tremujorit],"\Qq yyyy",0,0) AS Tremujori, Sum(Data_SDP.Mg3) AS [Low Dose], Sum(Data_SDP.Mcv3) AS Pop, Sum(Data_SDP.Depo3) AS Depo, Sum(Data_SDP.DIU3) AS IUD, Sum(Data_SDP.Cn3) AS Condoms
FROM District INNER JOIN Data_SDP ON District.Id = Data_SDP.Ref_District
GROUP BY District.Id, District.Emer, Data_SDP.[Data e fillimit te tremujorit], Format$([Data e fillimit te tremujorit],"\Qq yyyy",0,0)
ORDER BY District.Emer, Data_SDP.[Data e fillimit te tremujorit];



we get the result as attached.

How to modify this query for six months period time.
Regards

[jzwp11]

If you only want to extract data for a six month period from the data returned by the query, you will need to create another query

SELECT *
FROM NameOfTheQueryYouPosted
WHERE Data e fillimit te tremujorit] between [date1] and [date2]

When you run the above query you will be prompted to entere values for [date1] and [date2]. The [date1] and [date2] are known as parameters. Alternatively, you can use an unbound form (with two textboxes ) to supply the dates to the query. This is typically the better approach when you have other people using the database.

The query would look like this if you are using a form


SELECT *
FROM NameOfTheQueryYouPosted
WHERE Data e fillimit te tremujorit] between forms!formname!firstdatecontrolname and forms!formname!seconddatecontrolname

[endri81]

Thank you it works perfect.
Anyway instead of form my user want reports with intervals by six months.
How to replace in query you wrote time intervals where date1 = 4/1/2002 and date2 = 10/1/2002 and also date1= 1/10/2002 date2=1/1/2033 and so on.
User wants all intervals in the same report.
Regards

[jzwp11]

date1 = 4/1/2002 and date2 = 10/1/2002 and also date1= 1/10/2002 date2=1/1/2033 and so on

I'm not sure what you meant by the above. Assuming your dates are mm/dd/yyyy the first set is from April 1, 2002 until October 1, 2002 whereas the second period I assume is supposed to be October 1, 2002 to April 1, 2003. First, you cannot have October 1st fall into both periods, nor April 1st. I assume the periods you want are as follows:

April 1, 2002 through September 30, 2002 (first half of fiscal year)
and
October 1, 2002 through March 31, 2003 (second half of fiscal year)

In a report you should be able to set up a group based on your date field. You can customize that footer to group by every 6 months. That will work if your first quarter starts at the beginning of the year. Now if your year begins at a different time such as April 1st (or the beginning of the second calendar quarter) as in the case of some fiscal years which I believe is what you are after, then that report functionality will not work, or at least I could not figure out how to adjust the starting date of the period.

But, we should able to do it using a calculated field in a query. Once we calculate it, we can bring that into the report and group by it.

I would actually create 2 calculated fields in the query. The first is to pull the calendar quarter in which the date ([Data e fillimit te tremujorit]) falls. I would then use that field to calculate in which half of the year (i.e. which 6 month period) the date falls. I will call April 1st through September 30 as the first half of the year or 1yyyy. I will then assume that October 1st through March 31st (of the next year) will be the second half or 2yyyy. The yyyy in both cases will be in reference to the year in which the fiscal year starts.

SELECT your fields..., datepart("q",mydate) & year(mydate) as PeriodQuarter,clng(IIF(left(PeriodQuarter,1) in (2,3),"1" & year(mydate),iif(left(periodquarter,1)=4,"2" & year(mydate), "2" & year(mydate)-1))) as PeriodSemiAnnual
FROM ...

You would substitute your date field [Data e fillimit te tremujorit] in place of the mydate field above.

You could use your Format$([Data e fillimit te tremujorit],"\Qq yyyy",0,0) field for the quarter but you would have to pull the quarter number after the Q which would require you to use the mid() function instead of the left() function.

[endri81]

Thanxxxxxxxxxxxxxxxxxxxxxxxxx

Working great.
The result is attached.
Is there now any modification to add rows by two so I can get the results of Low Dose fields.... for 6 months?
Regards

[jzwp11]

Is there now any modification to add rows by two so I can get the results of Low Dose fields.... for 6 months?

I'm not sure I understand what you want. Could you provide an example of starting data and what you want as the desired output?

Are you wanting to add up the low dose, Pop, depo fields for the two quarters in each 6 month period?

If so you can do that in a report, you just need to group by the semi-annual period field that we created in the query.

[endri81]

I need to create a report of city name i.e Berat , PeriodSemiAnnual i.e First 2002 (12002 in the table) and other data by summing rows where the PeriodSemiAnnual is the same.
For example first 2 rows of report should be :

Low Dose Pop Depo IUD Condoms
Berat First 2002 1147 36 133 102 2875
Berat Second 2002 992 20 113 68 7050

Regards

[jzwp11]

In the report you will need to set up 2 grouping levels one on the city field and the other on the PeriodSemiAnnual, in the footer of the PeriodSemiAnnual you would have controls for summing. If you want to sum by city as well, then you would need controls in the city footer as well.

[endri81]

That works perfect my friend.
You are great.
Can you please explain me the logic of changes I have to do for 9month period and one year period.
Best regards

[jzwp11]

If you want to do a calendar year, you would just need to group using the year of your date field: year([Data e fillimit te tremujorit]). However, if you want to do a fiscal year such as we discussed before, we have already defined the 2 six month periods in the year, so just group by the year portion of the semiannual period. You would first convert the number to text using the cstr() function and the use the right() function to pull the year. You can do this as a new field in the query we worked on previously.

As to the 9 month period, that would be more difficult since two 9-month periods would span either 2 different calendar year or 2 different fiscal years. I cannot think of a way to do that with simple expressions in a query.

[endri81]

Please if it is possible to help me with some more indications because I am really not very familiar with this kind of queries.
Regards

[jzwp11]

Modifying the expression I presented earlier, you can get the year pertaining to the fiscal year April 1st through March 31st using the following:

clng(IIF(left(PeriodQuarter,1) in (2,3,4), year(mydate), year(mydate)-1)) as FiscalYear

You would then group on that in your report.

[endri81]

Working great.
About 9months reports nothing we can arrange?
Thx a lot for your support.

[jzwp11]

You're welcome.

For the 9-month report, a custom Visual Basic for Application function may be necessary to create a period designation similar to what I have been doing with the expressions. You would have to find or define the starting month based on something or just assume the first period starts with the month/year of the first record in the dataset. If you had to do it manually, how would you pick the start date of the first period?

[endri81]

Thank you for the fast answer.
Basically I would calculate manually like that :
1/4/2002 - 31/12/2002
1/1/2003-30/9/2003
1/10/2003-30/6/2004
1/7/2004 -31/3/2005
etc
Regards