Query modification

[jzwp11]

The only thing I can think of is to create a table called tbl9MoStart with a field called StartDate. Put in the starting date you want 1/4/2002 (dd/mm/yyyy) in the StartDate field (just the one record in this table). You can then reference that value in an expression in your query to give you the 9 month period. That expression would look like this:



INT(DateDiff("m",DLookUp("StartDate","tbl9MoStart" ),[Data e fillimit te tremujorit])/9)+1 AS 9MoPeriod

You would then group on this field in your report.

[endri81]

You are a genius and saved my day.
Regards

[endri81]

Maybe this one can be adapted similarly for 6,9 period?

SELECT District.Id, District.Emer, [low dose]/15 AS [Low dose CYP], [pop]/15 AS [Pop CYP], [depo]/4 AS [Injectable CYP], [IUD]*3.5 AS [IUD CYP], [Condoms]/120 AS [Condoms CYP], [Low dose CYP]+[Pop CYP]+[Injectable CYP]+[IUD CYP]+[Condoms CYP] AS CYP, [Pop District].Nr_Pop, [nr_pop]/6 AS WRA, [CYP]*4/[WRA] AS CYP_WRA
FROM [Pop District] RIGHT JOIN (Konsumi RIGHT JOIN District ON Konsumi.Id = District.Id) ON [Pop District].Ref_dis = District.Id;

[jzwp11]

You need a date field, I cannot tell from your field names if one of the fields holds date information.

[endri81]

Actually when I run this query it asks me to enter the beginning date of 3months period and then display attached 1.jpeg when I enter for example 1/3/2002.
The schema for this query is as 2.jpeg
How to modify for 6,9 months.
Please help
Regards

[jzwp11]

I cannot see any date fields nor do I see any parameters specified. I am guessing that the query you posted earlier is built from another query

SELECT District.Id, District.Emer, [low dose]/15 AS [Low dose CYP], [pop]/15 AS [Pop CYP], [depo]/4 AS [Injectable CYP], [IUD]*3.5 AS [IUD CYP], [Condoms]/120 AS [Condoms CYP], [Low dose CYP]+[Pop CYP]+[Injectable CYP]+[IUD CYP]+[Condoms CYP] AS CYP, [Pop District].Nr_Pop, [nr_pop]/6 AS WRA, [CYP]*4/[WRA] AS CYP_WRA
FROM [Pop District] RIGHT JOIN (Konsumi RIGHT JOIN District ON Konsumi.Id = District.Id) ON [Pop District].Ref_dis = District.Id;

Are the following queries or tables: Pop District, Konsumi, District? You will have to look at each if a query and see if the date field & the parameters are in one of those queries.

[endri81]

They are all queries.

District

SELECT DISTINCTROW District_all.Id, District_all.Emer, District_all.Ref_prefekture, District_all.JSI_Program, District_all.Aktive
FROM District_all
WHERE (((District_all.Aktive)=-1))
ORDER BY District_all.Emer;

Pop District

SELECT District_pop.Ref_dis, District_pop.Nr_Pop
FROM [District pop descending date] INNER JOIN District_pop ON ([District pop descending date].Ref_dis = District_pop.Ref_dis) AND ([District pop descending date].MaxOfDate_Pop = District_pop.Date_Pop);

Konsumi

SELECT District.Id, District.Emer, 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
WHERE (((Data_SDP.[Data e fillimit te tremujorit])=[forms]![raporte]![Jepni Daten e fillimit te tremujorit]))
GROUP BY District.Id, District.Emer
ORDER BY District.Emer;

[jzwp11]

It looks like the Konsumi query is referencing a parameter supplied by a control on a form. Also, the Pop District query references the query [District pop descending date], you'll have to check that query for any date criteria as well.

[endri81]

Hi
Thx for your help
How to implement in this query :

SELECT District.Id, District.Emer, 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, datepart("q",datatremujori) & year(datatremujori) AS PeriodQuarter, clng(IIf(left(PeriodQuarter,1) In (2,3),"1" & year(datatremujori),IIf(left(periodquarter,1)=4,"2 " & year(datatremujori),"2" & year(datatremujori)-1))) AS PeriodSemiAnnual,
FROM District INNER JOIN Data_SDP ON District.Id=Data_SDP.Ref_District
WHERE (((Data_SDP.[Data e fillimit te tremujorit])=forms!raporte![Jepni Daten e fillimit te tremujorit]))
GROUP BY District.Id, District.Emer
ORDER BY District.Emer;

your modification :


INT(DateDiff("m",DLookUp("StartDate","tbl9MoStart" ),[Data e fillimit te tremujorit])/9)+1 AS 9MoPeriod

Trying different variants still getting error.
Please help

[jzwp11]

SELECT District.Id, District.Emer, 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
WHERE (((Data_SDP.[Data e fillimit te tremujorit])=forms!raporte![Jepni Daten e fillimit te tremujorit]))
GROUP BY District.Id, District.Emer
ORDER BY District.Emer;


I have removed the expression fields from the query. What you show above is an aggregate query (GROUP BY clause). The WHERE clause suggests that you are using data for just 1 date as specified on the raporte form. If that is the case you will only have 1 period. Is that really what you want?

[endri81]

I just need to apply this query for 9months fiscal.
Thank you.

[jzwp11]

I do not think the query you are starting with is correct based on what you want to do. I would create the following query

query name: qrySummaryDataByDate
SELECT District.Id, District.Emer, 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, Data_SDP.[Data e fillimit te tremujorit]
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]
ORDER BY District.Emer;


Then create a second query based on the above that includes the 9MoPeriod field

SELECT qrySummaryDateByDate.ID, qrySummaryDateByDate.Emer, qrySummaryDateByDate.[Low Dose], qrySummaryDateByDate.Depo,qrySummaryDateByDate.IUD , qrySummaryDateByDate.Condoms, qrySummaryDateByDate.[Data e fillimit te tremujorit],INT(DateDiff("m",DLookUp("StartDate","tbl9MoStart " ),[Data e fillimit te tremujorit])/9)+1 AS 9MoPeriod
FROM qrySummaryDataByDate

[endri81]

Thank you.
The first query is working perfect but the second keep asking me for all the fields.
Regards

[jzwp11]

It sounds like you spelled something incorrectly.