Access - Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Pos: 112

Val: 24

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 02/09/2019 05:47:06

Estimados,
Mi consulta es la siguiente:

Necesito un módulo en ACCESS que calcule el promedio de los n primeros registros de un campo (campo "Nota") y luego cree una tabla nueva y copie dichos registros en ella.

Consulta1
Nombre Alumno FechaExámen Nota
AAAA 01/01/2019 5,5
AAAA 02/01/2019 5,7
AAAA 03/01/2019 5,9
AAAA 04/01/2019 6,1
AAAA 05/01/2019 6,3
AAAA 06/01/2019 6,5
AAAA 07/01/2019 6,7
BBBB 23/02/2019 4
BBBB 23/02/2019 4,3
BBBB 25/02/2019 4,6
BBBB 26/02/2019 4,9
BBBB 27/02/2019 5,2
BBBB 28/02/2019 5,5
BBBB 01/03/2019 5,8

Muchas gracias por vuestra ayuda.

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Anonimo (3397 intervenciones) el 02/09/2019 09:54:00

El enfoque no me parece el correcto:

Paso uno ==> crear la tabla destino
Paso dos ==> crear una consulta que ordene por alumno (las fechas de los elementos carecen de sentido para el calculo)
Paso tres ==> con ese origen de datos (la consulta anterior) abrir un recordset de solo lectura
Paso cuatro ==> recorrer ese recordset y (registro a registro) sumar el campo y contar el registro (dos variables independientes)
Paso cinco ==> al detectar el cambio de alumno (una tercera variable), calcular el dato (simple división) y añadir ese registro a la nueva tabla
Paso seis ==> reiniciar las variables (suma, cuenta, alumno) para comenzar el nuevo ciclo hasta el final de los datos.

Nota:
La inserción de datos se puede hacer directamente abriendo el destino como otro recordset o bien utilizando una simple SQL de inserción.

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 112

Val: 24

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 02/09/2019 16:18:33

Agradezco la repuesta de Anonimo, es muy orientadora en términos generales.

Me parece que comencé al revés, mis disculpas.
Lo primero que debo contarles es que estoy recién incursionando en el tema de la
programación en DAO, en ACCESS, y por lo mismo necesito una ayuda más
específica al respecto, es decir, el módulo que debería usar para obtener el
promedio de los n primeros lugares de un campo, en una consulta dada.

Alguien que me ayude con el módulo access, por favor, o que me den
ciertas trazas de cómo debo orientarme para hacerlo.

Para aclarar más todavía con un ejemplo, (ya que el que inserté en el chat
anterior no se aprecia bien, al parecer por un tema de formateo), adjunto archivo.

Desde ya muy agradecido

Promedio-3-primeras-notas.rar(7,1 KB)

Valora esta respuesta

Comentar

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Anonimo (3397 intervenciones) el 02/09/2019 17:13:21

No encuentro ninguna referencia a un post anterior (al menos con ese nombre de participante), presumo que lo has planteado en otro foro y que te han dado alguna solución que no cumple con las expectativas deseadas.

Honradamente pienso que en donde has iniciado el hilo y en el que han propuesto 'algo' que representa un primer paso, se debería continuar para logra el segundo y tras ello el éxito,

Para aunar soluciones de diferentes orígenes hay que disponer de recursos que a día actual parecen lejanos.

Access tiene algunas funciones matemáticas (incluso accesibles mediante el 'generador de expresiones') que pueden solucionar alguna que otra dificultad en la generación de la sintaxis correcta.

Valora esta respuesta

Comentar

Pos: 7

Val: 467

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Francisco (212 intervenciones) el 02/09/2019 17:26:11

Hola

Lo primero es crear una consulta que contendra esta sentencia

SELECT [Nombre Alumno], AVG(Nota) Promedio
FROM alumnos
GROUP BY [Nombre Alumno]
ORDER BY AVG(Nota) DESC
LIMIT 3;

Al ejecutar la consulta debe mostrar los 3 primeros promedios ordenados de mayor a menor

Esta consulta la guardas y la puedes usar luego en un formulario o reporte para que la muestre

Si lo quieres programar se haria asi:

Dim rst As recordset

Dim miSQL As String

Dim nLimit As Integer

'Se establecen cuantos registros desea devolver

nLimit = 3

miSQL = "SELECT [Nombre Alumno], AVG(Nota) Promedio FROM alumnos GROUP BY [Nombre Alumno] ORDER BY AVG(Nota) DESC

LIMIT " & nLimit

'La tabla alumnos.se encuentra en la BD actual

