Crear consulta paso a través con postgresql

Primero que todo lo felicito Hernán por utilizar PostgreSQL como Back End, es un gran acierto. La instrucción que explican le sobra un paréntesis en And (Year(fecha)<2020 y NO es la respuesta porque la información ya está agrupada por idgrupo y fecha, lo que solicitan es "... obtener en una columna el monto del siguiente período de acuerdo con el grupo". Asumo por esto se hace en PostgreSQL por la dificultad.

Lo primero que debe realizar es hacer la consulta en PostgreSQL y probarla, si es correcta se copia para insertarla en el código de VBA.

CONSULTA EN POSTGRESQL

Explicación de la consulta en PostgreSQL:

Para agrupar por año es necesario obtener el año del campo fecha, esto lo hago con la función extract(year from fecha). Retorna 2018, 2019 etc

LEAD proporciona acceso a una fila que sigue a la fila actual en un desplazamiento físico especificado

OVER determina exactamente cómo se dividen las filas de la consulta para que las procese la función de ventana.

PARTITION BY pertenece a la categoría Funciones de ventana. Las funciones de ventana de PostgreSQL son las que son capaces de realizar cálculos que abarcan varias filas de una columna, pero no todas las filas.

ORDER BY permite ordenar por el año, igualmente se debe convertir el campo fecha. Se había podido crear una función en PostgreSQL para pasar la fecha como parámetro y obtener el año.

RESULTADO EN POSTGRESQL

AHORA EN ACCESS

FORMULARIO

Private Sub btn_2_Click()
 Dim strQuery As String
 strQuery = "qryComparaPorGruposPeriodos"
 If (Not (IsNull(Me.cboDesde) And IsNull(Me.cboHasta))) And Me.cboDesde < Me.cboHasta Then
  Call CreaQueryComparaPorGruposPeriodos(strQuery, Val(Me.cboDesde), Val(Me.cboHasta))
  DoCmd.OpenQuery strQuery
 End If
End Sub

CÓDIGO DE LA FUNCIÓN

Public Function CreaQueryComparaPorGruposPeriodos(SPTQueryName As String, intDesde As Integer, intHasta As Integer)
'En este ejemplo se utiliza la función para obtener en una columna
'el monto del año siguiente en un rango de años por grupos
'Requiere referenciar a:
'                       Microsfot ActiveX Data Objectc 6.1 Library
'                       Microsfot ADO Ext. 6.0 for DLL and Security
'Fuente:
'    Microsoft
'Adpatación para PostgreSQL
'Eduardo Pérez Fernández
'Fecha: 13/09/2022
'Parametros:
'               SPTQueryName  --> Nombre de la consulta a crear
'               intDesde ---> Año inicial
'               intHasta ---> Año final
 On Error GoTo hay_Error
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strSQl As String
Dim tB As DAO.TableDef
Dim MyQueryDef As QueryDef
With CurrentDb
  For Each MyQueryDef In CurrentDb.QueryDefs
   If MyQueryDef.Name = SPTQueryName Then
   .QueryDefs.Delete (SPTQueryName)
   .QueryDefs.Refresh
   Exit For
   End If
  Next
End With
Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
Set cmd.ActiveConnection = cat.ActiveConnection
  strSQl = "SELECT   idgrupo,extract(year from fecha) as mperiodo " & vbCrLf
  strSQl = strSQl & "           ,   monto" & vbCrLf
  strSQl = strSQl & "           ,  LEAD(monto,1) OVER (PARTITION BY idgrupo     " & vbCrLf
  strSQl = strSQl & "    ORDER BY extract(year " & vbCrLf
  strSQl = strSQl & "        FROM fecha) ) ventas_sgte_periodo " & vbCrLf
  strSQl = strSQl & "        FROM  ventas " & vbCrLf
  strSQl = strSQl & "       WHERE extract(year " & vbCrLf
  strSQl = strSQl & "        FROM fecha) BETWEEN " & intDesde & " AND " & intHasta & ";"
  cmd.CommandText = strSQl
  cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
cmd.Properties _
     ("Jet OLEDB:Pass Through Query Connect String") = _
       "ODBC;DSN=dbconta;"
  cat.Procedures.Append SPTQueryName, cmd
  Set cat = Nothing
  Set cmd = Nothing
hay_Error_Exit:
   Exit Function
hay_Error:
    MsgBox "Ocurrió el error " & Err.Number & vbCrLf & vbCrLf & Err.Description, vbCritical, "Error..."
    Resume hay_Error_Exit
End Function

Y no dejará de resultar quien opine que no es eficiente utilizar PostgreSQL con Access en bases de datos "pequeñas", supuestamente por el rendimiento.  

... Aunque no me sirven las funciones de dominio de Access, porque no son tablas vinculadas, también quisiera conocer cómo se puede hacer la consulta en Access.

En Access no se utilizarían funciones de dominio, se utilizaría una consulta de datos agrupados, se agrupa por año e idgrupo y se suma el monto:

SELECT idgrupo, Year(fecha) AS Año, Sum(monto) AS Resultado FROM Tabla0001 GROUP BY Year(fecha),idgrupo HAVING Year(fecha)>=2018 And (Year(fecha)<2020

Antes de nada: mis disculpas por no haber leído el hilo con atención y completo, la respuesta correcta para obtener ese resultado en Access es esta:

SELECT Grupo1.Idgrupo, Grupo1.Año, Grupo1.mperiodo, Grupo2.ventasSigPeriodo
FROM (SELECT Year([Fecha]) AS Año, Tabla4.Idgrupo, Tabla4.monto AS mperiodo
FROM Tabla4) as Grupo1  LEFT JOIN (SELECT Year([Fecha])-1 AS año2, Tabla4.Idgrupo, Tabla4.Monto AS ventasSigPeriodo
FROM Tabla4) as Grupo2  ON (Grupo1.Año = Grupo2.año2) AND (Grupo1.Idgrupo = Grupo2.Idgrupo)
ORDER BY Grupo1. Idgrupo, Grupo1. Año;

Se parte de un conjunto de datos (tabla4) que como tal conjunto de datos puede ser local o externo, sea en PosgreSQL, SqlServer, MySQl o MariaDb ... o cualquier otro, simplemente hace falta tener acceso a los datos para tratarlos.