Modify query (1 Viewer)

[endri81]

Hi
How can I modify this query showing the results of 3 months data :

SELECT District.Id, District.Name, Data_SDP.[First Date of 3month period], Format$([First Date of 3month period],"\Qq yyyy",0,0) AS TreMonth, Sum(Data_SDP.Mg3) AS [Low Dose], Sum(Data_SDP.Mcv3) AS Pop, Sum(Data_SDP.Depo3) AS Depot, Sum(Data_SDP.DIU3) AS IUD, Sum(Data_SDP.Cn3) AS Apples
FROM District INNER JOIN Data_SDP ON District.Id=Data_SDP.Ref_District
GROUP BY District.Id, District.Name, Data_SDP.[First Date of 3month period], Format$([First Date of 3month period],"\Qq yyyy",0,0)
ORDER BY District.Emer, Data_SDP.[First Date of 3month period];

to a query showing 6month period date.

Regards

 

[tehNellie]

Difficult to say from the query itself as there is no current filtering in place and the column name from Data_SDP suggests that it's a 3 month summary?.

can we assume that you currently get in your output something along the lines of:

Id Name dateof3monthPeriod  sum(something)
1   Fred     01/01/2011           124
1   Fred     01/04/2011           234

?

In which case an iif() could do it.

SELECT District.Id, 
District.Name, 
Year([First Date of 3month period) AS year,
iif([first date of 3month period]) < "#01/07/" & Year([First Date of 3month period) & "#", "First", "Second") as HalfYear,
Sum(Data_SDP.Mg3) AS [Low Dose], 
Sum(Data_SDP.Mcv3) AS Pop, 
Sum(Data_SDP.Depo3) AS Depot, 
Sum(Data_SDP.DIU3) AS IUD, 
Sum(Data_SDP.Cn3) AS Apples
FROM District 
INNER JOIN Data_SDP ON 
  District.Id=Data_SDP.Ref_District
GROUP BY District.Id, 
District.Name, 
Year([First Date of 3month period]),
iif([first date of 3month period]) < "#01/07/" & Year([First Date of 3month period) & "#", "First", "Second")
ORDER BY District.name, 
Year([First Date of 3month period]),
iif([first date of 3month period]) < "#01/07/" & Year([First Date of 3month period) & "#", "First", "Second");

So if the date is less than 1st of July (UK date format in the example) it's the first period of the year, otherwise it's the second. Group and sum on that basis.

 

[endri81]

Thank you,
Anyway the query still not working for me.
I attached 2 pic one of tables I am using and second of data query I posted print.
Can u give me some further orientation pls.
Regards