Set rst = Application.CurrentDB.OpenRecordset(miSQL)

'Hay registros?

If  rst.Count() > 0 Then

   'Mientras existan registros

   Do While Not rst.EOF()

            Debug.Print  rst![Nombre Alumno] & "-> " rst!Promedio

            'Siguiente registro

            rst.next

   Loop

Else

  Debug.Print "Alumnos no tienen notas para calcular promedios"

End If

'Cerramos el recordset

rst.Close

Set rst = Nothing

Esto es bastante simple, sin embargo lo puedes adaptar para que funcione en un formulario o reporte
Tienes que conocer sobre eventos ya que Access se basa en el manejo de eventos sobre controles como formularios, reportes, botones etc.

Saludos

Valora esta respuesta

Comentar

Pos: 112

Val: 24

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 02/09/2019 17:36:02

Muchas gracias Francisco por tu esclarecedora respuesta.
Eso es justamente lo que estaba buscando, entrar en el tema específico
del programa en DAO. Voy a incorporar el código que me enviaste en mi base
de datos y te comento luego , tal vez me surja una que otra duda, en las
que espero me puedas ayudar.

Valora esta respuesta

Comentar

Pos: 112

Val: 24

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 02/09/2019 21:32:14

Francisco, lo revisé, pero me envía error:
"Error de compilación: No se ha definido sub o function"

Marca la fila donde está la cláusula de sql, específicamente

la palabra LIMIT, parece que no la reconoce, pues no me funciona

ni en la consulta ni en el módulo, es decir que en ambos marca el
mismos error(LIMIT).

Valora esta respuesta

Comentar

Pos: 112

Val: 24

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 03/09/2019 06:09:14

Francisco, o quien pueda ayudarme.
Adjunto archivo de ejemplo, no me resulta lo que he hecho hasta el momento.

Agradeceré mucho la ayuda. Disculpen la insistencia.

Alumnos.rar(23,9 KB)

Valora esta respuesta

Comentar

Pos: 7

Val: 467

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Francisco (212 intervenciones) el 03/09/2019 18:28:21

Hola

Tu funcion corregida para pruebas

Se agrega redondeo en promedio y tratamiento de errores

Llamada desde ventana inmediato

'Test

? PromN("Nota", 3)

? PromN("NotaX", 3)

Funcion mejorada

Public Function PromN(sCampo As String, nLimit As Integer) As Double

    Dim rst As DAO.Recordset

    Dim miSQL As String

On Error GoTo Capturar_error

    miSQL = ""

    miSQL = miSQL & "SELECT TOP " & nLimit

    miSQL = miSQL & " [Nombre Alumno], "

    miSQL = miSQL & " ROUND( AVG(" & sCampo & "), 2) AS Promedio "

    miSQL = miSQL & " FROM Alumnos "

    miSQL = miSQL & " GROUP BY [Nombre Alumno] "

    miSQL = miSQL & " ORDER BY ROUND( AVG( " & sCampo & "), 2) DESC"

    'La tabla alumnos.se encuentra en la BD actual

    Set rst = Application.CurrentDb.OpenRecordset(miSQL)

    'Hay registros?

    'No existe Count(), es RecordCount(), error de mi parte ;-)

    If rst.RecordCount() > 0 Then

        'Mientras existan registros

        Debug.Print "Alumno - > Promedio"

        Do While Not rst.EOF()

            Debug.Print rst![Nombre Alumno] & "  -> "; rst!Promedio

            'Siguiente registro

            rst.MoveNext

        Loop

    Else

        Debug.Print "Alumnos no tienen notas para calcular promedios"

    End If

    'Cerramos el recordset

    rst.Close

    Set rst = Nothing

Salir_procedimiento:

    PromN = 0

    Exit Function

Capturar_error:

    Debug.Print "ERROR(" & Err.Number & ") -> No existe el campo " & sCampo & ". " & Err.Description

    Resume Salir_procedimiento

End Function

Saludos

Valora esta respuesta

Comentar

Pos: 7

Val: 467

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Francisco (212 intervenciones) el 03/09/2019 17:39:23

Hola

Si es cierto , este es el problema cuando Microsoft no respeta standares, el sustituo de LIMIT es TOP

La sentencia original

SELECT TOP 3 [Nombre Alumno], AVG(Nota) Promedio

FROM alumnos

GROUP BY [Nombre Alumno]

ORDER BY AVG(Nota) DESC;

Por codigo

Dim rst As recordset

Dim miSQL As String

Dim nLimit As Integer

'Se establecen cuantos registros desea devolver

nLimit = 3

