Modules & VBA :: Write SQL Query And Design Report With Proper Field Positions

I need to display the year, Date, agent name, amount field, note and sub total for each year.I need to write the SQL query code in VBA and i designed the report with proper field positions.

YEAR column: Display Value only once for the FIRST ROW for Maximum value of Date field value for each set of YEAR. If it has only one record, it display that year.SUB TOTAL Column: Display Total Value for Amount field only once for the LAST ROW of each set of year record (Mininum value of year). How to make it available the values for first and last record alone ?

I just wrote my SQL code based on your inputs in my editor..Based on the input from Plog (from this forum), i wrote a code which listed below.

Code:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT CessioneCredito.Anno, CessioneCredito.Data_Movimento, CessioneCredito.Note_Liq_Cessione_Credito, CessioneCredito.Importo, " & _
" Agenzie.Denominazione, DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno]) AS GroupSize,

[Code] ....

While saving it, it shows the code in RED COLOR due to compilation error.As I am writing the query in Me.Recordsource = "SQL query", where i should place the below listed code in the query as per Plog???

Change the Year Control Source to this:
=IIf([GroupPos]=1,[Year])
Change the GroupTotal Control Source to this:
=IIf([GroupPos]=[GroupSize],[GroupTotal])

If this is not possible in Me.recordsource, then its advisable to use recordset like writing in two different queries.

Code:
Dim ds1 As Recordset
Dim ds2 As Recordset
myquery1 = "SELECT CessioneCredito.ID_Agente, CessioneCredito.Data_Movimento, CessioneCredito.Importo, CessioneCredito.Anno, CessioneCredito.Note_Liq_Cessione_Credito, Agenzie.Denominazione
FROM CessioneCredito INNER JOIN Agenzie ON CessioneCredito.ID_Agente=Agenzie.ID_agenzia
WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))"

[code]....