fonction d'agrégat personnalisée

[rstck]

Bonjour,

j'ai parcouru pas mal de discussions et l'aide Access, y compris l'aide en ligne, mais je ne trouve pas de réponse à ma question : y a-t-il moyen d'écrire des fonctions d'agrégation personnalisées pour l'utiliser dans des requêtes écrites en SQL et interprétées par Access ? Je ne sais pas si j'ai bien formulé ma question... Je ne sais pas si écrire une fonction en VBA dans un module est possible, si écrire une fonction en SQL est possible, ou autre chose.

Si je travaillais sous MS SQL Server 2005, la solution des agrégats CLR user-defined serait visiblement bonne pour moi. Existe-t-il des technologies qui s'appliquent à ACCESS ? http://msdn.microsoft.com/fr-fr/libr...=vs.80%29.aspx

J'ai trouvé dans un post datant de 2006 où on disait que SQL Server 2005 était le seul SGBD qui proposait cette fonctionnalité...

Si vous avez la réponse / une partie de la réponse, merci d'avance !! Je continue les recherches de mon côté...

[jpcheck]

Salut,

si tu souhaites savoir si on peut appliquer une fonction développée en VBA à un champ dans une requete Access, la réponse est oui

Typiquement en déclarant une fonction de portée Public comme

Code :

1
2
3
Public Function Piou(strInput As String) As Double
Piou = Len(strInput)
End Function

Piou peut être appelée dans une requête SQL Access

Code SQL :

SELECT ChampA, Piou(ChampA) AS Pioupi FROM MaTable GROUP BY ChampA;

[rstck]

Bonjour, merci pour ta réponse JPCheck.

ok merci pour l'éclaircissement. Ta fonction Piou prend un string en argument. Quand on lance ta requête, on GROUP BY ChampA, on se retrouve avec un argument unique par enregistrement pour ChampA et utiliser Piou a un sens. Mais je pensais plutôt au cas où on fait un GROUP BY ChampA et on souhaite, par exemple, calculer SUM(ChampB), où ChampB serait du bon type. Ca marcherait aussi pour Piou si ChampB est un champ de type string ?

Code :

SELECT ChampA, Piou(ChampB) AS Pioupi FROM MaTable GROUP BY ChampA;