miSQL = "SELECT TOP " & nLimit & " [Nombre Alumno], AVG(Nota) Promedio FROM alumnos GROUP BY [Nombre Alumno] ORDER BY AVG(Nota) DESC"

'La tabla alumnos.se encuentra en la BD actual

Set rst = Application.CurrentDB.OpenRecordset(miSQL)

'Hay registros?

If  rst.Count() > 0 Then

   'Mientras existan registros

   Do While Not rst.EOF()

            Debug.Print  rst![Nombre Alumno] & "-> " rst!Promedio

            'Siguiente registro

            rst.next

   Loop

Else

  Debug.Print "Alumnos no tienen notas para calcular promedios"

End If

'Cerramos el recordset

rst.Close

Set rst = Nothing

Saludos

Valora esta respuesta

Comentar

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 03/09/2019 18:38:40

Francisco, muchas gracias nuevamente.
Encuentro notable tu labor docente.

Voy a revisarlo en cuanto pueda y te hago algún comentario.

Un abrazo,

Eric

Valora esta respuesta

Comentar

Pos: 112

Val: 24

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 04/09/2019 00:26:19

Estimado Francisco, al correr el módulo que me enviaste, me arroja
los promedios por alumno, pero promedios generales, no de las 3 primeras
notas por alumno.
Adjunto archivo access con las modificaciones que hice.
Espero me puedan ayudar nuevamente.

Saludos,

Eric

Alumnosv2.rar(22,5 KB)

Valora esta respuesta

Comentar

Pos: 7

Val: 467

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Francisco (212 intervenciones) el 05/09/2019 00:49:21

Hola

El problema es que con los datos que tienes no puedes aplicar hasta donde quieres promediar. En otros gestores de base de datos como postgres o SQLServer si puedes aplicar esto la funcion rank() y OVER pero en el SQL de Access no aplica

Lo que debes hacer es agregar una columna llamada orden y aplicar un correlativo a las notas por cada alumno y asi aplicas la sentencia

SELECT

	DISTINCT a.[Nombre Alumno],

	(SELECT AVG(Nota)

		FROM alumnos b

		WHERE b.[Nombre Alumno] = a.[Nombre Alumno] AND b.orden <= 3 ) AS Promedio

FROM alumnos AS a;

Saludos

Valora esta respuesta

Comentar

Pos: 112

Val: 24

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 05/09/2019 04:30:16

Estimado Francisco, !!LO RESOLVÍ!!
Gracias por tu inestimable ayuda.
El código me quedó así:

PromedioN

Private Sub PromediosN3()

Dim dbsN As DAO.Database

Dim rst As DAO.Recordset

Dim rst1 As DAO.Recordset

Dim miSQL As String, miSQL1 As String, NombreAlumno As String, Crit As String

Dim k As Integer

Dim sumaN As Double, n As Integer, Cuenta As Integer, h As Integer, NotaN As Double

n = 3

k = 0

h = 0

sumaN = 0

Set dbsN = CurrentDb

miSQL = "SELECT [Nota], [Nombre Alumno] FROM Alumnos;"

miSQL1 = "SELECT [Nombre Alumno] FROM C_Alumnos;"

Set rst = dbsN.OpenRecordset(miSQL, dbOpenSnapshot)

Set rst1 = dbsN.OpenRecordset(miSQL1, dbOpenSnapshot)

If rst1.RecordCount() > 0 Then

        Do While Not rst1.EOF()

                Crit = "[Nombre Alumno] = " & "'" & rst1![Nombre Alumno] & "'"

                Cuenta1 = rst1.RecordCount()

                NombreAlumno = rst![Nombre Alumno]

                NombreAlumno1 = rst1![Nombre Alumno]

                If Cuenta1 > 0 Then

                   Do While Not rst.EOF()

                   rst.FindFirst "[Nombre Alumno] = " & "'" & rst1![Nombre Alumno] & "'"

                   Cuenta = DCount("[Nombre Alumno]", "Alumnos", "[Nombre Alumno] = " & "'" & rst1![Nombre Alumno] & "'")

                                 If Cuenta < n Then

                                    n = Cuenta

                                 End If

                                     For k = 1 To n

                                              sumaN = sumaN + rst![Nota]

                                              rst.MoveNext

                                     Next k

                                        Exit Do

                   Loop

                                        PromN3 = sumaN / (k - 1)

                                        Debug.Print NombreAlumno1 & "-> " & PromN3

                                        sumaN = 0

                Else

                        PromN3 = 0

                        sumaN = 0

                        Debug.Print NombreAlumno1 & "-> " & PromN3

                End If

                rst1.MoveNext

        Loop

End If

rst.Close

rst1.Close

Set rst = Nothing

Set rst1 = Nothing

End Sub

Adjunto BD, para que le sirva a todo el que la necesite. El código está en el módulo llamado Prom_NPrimeros_V2, que trabaja con la tabla Alumnos y la consulta C_Alumnos, los demás códigos y consultas no son relevantes para lo que buscaba.
Quedo atento a cualquier comentario, observación o mejora que tú o cualquiera estime conveniente formular.
OBSERVACIÓN: Me sobraron algunas variables.

Un abrazo y gracias por este sitio que es de grandiosa ayuda

Alumnosv7.rar(28,5 KB)

Valora esta respuesta

Comentar

Pos: 112

Val: 24

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 05/09/2019 05:46:10

Estimado Francisco, preguntas:
1.- ¿Cómo lo hago para transformar ese mismo código en función y poderla usar desde una consulta?. Si me pudieras
explicar esto paso a paso, te lo agradecería mucho, pues ahí si que estoy perdido.
2.-¿Cómo hago para que con los promedios calculados generar una nueva tabla con los alumnos y sus promediosN?.

Saludos,

Eric

Valora esta respuesta

Comentar

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Anonimo (3397 intervenciones) el 06/09/2019 00:59:29

Sinceramente:
Si el paso final consiste en crear una tabla con los resultados, la idea que propuse la considero vigente, solo le añadiría un detalle mas:

.- una variable para limitar el numero de elementos a promediar y que serviría también para 'limpiar' la tabla de elementos que no cumpliesen ese mínimo (si el mínimo es una condición a cumplir, esto es, no vale con uno o dos han de ser 'n' o mas y a los '+' que sobran se les ignora).

Y si, en y para este caso la fecha seria tenida en cuenta para lograr el orden (primeros <-> últimos)

Valora esta respuesta

Comentar

Pos: 112

Val: 24

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 06/09/2019 01:27:39

Muchas gracias por tu respuesta.
Me podrás ayudar con el tema de transformar a una Public Function
para después usarla en una consulta, y cómo lo podría lograr?.
Puedes señalarme eso paso a paso, por favor?

Valora esta respuesta

Comentar

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Anonimo (3397 intervenciones) el 06/09/2019 14:36:59

Lo que solicitas es muy ambiguo, y además es muy sencillo de implementar si se tiene claro como funcionan las consultas y se planifica a la susodicha función para que devuelva un único resultado (no un conjunto, algo que para este caso seria un desperdicio de recursos).

Para hacer una función publica se necesita proveerla de los datos de entrada (parámetros) indispensables para que las acciones que se le soliciten (apertura de tablas, consultas, sumas, restas ….) puedan definirse con precisión y ello exige un conocimiento del entorno y muy claro lo que se quiere por un simple motivo, cuanto mas concreto y directo sea el accesso a los resultados, mas velocidad tendrá esa aplicación, para hacer aplicaciones que 'piensen' … mejor es que se le indique al usuario que utilice los dedos (para sumar … etc.).

Para una función que devuelva un dato que se utilizaría registro a registro en una consulta lo esencial seria:
.- Indicarle el origen de datos (si por definición NUNCA JAMAS será modificado, puede prescindirse de el, seria una constante)
.- Indicarle que elemento tiene que tomar como referente (este elemento puede ser obtenido del origen de datos de la propia consulta)
.- Indicarle cuantos elementos del subconjunto anterior (origen filtrado por la referencia) utilizara para el calculo.
.- Calcular lo que sea preciso (la suma de los datos/elementos a cuantificar)
.- Si fuera el caso, verificar que se cumplen los mínimos (si se indica promedio de tres valores, que hacer si solo hay cero, uno o dos, si hay tres o mas los 'mas' serian ignorados)
.- El tipo de dato que devolverá la función (texto, numero, fecha o booleano)

Básicamente todo iría en la línea de recibir los parámetros indispensables, en base a ello generar un origen de datos
(una SQL que filtre al conjunto por alumno, ordenado por fecha y con la sentencia TOP que utiliza Access desde hace mas de veinticinco años obtener el numero de elementos solicitados)

Obtenida la SQL y utilizada como origen de datos de un recordset, se puede saber si hay los 'n' elementos o menos gracias al clásico 'RecordCount' (mas no debería porque el limite lo pone el TOP) si no los hay ¿ que desvío se toma?

Y si los hay se recorre el recordset, sumando el UNICO campo que devuelve la consulta (ya se sabe el referente 'alumno' así como el referente 'numero de elementos a cuantificar'), a la suma obtenida se la divide por el numero de elementos -que es un dato conocido obteniendo el promedio buscado.