Cette requête fonctionnerait ? (je n'ai pas les moyens de tester pour le moment, sorry). A supposer qu'on change les traitements de Piou pour qu'elle fonctionne sur une "population" de données.

[rstck]

Bonjour,

je peux donner une précision supplémentaire : à l'édition d'une requête, lorsqu'on utilise le générateur d'expression Access, on peut 'browser' les expressions toutes prêtes à l'usage par les trois fenêtres "éléments d'expression", "catégories d'expressions" et "valeurs d'expression".

Or c'est dans 'Fonctions > fonctions intégrées' puis 'Regroupement SQL' qu'on trouve des fonctions qui peuvent être utilisées comme au-dessus. Qu'on-t-elles de spécial ? Peut-on voir comment elles sont codées ? ...

J'ai comme la sensation que la réponse à ma question est 'non, pas en VBA' mais je continue de chercher les infos.
A plus tard !

[rstck]

En imaginant que je sache que je veuille faire une opération sur la population de [ChampB] de la table [MaTable] après un regroupement sur [ChampA], je peux écrire une fonction, sorte de sous-requête, qui fait ce qu'il faut. Je me suis largement inspiré d'un morceau de code trouvé sur la FAQ pour écrire :

Code :

1
2
SELECT DISTINCT ChampA, Agreg(ChampA) AS AgregChampB
FROM MaTable;

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Public Function Agreg(ChampA AS type_ChampA) AS type_a_choisir_selon_nature_operations
Dim rst As Recordset
SQL = "SELECT ChampB FROM MaTable WHERE ChampA=" & ChampA
Set rst = CurrentDb.OpenRecordset(SQL)
'variable construisant le résultat de la fonction
res = null
'opération sur les différents enregistrement
While Not rst.EOF
    ' 
    ' opérations
    ' 
    rst.MoveNext
Wend
Agreg = res
'libere la mémoire
Set rst = Nothing
End Function

Dans cette solution, on n'utilise pas l'instruction GROUP BY mais SELECT DISTINCT. Cette fonction est à récrire dès qu'on change le champ cible, donc dès qu'on change de source de données.

[jpcheck]

salut,

Envoyé par rstck

Code :

SELECT ChampA, Piou(ChampB) AS Pioupi FROM MaTable GROUP BY ChampA;

Cette requête fonctionnerait ?

Elle est fonctionnelle ! (rire glauque à la frankenstein )

Il faut savoir quelle question tu nous poses exactement vis à vis de groupe by

[rstck]

Ok désolé, mon vocabulaire n'est peut-être pas adapté... quand tu dis qu'elle est fonctionnelle, tu joues sur les mots ? Parce qu'Access refuse de la faire fonctionner : "Vous avez essayé d'exécuter une requête ne comprenant pas l'expression spécifique Piou([ChampB]) comme une partie de la fonction d'agrégat"

Je reformule ma question : dans Access il y a une petite dizaine de fonctions intégrées qui peuvent faire qu'une requête comme ça rende un résultat :

Code :

SELECT ChampA, CALCUL(ChampB) AS ChampB_CALCUL FROM MaTable GROUP BY ChampA;

où CALCUL fait des opérations sur un ensemble de valeurs de ChampB pour lesquelles la valeur de ChampA est identique. Il y a SUM, MOYENNE, etc. Peut-on en écrire une nous-même (genre : CONCATENE) ?

[jpcheck]

Dans une base test, ca marche très bien

Donc oui la fonction est possible

[rstck]

tiens tiens , je vais refaire des essais de mon côté alors...
juste pour être sûr, tu proposais

Code :

SELECT ChampA, Piou(ChampA) ... GROUP BY ChampA

ça, ça fonctionne nickel ; je veux faire :

Code :

SELECT ChampA, Piou(ChampB) ... GROUP BY ChampA

merci pour tes réponses JPCheck!

[jpcheck]

auquel cas il faut effectivement faire un GROUP BY aussi sur ton second champs

[rstck]

je crois que dans ce cas; le résultat n'est pas le même ; je pense que ça va renvoyer possiblement plusieurs lignes...
Si MaTable est, par exemple ChampA (integer) | ChampB (string) :

1 | a
1 | ab
1 | ad
2 | a
2 | a
2 | a

Si je fais ce que tu dis, la requête renvoie

1 | 1
1 | 2
2 | 1

Mais je souhaite obtenir quelque chose 'du genre' :
1 | Piou(a,ab,ad)
2 | Piou(a,a,a)
en tout cas un tableau de données à deux lignes.

[jpcheck]

Ah, mais s'il s'agit de concaténer les lignes, on passe directement par une solution proposée dans la
http://access.developpez.com/sources...QLLigneColonne

[rstck]

merci pour le lien, je m'en suis servi ; j'ai ajouté en arguments
* le nom de la source de données
* les champs (la clé de regroupement, le champ à concaténer)
l'idéal serait qu'on puisse 'lire' la donnée FROM de la requête depuis laquelle la fonction est appelée :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Public Function ConcateneChamp(DataName As String, ChpA As String, ChpB As String, ChpAVal As Variant) As Variant
 
Dim SQL As String
Dim rst As DAO.Recordset
ConcateneChamp = ""
 
'Ouvre le jeu de données où ChpA a la bonne valeur
SQL = "SELECT " & "[" & ChpB & "]" & " FROM " & DataName & " WHERE " & "[" & ChpA & "]" & "=" & Chr(34) & ChpAVal & Chr(34)
Set rst = CurrentDb.OpenRecordset(SQL)
 
'Concatene les différents enregistrement
While Not rst.EOF
    ConcateneChamp = ConcateneChamp & rst.Fields(ChpB).Value & " "
    rst.MoveNext
Wend
'Enleve le dernier espace
ConcateneChamp = Left(ConcateneChamp, Len(ConcateneChamp) - 1)
'libere la mémoire
Set rst = Nothing
End Function

le résultat de la requête

Code :

1
2
3
SELECT ChpA, ConcateneChamp("MaTable","ChpA","ChpB",ChpA)
FROM MaTable
GROUP BY ChpA

est bien
1 | a ab ad
2 | a a a

il manque la gestion des erreurs etc. et les pbs liés au types.

Mon rêve ce serait de pouvoir écrire, pour le même résultat, un truc comme ça :

Code :

1
2
3
SELECT ChpA, ConcateneChamp(ChpB)
FROM MaTable
GROUP BY ChpA