Se le puede dar forma:
.- en principio puede dar decimales infinitos (incluso periódicas puras) así que se puede recortar a un numero determinado de decimales (devolvería un valor de tipo Double)
.- Lo mismo sin decimales (devolvería u tipo Long)
.- comparado con una tabla de referencias puede devolver desde 'apto/no-apto' a 'por debajo del nivel requerido', 'pasa por los pelos', 'es un cerebrito' , 'lo veo como mi sucesor' … (devolvería un texto)
.- o lo mas simple un simple Sí / No (que se correspondería con un valor booleano)

Personalmente no me gusta el 'copiar y pegar', considero que se viene al foro con el animo de aprender y el movimiento se demuestra …. andando, inténtalo y desde la distancia se pueden corregir los problemas (y los problemas nunca son un fracaso, son los pasos de una nueva experiencia a positivo).

Los que no tienen ganas de caminar y aprender que esperen a quien se lo de bien molido (y aunque no intervenga, les deseo suerte)

Valora esta respuesta

Comentar

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 06/09/2019 15:17:32

Creo que tu respuesta en general es mas bien una llamada de atención, injustuficada y demuestra que no has visto todo el hilo de esta consulta.
Los términos y juicios que haces son ofensivos y violentan el espíritu tu, lamentable que en estos foros haya gente que se creen una suerte de sabios absolutos que pueden juzgar a priori a todo el mundo.
Pesimo comentario el tuyo de copiar y pegar.

Valora esta respuesta

Comentar

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Anonimo (3397 intervenciones) el 06/09/2019 15:57:03

Estas en el mismo pleno derecho a opinar sobre mis post como yo puedo opinar sobre los tuyos

Y si deseas saber en que me baso, creo que si se sigue el hilo desde su inicio, se apreciara que solo aparece algo de intento de solución cuando alguien postea un metodo que a continuación se reutiliza para intentos que no dan el fruto esperado, lo posteado original son simples datos y mas tarde una referencia ¿a un chat ?.

Con la excepción de que no aprecié el 'limite' del numero de registros (por ello desprecie la fecha) el resultado tanto unitario como de conjunto (dato para aunar en una consulta o para crear una tabla) la respuesta era simple y concreta, se utilizaría un único recordset (o el recordsetclone del formulario si fuera el caso) y unas simples variables para obtener el calculo (sin necesitar funciones de promedio que trabajarían con todo el conjunto y ello consumiría recursos y velocidad sin aportar beneficio).

No por utilizar varios recordset, funciones integradas, búsquedas aplicando criterios … etc. se llega a un resultado mas rápidamente o se llega, una buena programación parte de:
.- un esquema previo (y no es un invento mío, hay infinita literatura al respecto)
.- de conocer el entorno (aquí si puede intervenir la ayuda externa ante las dudas)
.- y sobre todo de tener las ideas claras para utilizar el camino mas corto.

Indudablemente eso requiere tiempo (y en el tiempo hay fracasos y éxitos), a eso normalmente se le conoce como experiencia y esa experiencia (al menos a mi) me indica que sin saber 'como funciona' se depende en exceso de la buena voluntad ajena para 'comer pescado hoy' cuando lo ideal es aprender a pescar y comer pescado 'todos los días'.

Yo intento que los demás aprendan a pescar
Lamento mucho que mi forma de compartir la experiencia te moleste al sentirte inferior, pero ese sentimiento es únicamente tuyo, a mi (créeme) no me produce ningún placer, si desease que me aplaudiesen … simplemente daría pescado.

Pareces tener experiencia con recordset, falta un poco del resto (enfocar la meta y utilizar el camino corto)

Valora esta respuesta

Comentar

Pos: 112

Val: 24

Obtener el Promedio de los n primeros registros de una consulta acces usando DAO

Publicado por Eric (12 intervenciones) el 06/09/2019 16:33:39

Que lamentable trenzarse en discusiones bizantinas, habiendo
yo solicitado una ayuda clara y específica de una cuestión
eminentemente técnica.
Vuelvo a pedir ayuda a la comunidad de este foro:

Hay alguien que me pueda orientar cómo logro hacer funcionar
una UDF en una consulta en ACCESS en DAO.
O bien que me indiquen una buena referencia donde encontrar
el tema bien explicado, esto es, paso a paso.
Gracias

Valora esta respuesta

Comentar

Variable no coge el valor de cuadro combinado

Completar una columna vacía de un nuevo mes cargado en la base, con los datos del mes pasado