XL 2010 tableau de comptage sans doublon

cathodique · 6 Octobre 2024

Bonjour,

N'étant pas parvenu à résoudre mon problème seul. Je sollicite une aide auprès de la communauté d'XLD.

Sur la feuille résultat vous trouverez 3 petits tableaux, le nombre de lignes est fonction du nombre d’espèces (col6).
Je les ai arrêté à 3 par rapport aux données actuelles de la bd, juste pour illustrer le problème.
Je voudrais travailler avec des variables tableaux parce que la bd aura un nombre très important de lignes. (les colonnes pourraient évoluer aussi).

L'objectif est de compter sans doublon selon des critères:
Critère1 sur les dates, 1er tableau jusqu'à fin de l'année N-1, 2ème tableau année en cours et le 2ème toutes les années.
Critère2 par rapport aux espèces, dans les tableaux résultats ils sont dans la colonne1 à partir de la 2ème ligne et se trouvent en colonne 6 de la bd. Ils faut compte pour chaque espèce selon les critères 3, qui se trouvent en ligne 1 à partir de la 2ème colonne des petits tableaux. les critères de 2 à 7 se trouvent dans la colonne 4 de la bd; de 8 à 9 dans la colonne 13 et la 10 dans la colonne 14.

Pour 8 à 9, le comptage se fait si dans la colonne 4 il y a "Fa"
Pour 10, le comptage se fait si l'année de la colonne 14 correspond à l'année de la colonne 1.

Dans une précédente discussion, @dysorthographie m'avait suggéré une approche dans le genre bataille navale pour récupérer les critères. Très bonne idée, mais je me suis emmêlé les pinceaux.

Je suis parvenu à mon monter mes petits tableaux par code (non joint pour le pas "polluer" le fichier

).
Et, il se peut que mon approche était fausse dès le départ.

En espérant, avoir été clair.

Merci

cathodique · 8 Octobre 2024

klin89 à dit:

Re à tous,

Étant donné la question initiale de cathodique, les remarques pertinentes de job75, les tentatives avortées de Bernard et Staple1600, et les éclaircissements fournis par dysortographie,
ma réponse n'apportera probablement rien de nouveau et est finalement à côté de la plaque.

Mais comme j'ai bossé sur le sujet, je la poste tout de même, elle pourra peut-être être utile à d'autres.

VB:

Option Explicit
Sub test()
    Dim a, b, e, s, v, i As Long, n As Long, dico(1) As Object
    Dim LastYear As Long
    Set dico(0) = CreateObject("Scripting.Dictionary")
    Set dico(1) = CreateObject("Scripting.Dictionary")
    a = Sheets("BD").[a1].CurrentRegion.Value2
    LastYear = Year(Application.Max(Application.Index(a, 0, 1)))
    For Each e In Array(Array("<" & LastYear + 1, "Anterieur_" & LastYear + 1), Array("<" & LastYear, "Anterieur_" & LastYear), Array("=" & LastYear, "Annee_" & LastYear))
        For i = 2 To UBound(a, 1)
            If Evaluate("Year(" & a(i, 1) & ")" & e(0)) Then
                If Not dico(1).exists(a(i, 4)) Then dico(1)(a(i, 4)) = dico(1).Count + 2
                If Not dico(0).exists(a(i, 6)) Then
                    Set dico(0)(a(i, 6)) = CreateObject("Scripting.Dictionary")
                End If
                dico(0)(a(i, 6))(a(i, 4)) = dico(0)(a(i, 6))(a(i, 4)) + 1
            End If
        Next
        For i = 2 To UBound(a, 1)
            If Evaluate("Year(" & a(i, 1) & ")" & e(0)) Then
                If Not dico(1).exists(a(i, 13)) Then dico(1)(a(i, 13)) = dico(1).Count + 2
                If a(i, 4) = "Fa" Then
                    dico(0)(a(i, 6))(a(i, 13)) = dico(0)(a(i, 6))(a(i, 13)) + 1
                End If
            End If
        Next
        If Not dico(1).exists("Décès") Then dico(1)("Décès") = dico(1).Count + 2
        For i = 2 To UBound(a, 1)
            If Evaluate("Year(" & a(i, 1) & ")" & e(0)) Then
                If Not IsEmpty(a(i, 14)) Then
                    If Year(a(i, 14)) = Year(a(i, 1)) Then
                        dico(0)(a(i, 6))("Décès") = dico(0)(a(i, 6))("Décès") + 1
                    End If
                End If
            End If
        Next
        ReDim b(1 To dico(0).Count + 1, 1 To dico(1).Count + 1)
        n = 0
        For Each s In dico(0)
            n = n + 1: b(n, 1) = s
            For Each v In dico(0)(s)
                b(n, dico(1)(v)) = dico(0)(s)(v)
            Next
        Next
        Application.ScreenUpdating = False
        If Not Evaluate("isref('" & e(1) & "'!a1)") Then
            Sheets.Add(after:=Sheets(Sheets.Count)).Name = e(1)
        End If
        With Sheets(e(1)).[a1]
            .CurrentRegion.Clear
            .Value = "Espèces"
            .Range("b1").Resize(, dico(1).Count) = dico(1).keys
            .Range("a2").Resize(dico(0).Count, dico(1).Count + 1) = b
            With .CurrentRegion
                .Font.Name = "calibri"
                .Font.Size = 10
                .VerticalAlignment = xlCenter
                .Borders(xlInsideVertical).Weight = xlThin
                .BorderAround Weight:=xlThin
                With .Rows(1)
                    .HorizontalAlignment = xlCenter
                    .Font.Size = 11
                    .BorderAround Weight:=xlThin
                    .Interior.Color = 9420794
                End With
                .Columns.ColumnWidth = 14
            End With
        End With
        Erase b
        dico(0).RemoveAll: dico(1).RemoveAll
    Next
    Set dico(0) = Nothing: Set dico(1) = Nothing
    Application.ScreenUpdating = True
End Sub

Me reste à examiner l'analyse apportée par dysortographie et à en comprendre le cheminement
Pas simple quand même

klin89

Merci beaucoup.
Je m'excuse si je n'ai pas été à la hauteur pour exposer mon problème.
et désolé d'avoir peut-être causé des frustrations à certain.

dysorthographie · 8 Octobre 2024

a oui désolé j'ai inversé les colonne dan ma requête mais si tu remets en phase les entête de colonnes ?

	Espèce	Cd	Fa	Ad	ChFa	RtAd	Adoptable	Non Adoptable	A Déterminer	Décès
2010	Chat	0	1	0	0	0	0	0	0	0
2011	Chien	1	0	0	0	0	0	0	0	1
2015	Chat	0	1	1	0	0	0	0	0	0
2016	Chat	0	2	0	0	0	0	0	0	0
2017	Chat	0	2	1	0	0	0	0	0	1
2018	Chien	0	1	0	0	0	0	0	0	0
2019	Chat	0	6	0	2	0	0	0	0	0
2020	Chat	0	5	0	0	0	0	0	0	1
2021	Chat	2	10	0	2	0	0	0	0	0
2021	Chien	0	1	0	0	0	0	0	0	0
2022	Chat	0	8	0	0	0	0	0	0	0
2023	Chat	11	24	0	3	0	0	0	0	0
2023	Chien	0	4	0	0	0	0	0	0	0
2023	Lapin	0	1	0	0	0	0	0	0	0
2024	Chat	0	28	34	0	0	0	0	2	0
2024	Chien	0	2	4	0	0	0	0	0	0
2024	Lapin	0	0	1	0	0	0	0	0	0

cathodique · 8 Octobre 2024

dysorthographie à dit:
a oui désolé j'ai inversé les colonne dan ma requête mais si tu remets en phase les entête de colonnes ?

Espèce Cd Fa Ad ChFa RtAd Adoptable Non Adoptable A Déterminer Décès
2010
Chat
0
1
0
0
0
0
0
0
0
2011
Chien
1
0
0
0
0
0
0
0
1
2015
Chat
0
1
1
0
0
0
0
0
0
2016
Chat
0
2
0
0
0
0
0
0
0
2017
Chat
0
2
1
0
0
0
0
0
1
2018
Chien
0
1
0
0
0
0
0
0
0
2019
Chat
0
6
0
2
0
0
0
0
0
2020
Chat
0
5
0
0
0
0
0
0
1
2021
Chat
2
10
0
2
0
0
0
0
0
2021
Chien
0
1
0
0
0
0
0
0
0
2022
Chat
0
8
0
0
0
0
0
0
0
2023
Chat
11
24
0
3
0
0
0
0
0
2023
Chien
0
4
0
0
0
0
0
0
0
2023
Lapin
0
1
0
0
0
0
0
0
0
2024
Chat
0
28
34
0
0
0
0
2
0
2024
Chien
0
2
4
0
0
0
0
0
0
2024
Lapin
0
0
1
0
0
0
0
0
0

Je te remercie mais ce n'est pas du tout ça. Ci-dessous résultat

2 chats décès et 1 chien doivent apparaitre pour 2024, car les décès sont survenus en 2024.
En espérant que tu me comprennes. Encore merci.

Bonne soirée.

dysorthographie · 8 Octobre 2024

Ok merci pour la précision ça me tardait de voir 2024 pour la date de décès sur l'année 2019 d'où le surlignage des résultats.

	Espèce	Cd	Fa	Ad	ChFa	RtAd	Adoptable	Non Adoptable	A Déterminer	Décès
2010	Chat	0	1	0	0	0	0	0	0	0
2011	Chien	1	0	0	0	0	0	0	0	0
2015	Chat	0	1	1	0	0	0	0	0	0
2016	Chat	0	2	0	0	0	0	0	0	0
2017	Chat	0	2	1	0	0	0	0	0	0
2018	Chien	0	1	0	0	0	0	0	0	0
2019	Chat	0	6	0	2	0	0	0	0	0
2020	Chat	0	5	0	0	0	0	0	0	0
2021	Chat	2	10	0	2	0	0	0	0	0
2021	Chien	0	1	0	0	0	0	0	0	0
2022	Chat	0	8	0	0	0	0	0	0	0
2023	Chat	11	24	0	3	0	0	0	0	0
2023	Chien	0	4	0	0	0	0	0	0	0
2023	Lapin	0	1	0	0	0	0	0	0	0
2024	Chat	0	28	34	0	0	0	0	2	2
2024	Chien	0	2	4	0	0	0	0	0	1
2024	Lapin	0	0	1	0	0	0	0	0	0

cathodique · 8 Octobre 2024

dysorthographie à dit:
Ok merci pour la précision ça me tardait de voir 2024 pour la date de décès sur l'année 2019 d'où le surlignage des résultats.

Espèce Cd Fa Ad ChFa RtAd Adoptable Non Adoptable A Déterminer Décès
2010
Chat
0
1
0
0
0
0
0
0
0
2011
Chien
1
0
0
0
0
0
0
0
0
2015
Chat
0
1
1
0
0
0
0
0
0
2016
Chat
0
2
0
0
0
0
0
0
0
2017
Chat
0
2
1
0
0
0
0
0
0
2018
Chien
0
1
0
0
0
0
0
0
0
2019
Chat
0
6
0
2
0
0
0
0
0
2020
Chat
0
5
0
0
0
0
0
0
0
2021
Chat
2
10
0
2
0
0
0
0
0
2021
Chien
0
1
0
0
0
0
0
0
0
2022
Chat
0
8
0
0
0
0
0
0
0
2023
Chat
11
24
0
3
0
0
0
0
0
2023
Chien
0
4
0
0
0
0
0
0
0
2023
Lapin
0
1
0
0
0
0
0
0
0
2024
Chat
0
28
34
0
0
0
0
2
2
2024
Chien
0
2
4
0
0
0
0
0
1
2024
Lapin
0
0
1
0
0
0
0
0
0

Ok, merci, mais les autres résultats ne sont pas corrects à l'exception de quelques uns.
Je suppose que je n'ai pas été très clair. Je ne sais pas si tu soustrais ou ne compte dans la catégorie Cd.
On compte cette catégorie et on n'y touche plus. La gestion des mouvements se fait au travers des autres catégories (Fa, Ad, ChFa et RtAd).

dysorthographie · 9 Octobre 2024

reposte le tableaux corrigé avec tes commentaire et je devrai trouvé les bonnes correction

On compte les cd tél qu'il ce présente car il ne peuvent pas être en double ?

Pour les autres qui correspondent à des mouvements on prend le dernier mouvement?

pourrais tu également préciser sur quel critère Etat des Animaux encore présents en Fin d'Année 2024.

ne te flagelle pas, ton propos est complexe et à expliquer et à comprendre mais je pense qu'on est en bon voie!

De toutes le façon je me vengerai au moment des explications car un usine à gaz en SQL ce ce digérer pas facilement.

VB:

Sub test2()
Dim SqlTOutSdoublon As String, SqlTOutTOut As String, Sql
 SqlTOutSdoublon = "(SELECT  year([Date]) as Annee,NoDossier,last([Cat#]) as Cat,last(Espece) as Espece,last(Caractère) as Caractère,max(DateDC) as DateDC FROM [BD$] group by NoDossier, year([Date]))"

With CreateObject("Adodb.connection")
    .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    SqlTOut = "SELECT Annee, Espece, sum(CD) as CD,sum(FA) as FA,sum(AD) as AD,sum(ChFa) as ChFa,Sum(RtAd) as RtAd,Sum(adoptable) as adoptable,Sum(NAdoptable) as NAdoptable,sum([A Déterminer]) as [A Déterminer],Sum(DateDc) as DateDc FROM (" & vbCrLf
    SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 1 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'CD'" & vbCrLf
    SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
    SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 1 AS FA ,0 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'FA'" & vbCrLf
    SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
    SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,1 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'AD'" & vbCrLf
    SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
    SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,1 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'CHFA'" & vbCrLf
    SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
    SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,1 as RtAd ,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'RTAD'" & vbCrLf
    SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
     SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,1 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'ADOPTABLE'" & vbCrLf
     SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
     SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,0 as adoptable,1 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'NON ADOPTABLE'" & vbCrLf
     SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
     SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier,year(DateDc) as  Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],1 as DateDc FROM " & SqlTOutSdoublon & " WHERE[DateDc]  is not null" & vbCrLf
     SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
     SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,0 as adoptable,0 as NAdoptable,1 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Caractère]) = ucase('A Déterminer')" & vbCrLf
      SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
     SqlTOut = SqlTOut & "SELECT DISTINCT  NoDossier, Year([BD$].[Date]) AS Annee, Especes.Espece,CD,FA,AD,ChFa,RtAd,adoptable,NAdoptable,[A Déterminer],DateDc   FROM [BD$] , (SELECT DISTINCT Espece, 0 AS CD, 0 AS FA, 0 AS AD, 0 AS ChFa, 0 AS RtAd, 0 AS adoptable, 0 AS NAdoptable, 0 AS [A Déterminer], 0 AS DateDc FROM [BD$]) AS Especes" & vbCrLf
     SqlTOut = SqlTOut & ") AS SubQuery GROUP BY Annee, Espece"

'2023
 Sql = "SELECT  Espece, sum(CD) as CD,sum(FA) as FA,sum(AD) as AD,sum(ChFa) as ChFa,Sum(RtAd) as RtAd,Sum(adoptable) as adoptable,Sum(NAdoptable) as NAdoptable,sum([A Déterminer]) as [A Déterminer],Sum(DateDc) as DateDc FROM (" & vbCrLf
 Sql = Sql & SqlTOut
 Sql = Sql & ") AS SubQuery WHERE Annee=" & Year(Date) - 1 & "  GROUP BY  Espece"
   Sheets("Résultat").Range("B7").CopyFromRecordset .Execute(Sql)
 
'2024
 Sql = "SELECT  Espece, sum(CD) as CD,sum(FA) as FA,sum(AD) as AD,sum(ChFa) as ChFa,Sum(RtAd) as RtAd,Sum(adoptable) as adoptable,Sum(NAdoptable) as NAdoptable,sum([A Déterminer]) as [A Déterminer],Sum(DateDc) as DateDc FROM (" & vbCrLf
 Sql = Sql & SqlTOut
 Sql = Sql & ") AS SubQuery WHERE Annee=" & Year(Date) & "  GROUP BY  Espece"
   Sheets("Résultat").Range("B13").CopyFromRecordset .Execute(Sql)
End With

End Sub

cathodique · 9 Octobre 2024

dysorthographie à dit:

reposte le tableaux corrigé avec tes commentaire et je devrai trouvé les bonnes correction

On compte les cd tél qu'il ce présente car il ne peuvent pas être en double ?

Pour les autres qui correspondent à des mouvements on prend le dernier mouvement?

pourrais tu également préciser sur quel critère Etat des Animaux encore présents en Fin d'Année 2024.

ne te flagelle pas, ton propos est complexe et à expliquer et à comprendre mais je pense qu'on est en bon voie!

De toutes le façon je me vengerai au moment des explications car un usine à gaz en SQL ce ce digérer pas facilement.

VB:

Sub test2()
Dim SqlTOutSdoublon As String, SqlTOutTOut As String, Sql
 SqlTOutSdoublon = "(SELECT  year([Date]) as Annee,NoDossier,last([Cat#]) as Cat,last(Espece) as Espece,last(Caractère) as Caractère,max(DateDC) as DateDC FROM [BD$] group by NoDossier, year([Date]))"

With CreateObject("Adodb.connection")
    .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    SqlTOut = "SELECT Annee, Espece, sum(CD) as CD,sum(FA) as FA,sum(AD) as AD,sum(ChFa) as ChFa,Sum(RtAd) as RtAd,Sum(adoptable) as adoptable,Sum(NAdoptable) as NAdoptable,sum([A Déterminer]) as [A Déterminer],Sum(DateDc) as DateDc FROM (" & vbCrLf
    SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 1 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'CD'" & vbCrLf
    SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
    SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 1 AS FA ,0 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'FA'" & vbCrLf
    SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
    SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,1 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'AD'" & vbCrLf
    SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
    SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,1 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'CHFA'" & vbCrLf
    SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
    SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,1 as RtAd ,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'RTAD'" & vbCrLf
    SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
     SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,1 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'ADOPTABLE'" & vbCrLf
     SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
     SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,0 as adoptable,1 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'NON ADOPTABLE'" & vbCrLf
     SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
     SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier,year(DateDc) as  Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],1 as DateDc FROM " & SqlTOutSdoublon & " WHERE[DateDc]  is not null" & vbCrLf
     SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
     SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,0 as adoptable,0 as NAdoptable,1 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Caractère]) = ucase('A Déterminer')" & vbCrLf
      SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
     SqlTOut = SqlTOut & "SELECT DISTINCT  NoDossier, Year([BD$].[Date]) AS Annee, Especes.Espece,CD,FA,AD,ChFa,RtAd,adoptable,NAdoptable,[A Déterminer],DateDc   FROM [BD$] , (SELECT DISTINCT Espece, 0 AS CD, 0 AS FA, 0 AS AD, 0 AS ChFa, 0 AS RtAd, 0 AS adoptable, 0 AS NAdoptable, 0 AS [A Déterminer], 0 AS DateDc FROM [BD$]) AS Especes" & vbCrLf
     SqlTOut = SqlTOut & ") AS SubQuery GROUP BY Annee, Espece"

'2023
 Sql = "SELECT  Espece, sum(CD) as CD,sum(FA) as FA,sum(AD) as AD,sum(ChFa) as ChFa,Sum(RtAd) as RtAd,Sum(adoptable) as adoptable,Sum(NAdoptable) as NAdoptable,sum([A Déterminer]) as [A Déterminer],Sum(DateDc) as DateDc FROM (" & vbCrLf
 Sql = Sql & SqlTOut
 Sql = Sql & ") AS SubQuery WHERE Annee=" & Year(Date) - 1 & "  GROUP BY  Espece"
   Sheets("Résultat").Range("B7").CopyFromRecordset .Execute(Sql)
 
'2024
 Sql = "SELECT  Espece, sum(CD) as CD,sum(FA) as FA,sum(AD) as AD,sum(ChFa) as ChFa,Sum(RtAd) as RtAd,Sum(adoptable) as adoptable,Sum(NAdoptable) as NAdoptable,sum([A Déterminer]) as [A Déterminer],Sum(DateDc) as DateDc FROM (" & vbCrLf
 Sql = Sql & SqlTOut
 Sql = Sql & ") AS SubQuery WHERE Annee=" & Year(Date) & "  GROUP BY  Espece"
   Sheets("Résultat").Range("B13").CopyFromRecordset .Execute(Sql)
End With

End Sub

Bonjour,

Merci beaucoup. SqlTout n'est défini, en quoi dois-je la définir?

De mon côté, je penses être sur la bonne voie.

Je t'explique comment j'ai procédé pour une espèce manuellement, par exemple chat.

Je filtre la colonne date,

ensuite filtre colonne espèce (chat),

ensuite filtre colonne Cat: 1. Cd et compte dans la colonne NoDossier (normalement,pour cette cat. il n'y a pas de doublon. Sauf erreur, alors par précaution compter sans doublon)
2. puis Fa et on compte NoDossier
3. puis Ad et on compte NoDossier
4. puis ChFa et on compte NoDossier
5. puis RtAd et on compte NoDossier (on ne compte pas RtFa, car pour RtAd il y a une RtFa)

Je défiltre la colonne Cat et passe à la colonne caractère
1. filtre sur 'adoptable' et filtre en colonne Cat sur 'Fa' et compte NoDossier sans doublon
2. même topo pour 'non adoptable' et 'à déterminer'

Pourla colonne DateDc, ils sont à compter si la date correspond ou est dans l'intervalle des dates du tableau considéré.

Particularité pour les décès, lorsque l'on compte Les Fa ils ne doivent être pris en compte.

C'est la même chose pour les caractères. Autrement dit, si l'animal décédé avait un caractère 'adoptable' on ne le compte pas dans les 'adoptables'. on ne le compte pas suivant son caractère.

Je ne sais si c'est assez clair.

Encore merci. Bonne journée.

edit: Grossière erreur de ma part, tous les résultats de la colonne Fa sont erronés. En effet, ils ne faut pas compter lorsqu'il y a une Ad. Pour avoir les bons chiffres, il faut diminuer le nombre des Ad de la colonne Fa. Toutes mes excuses.

klin89 · 9 Octobre 2024

Re cathodique,

Pour suivre ton raisonnement du post #37, il faut donc décompter les valeurs distinctes de la colonne B, soit les dossiers, après application des différents filtres.

Pour vérifier ce décompte, j'ai placé cette formule matricielle en U1 de la feuille "BD" et cela semble correspondre.

VB:

=SOMME(SI(FREQUENCE(SI(SOUS.TOTAL(103;DECALER(B2:B341;LIGNE(B2:B341)-MIN(LIGNE(B2:B341));0;1));EQUIV(B2:B341;B2:B341;0));LIGNE(B2:B341)-MIN(LIGNE(B2:B341))+1)>0;1))

Par contre, je ne comprends rien à cette partie.

Pour la colonne DateDc, ils sont à compter si la date correspond ou est dans l'intervalle des dates du tableau considéré.

Particularité pour les décès, lorsque l'on compte Les Fa ils ne doivent être pris en compte.

C'est la même chose pour les caractères. Autrement dit, si l'animal décédé avait un caractère 'adoptable' on ne le compte pas dans les 'adoptables'. on ne le compte pas suivant son caractère.

Idem pour ceci :

edit: Grossière erreur de ma part, tous les résultats de la colonne Fa sont erronés. En effet, ils ne faut pas compter lorsqu'il y a une Ad. Pour avoir les bons chiffres, il faut diminuer le nombre des Ad de la colonne Fa.

klin89

cathodique · 9 Octobre 2024

klin89 à dit:
Re cathodique,

Pour suivre ton raisonnement du post #37, il faut donc décompter les valeurs distinctes de la colonne B, soit les dossiers, après application des différents filtres.

Pour vérifier ce décompte, j'ai placé cette formule matricielle en U1 de la feuille "BD" et cela semble correspondre.

VB:

=SOMME(SI(FREQUENCE(SI(SOUS.TOTAL(103;DECALER(B2:B341;LIGNE(B2:B341)-MIN(LIGNE(B2:B341));0;1));EQUIV(B2:B341;B2:B341;0));LIGNE(B2:B341)-MIN(LIGNE(B2:B341))+1)>0;1))

Par contre, je ne comprends rien à cette partie.

Idem pour ceci :

klin89

Bonsoir @klin89 ,

Je te remercie infiniment de t'intéresser à mon problème. J'avoue que même moi je ne m'y retrouve plus.

Quant à la problématique du comptage des décès. Pour mieux, par exemple on enregistre l'entrée d'un chat le 12/04/2021. Dans la bd on enregistre 2 lignes: 1 Cd (avec enregistre de la personne qui le cède) et 1 Fa famille dans laquelle il est mis. Lors de son décès par exemple le 25/07/2024. cette date est enregistrée sur les 2 lignes créées lors de son entrée.
Donc ce décès doit être comptabilisé dans le tableau de l'année N soit en 2024.
Et non comptabilisé dans le tableau de toutes les années antérieures à l'année N, soit jusqu'au 31/12/2023.

Exact, on ne compte que les dossiers.
Merci pour ta formule matricielle.

Encore merci. Bonne soirée.

dysorthographie · 10 Octobre 2024

Bonjour,
voila pour test

cathodique · 10 Octobre 2024

dysorthographie à dit:
Bonjour,
voila pour test

Bonjour @dysorthographie ,

Je te remercie beaucoup, à très peu de chose le compte est bon.

Les quelques différences sont sûrement dues à des erreurs dans la bd.

Peux-tu m'expliquer au moins une ligne de code. J'ai constaté que tu jongles avec des 0,1,-1.

Je n'ai pas compris leur utilisation. Par exemple celle-ci:

VB:

    SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 1 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'CD'" & vbCrLf

Tu viens de me faire une démonstration de la puissance de SQL.

Encore merci

Bonne journée.

dysorthographie · 10 Octobre 2024

Bonjour,
D’abord analysons le raisonnement :

1. SqlTOutSdoublon c’ette requête récupère les données de l’onglet BD en extrayant les année des date et le tout sans doublon.

Années	Dosier	Cat	Espece	Caractère	DateDC
2010	2010027	Cd	Chat	Non Adoptable
2010	2010027	Fa	Chat	Non Adoptable
2011	2011017	Cd	Chien	Non Adoptable	15/09/2024
2011	2011017	Fa	Chien	Non Adoptable	15/09/2024
2015	2015003	Ad	Chat	Adoptable
2015	2015003	Cd	Chat	Adoptable

2. On fusionne ce tableau avec le tableau des résulta attendu

Cd
Fa
Ad
ChFa
RtAd
Adoptable
Non Adoptable
A Déterminer
Décès

Années Dosier Cat Espece Caractère DateDC Cd Fa Ad ChFa RtAd
2010
2010027
Cd Chat Non Adoptable
1

3. ensuite filtre colonne Cat: 1. Cd et compte dans la colonne NoDossier (normalement,pour cette cat. il n'y a pas de doublon. Sauf erreur, alors par précaution compter sans doublon)
2. puis Fa et on compte NoDossier
3. puis Ad et on compte NoDossier
4. puis ChFa et on compte NoDossier
5. puis RtAd et on compte NoDossier (on ne compte pas RtFa, car pour RtAd il y a une RtFa)

Je défiltre la colonne Cat et passe à la colonne caractère
1. filtre sur 'adoptable' et filtre en colonne Cat sur 'Fa' et compte NoDossier sans doublon
2. même topo pour 'non adoptable' et 'à déterminer'

Cliquez pour agrandir...
Le raisonnement pour passer de la demande à un résultat implique plusieurs étapes logiques basées sur des critères de filtrage, des opérations de comptage et l'extraction de données spécifiques. Voici un résumé de chaque étape du raisonnement :

1. Filtrage par catégorie (colonne Cat) et comptage des dossiers (colonne NoDossier)
- Étape 1 : Filtrer la colonne Cat pour obtenir uniquement les enregistrements ayant la valeur Cd et compter le nombre de dossiers uniques (NoDossier), car cette catégorie ne devrait pas avoir de doublon. Si des doublons existent, ils sont ignorés lors du comptage.
- Étape 2 : Répéter cette opération pour la catégorie Fa, en comptant également les dossiers. Ici, il est important de gérer les doublons de manière similaire.
- Étape 3 : Filtrer pour la catégorie Ad et compter les dossiers (NoDossier).
- Étape 4 : Filtrer pour la catégorie ChFa et compter les dossiers.
- Étape 5 : Filtrer pour la catégorie RtAd, mais ne pas compter ceux qui ont aussi RtFa, car un enregistrement RtAd implique nécessairement la présence de RtFa.
2. Retrait du filtre sur Cat et application d’un filtre sur la colonne Caractère
- Étape 1 : Filtrer sur la valeur adoptable dans la colonne Caractère et appliquer un second filtre sur la colonne Cat pour la valeur Fa. Compter ensuite les dossiers (NoDossier) sans doublon. Cela permet d'identifier les dossiers adoptables parmi la catégorie Fa.
- Étape 2 : Répéter l'opération pour la valeur non adoptable dans la colonne Caractère et la catégorie Fa, en comptant également les dossiers uniques.
- Étape 3 : Répéter encore pour les dossiers avec la valeur à déterminer, toujours en combinant le filtre sur la colonne Caractère avec le filtre Fa dans la colonne Cat.
3. Structuration de la requête SQL
- La requête SQL concatène les différentes étapes de filtrage et comptage pour chaque catégorie (Cd, Fa, Ad, etc.), avec l'application d’une condition distincte pour chaque filtre.
- Chaque section de la requête utilise une sous-requête ou une union (UNION ALL) pour agréger les résultats des différentes catégories et caractéristiques (adoptable, non adoptable, etc.) dans un format unifié, tout en s'assurant que les doublons sont correctement gérés.
- Le résultat final est regroupé par année (Annee) et espèce (Espece), en additionnant les comptes pour chaque catégorie, adoptabilité, et détermination.
Conclusion
L'ensemble du processus est organisé autour de deux axes :
1. Filtrage et comptage des dossiers uniques pour différentes catégories et caractéristiques, avec la gestion des doublons lorsque nécessaire.
2. Agrégation des résultats via une requête SQL qui regroupe les données en fonction des critères spécifiés (année, espèce, catégories, etc.). La logique de filtrage et comptage est structurée pour éviter les erreurs dues à la duplication et garantir que les bonnes informations sont extraites et comptabilisées pour chaque filtre appliqué.
Cliquez pour agrandir...
Pour bien comprendre et manipuler les requêtes SQL utilisées dans le code, voici un vocabulaire essentiel que vous devez maîtriser :
1. Requête SQL (Query) : Une commande utilisée pour interagir avec une base de données. Elle permet de sélectionner, insérer, mettre à jour ou supprimer des données.
2. SELECT : Instruction SQL utilisée pour récupérer des données d'une ou plusieurs tables.
3. DISTINCT : Mot-clé utilisé dans une requête SELECT pour retourner des lignes uniques et éliminer les doublons.
4. FROM : Spécifie la table ou l'ensemble de données à partir de laquelle vous récupérez des informations.
5. WHERE : Clause utilisée pour filtrer les lignes retournées selon certaines conditions.
6. GROUP BY : Instruction SQL utilisée pour regrouper les lignes qui ont des valeurs communes dans une ou plusieurs colonnes, généralement utilisée avec des fonctions d'agrégation (SUM, COUNT, etc.).
7. ORDER BY : Permet de trier les résultats retournés par une requête dans un ordre spécifique, ascendant ou descendant.
8. UNION et UNION ALL : Combinaison de résultats de plusieurs requêtes SELECT en une seule. UNION élimine les doublons, tandis que UNION ALL les conserve.
9. JOIN : Combine des lignes de plusieurs tables en fonction d'une condition définie (ici, pas explicitement utilisé mais sous-entendu par les regroupements de données).
10. AS : Mot-clé pour renommer une colonne ou une table temporairement dans le cadre d'une requête.
11. SUM() : Fonction d'agrégation qui additionne les valeurs d'une colonne numérique.
12. COUNT() : Fonction d'agrégation qui compte le nombre de lignes.
13. IIF() : Fonction conditionnelle qui retourne une valeur si une condition est vraie, et une autre valeur si elle est fausse (similaire à IF en Excel).
14. Subquery (Sous-requête) : Une requête imbriquée à l'intérieur d'une autre requête pour affiner le résultat ou effectuer des calculs avant de l'inclure dans la requête principale.
15. Alias : Nom temporaire donné à une colonne ou une table pour la rendre plus facile à référencer dans une requête.
16. UCASE() : Fonction SQL qui convertit les chaînes de caractères en majuscules (utile pour standardiser les données lors des comparaisons).
17. Execute : Dans le contexte du code VBA, c'est la méthode utilisée pour exécuter une requête SQL sur une connexion ADODB (objet base de données).
18. CopyFromRecordset : Méthode VBA pour copier les résultats d'une requête SQL directement dans une feuille Excel.
19. Date Functions (YEAR) : Fonction SQL qui extrait l'année d'une date, utile pour regrouper ou filtrer les résultats selon des périodes temporelles.
20. [BD$] : Nom d'une plage ou d'une table dans Excel représentant la source des données.
21. SubQuery (Sous-requête) : Une requête imbriquée dans une autre requête. Elle est souvent utilisée pour filtrer ou calculer des résultats avant de les utiliser dans la requête principale.
22. VbCrLf : Symbole dans VBA qui insère un retour à la ligne dans une chaîne de caractères. Utilisé pour rendre le code SQL plus lisible en divisant les lignes.
En maîtrisant ces termes, vous pourrez mieux comprendre le flux logique des requêtes SQL et comment elles interagissent avec les données dans le contexte du code fourni.
Cliquez pour agrandir...
Ce code effectue plusieurs requêtes SQL sur des données provenant d'un fichier Excel. Les requêtes sont ensuite exécutées via une connexion ADO (ActiveX Data Objects). Voici une explication des requêtes et des différentes parties du code :

1. Connexion à Excel via ADO
vbaCopier le code
With CreateObject("Adodb.connection") .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;"""
Cette partie du code ouvre une connexion ADO à la feuille de calcul Excel actuelle (ThisWorkbook.FullName). La connexion utilise Microsoft.ACE.OLEDB.12.0, un fournisseur OLEDB pour lire les fichiers Excel, et HDR=YES signifie que la première ligne des données est utilisée comme en-tête de colonne.

2. Définition des sous-requêtes SQL
SqlTOutSdoublon
vbaCopier le code
SqlTOutSdoublon = "(SELECT DISTINCT year([Date]) as Annee, NoDossier, ([Cat#]) as Cat, (Espece) as Espece, (Caractère) as Caractère, (DateDC) as DateDC FROM [BD$])"
Cette sous-requête sélectionne distinctement l'année (year([Date])), le numéro de dossier (NoDossier), et d'autres colonnes (Cat, Espece, Caractère, DateDC) à partir de la feuille BD$. Chaque combinaison unique est retournée.

3. Requête principale SqlTOut
Cette requête combine plusieurs sous-requêtes avec des conditions pour créer un récapitulatif des différentes catégories (CD, FA, AD, etc.) sur une base annuelle et par espèce.

Partie principale de SqlTOut
vbaCopier le code
SqlTOut = "SELECT Annee, Espece, sum(CD) as CD, sum(FA) as FA, sum(AD) as AD, sum(ChFa) as ChFa, Sum(RtAd) as RtAd, Sum(adoptable) as adoptable, Sum(NAdoptable) as NAdoptable, sum([A Déterminer]) as [A Déterminer], Sum(DateDc) as DateDc FROM ("
Cette requête regroupe les données par année et espèce, en additionnant des indicateurs (CD, FA, AD, etc.).

Sous-requêtes dans SqlTOut
vbaCopier le code
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 1 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'CD'" & vbCrLf
Cette sous-requête sélectionne les lignes où la catégorie (Cat) est égale à 'CD', définissant la colonne CD à 1 et les autres indicateurs à 0.
La même structure est répétée pour les autres catégories (FA, AD, ChFa, etc.) :
- WHERE UCASE([Cat]) = 'FA' définit FA à 1, le reste à 0.
- WHERE UCASE([Cat]) = 'AD' définit AD à 1, le reste à 0.
- Etc.
4. Combinaison des résultats
vbaCopier le code
SqlTOut = SqlTOut & ") AS SubQuery GROUP BY Annee, Espece"
Les résultats de toutes les sous-requêtes sont combinés en un sous-ensemble appelé SubQuery. Ce sous-ensemble est ensuite regroupé (GROUP BY) par année (Annee) et espèce (Espece), avec des sommes calculées pour chaque indicateur.

5. Requête SQL finale avec conditions WHERE
vbaCopier le code
Sql = "SELECT Espece, " & vbCrLf Sql = Sql & "SUM(IIF(CD < 0, 0, CD)) AS CD, " ' Calcul de la somme pour CD ... Sql = Sql & "[WHERE]" & vbCrLf ' Un emplacement réservé pour la clause WHERE Sql = Sql & "GROUP BY Espece"
Cette requête calcule les totaux par espèce, en filtrant les valeurs négatives et en insérant éventuellement une clause WHERE pour filtrer par année.

6. Exécution des requêtes
vbaCopier le code
Sheets("BD dédoublonnée").Range("A2").CopyFromRecordset .Execute(SqlTOutSdoublon) Sheets("Résulta par année").Range("B2").CopyFromRecordset .Execute(SqlTOut) Sheets("Résultat").Range("B7").CopyFromRecordset .Execute(Replace(Sql, "[WHERE]", "WHERE Annee=" & Year(Date) - 1)) Sheets("Résultat").Range("B13").CopyFromRecordset .Execute(Replace(Sql, "[WHERE]", "WHERE Annee=" & Year(Date)))
Chaque requête SQL est exécutée, et les résultats sont copiés dans les différentes feuilles de calcul :
- SqlTOutSdoublon pour la feuille "BD dédoublonnée".
- SqlTOut pour la feuille "Résulta par année".
- Sql avec les filtres WHERE Annee pour les feuilles "Résultat".
7. Conclusion
Le code effectue des opérations de sélection et d'agrégation sur des données Excel via des requêtes SQL, pour les regrouper par année et espèce, tout en gérant plusieurs catégories distinctes (CD, FA, AD, etc.). Les résultats sont ensuite affichés dans différentes feuilles de calcul pour analyse
Cliquez pour agrandir...

cathodique · 10 Octobre 2024

dysorthographie à dit:
Bonjour,
D’abord analysons le raisonnement :

1. SqlTOutSdoublon c’ette requête récupère les données de l’onglet BD en extrayant les année des date et le tout sans doublon.

Années Dosier Cat Espece Caractère DateDC
2010
2010027
Cd Chat Non Adoptable
2010
2010027
Fa Chat Non Adoptable
2011
2011017
Cd Chien Non Adoptable
15/09/2024
2011
2011017
Fa Chien Non Adoptable
15/09/2024
2015
2015003
Ad Chat Adoptable
2015
2015003
Cd Chat Adoptable

2. On fusionne ce tableau avec le tableau des résulta attendu

Cd
Fa
Ad
ChFa
RtAd
Adoptable
Non Adoptable
A Déterminer
Décès

Années Dosier Cat Espece Caractère DateDC Cd Fa Ad ChFa RtAd
2010
2010027
Cd Chat Non Adoptable
1

Bonsoir @dysorthographie

,

Je te remercie infiniment. C'est top.

Bonne soirée.

dysorthographie · 10 Octobre 2024

VB:

' Crée une sous-requête distincte qui sélectionne les colonnes Année, NoDossier, Cat, Espece, Caractère, et DateDC depuis la table BD$
SqlTOutSdoublon = "(SELECT  DISTINCT year([Date]) as Annee,NoDossier,[Cat#],Espece,Caractère,DateDC FROM [BD$])"

' Début de la requête principale : Sélectionne des colonnes agrégées avec des sommes pour différentes catégories
SqlTOut = "SELECT Annee, Espece, sum(CD) as CD,sum(FA) as FA,sum(AD) as AD,sum(ChFa) as ChFa,Sum(RtAd) as RtAd,Sum(adoptable) as adoptable,Sum(NAdoptable) as NAdoptable,sum([A Déterminer]) as [A Déterminer],Sum(DateDc) as DateDc FROM (" & vbCrLf

' Ajoute une première sous-requête pour les catégories CD
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 1 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'CD'" & vbCrLf
' WHERE UCASE([Cat]) = 'CD' : Cette clause WHERE filtre les lignes où la colonne [Cat] contient 'CD'.

' UNION permet d'ajouter une autre sous-requête pour les catégories FA
SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 1 AS FA ,0 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'FA'" & vbCrLf
' WHERE UCASE([Cat]) = 'FA' : Filtre les lignes où la colonne [Cat] contient 'FA'.

' UNION pour ajouter les catégories AD
SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,1 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'AD'" & vbCrLf
' WHERE UCASE([Cat]) = 'AD' : Filtre les lignes où la colonne [Cat] contient 'AD'.

' UNION pour ajouter les catégories CHFA
SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,1 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'CHFA'" & vbCrLf
' WHERE UCASE([Cat]) = 'CHFA' : Filtre les lignes où la colonne [Cat] contient 'CHFA'.

' UNION pour ajouter les catégories RTAD
SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,1 as RtAd ,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'RTAD'" & vbCrLf
' WHERE UCASE([Cat]) = 'RTAD' : Filtre les lignes où la colonne [Cat] contient 'RTAD'.

' UNION pour ajouter les dossiers adoptables dans la catégorie FA
SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,1 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'FA' AND UCASE([Caractère]) = 'ADOPTABLE'" & vbCrLf
' WHERE UCASE([Cat]) = 'FA' AND UCASE([Caractère]) = 'ADOPTABLE' : Filtre les lignes où [Cat] est 'FA' et [Caractère] est 'ADOPTABLE'.
' Cela permet de sélectionner uniquement les dossiers FA qui sont adoptables.

' UNION pour ajouter les dossiers non adoptables dans la catégorie FA
SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,0 as adoptable,1 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE UCASE([Cat]) = 'FA' AND UCASE([Caractère]) = 'NON ADOPTABLE'" & vbCrLf
' WHERE UCASE([Cat]) = 'FA' AND UCASE([Caractère]) = 'NON ADOPTABLE' : Filtre les lignes où [Cat] est 'FA' et [Caractère] est 'NON ADOPTABLE'.
' Cela sélectionne uniquement les dossiers FA qui ne sont pas adoptables.

' UNION pour les dossiers ayant une DateDc non nulle
SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier,year(DateDc) as  Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,0 as adoptable,0 as NAdoptable,1 as DateDc FROM " & SqlTOutSdoublon & " WHERE[DateDc]  is not null" & vbCrLf
' WHERE [DateDc] is not null : Filtre les lignes où la colonne [DateDc] contient une valeur non nulle, c'est-à-dire où une date de décès est enregistrée.

' UNION pour les catégories FA ayant une DateDc non nulle
SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier,year(DateDc) as  Annee, [Espece], 0 AS CD, -1 AS FA,0 as AD,0 as ChFa,0 as RtAd ,0 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE  UCASE([Cat]) = 'FA' AND[DateDc]  is not null" & vbCrLf
' WHERE UCASE([Cat]) = 'FA' AND [DateDc] is not null : Filtre les lignes où [Cat] est 'FA' et où une date de décès est enregistrée.

' UNION pour les dossiers adoptables ayant une DateDc non nulle
SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier,year(DateDc) as  Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,-1 as adoptable,0 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE  [DateDc]  is not null AND UCASE([Caractère]) = 'ADOPTABLE'" & vbCrLf
' WHERE [DateDc] is not null AND UCASE([Caractère]) = 'ADOPTABLE' : Sélectionne les dossiers adoptables qui ont une date de décès enregistrée.

' UNION pour les dossiers non adoptables ayant une DateDc non nulle
SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier,year(DateDc) as  Annee, [Espece], 0 AS CD, 0 AS FA,0 as AD,0 as ChFa,0 as RtAd ,0 as adoptable,-1 as NAdoptable,0 AS [A Déterminer],0 as DateDc FROM " & SqlTOutSdoublon & " WHERE  [DateDc]  is not null AND UCASE([Caractère]) = 'NON ADOPTABLE'" & vbCrLf
' WHERE [DateDc] is not null AND UCASE([Caractère]) = 'NON ADOPTABLE' : Sélectionne les dossiers non adoptables qui ont une date de décès enregistrée.

' UNION pour ajouter une sous-requête qui sélectionne tous les dossiers avec des valeurs par défaut pour les catégories
SqlTOut = SqlTOut & "UNION ALL" & vbCrLf
SqlTOut = SqlTOut & "SELECT DISTINCT NoDossier, Annee, 'Tous' as Espece,0 AS CD,0 as FA,0 as AD,0 as ChFa,0 as RtAd,0 as adoptable,0 as NAdoptable,0 as [A Déterminer],0 as DateDc  FROM " & SqlTOutSdoublon & vbCrLf

' Fermeture de la requête principale avec une clause GROUP BY pour agrégations par Année et Espèce
SqlTOut = SqlTOut & ") GROUP BY Annee,Espece" & vbCrLf

Explication de la Requête SQL

Dans ma requête SQL, j'utilise différentes valeurs pour gérer des informations concernant des animaux, en particulier pour les catégories et les décès. Voici comment les valeurs 1, 0, et -1 sont utilisées :

Valeur 1:
- J'attribue 1 à une colonne (comme CD, FA, AD, etc.) lorsque la condition correspondante est remplie. Par exemple, si un dossier appartient à la catégorie CD, je mets 1 dans la colonne CD. Cela permet de compter facilement combien d'animaux se trouvent dans chaque catégorie.
Valeur 0:
- La valeur 0 est utilisée pour les catégories où la condition n'est pas remplie. Par exemple, si un dossier ne fait pas partie de la catégorie FA, je mets 0 dans la colonne FA. Cela m'assure que cette catégorie ne contribue pas au total, tout en maintenant la structure des résultats.
Valeur -1:
- J'utilise -1 pour représenter des situations spécifiques, notamment les décès des animaux. Lorsque la colonne [DateDc] n'est pas nulle, cela indique qu'un animal est décédé. Dans ce cas, je mets -1 dans certaines colonnes pour indiquer une condition négative. Cela m'aide à distinguer les cas où un animal est décédé des autres catégories.

Détails de la Requête

La requête commence par définir une sous-requête SqlTOutSdoublon, qui sélectionne des données distinctes des dossiers, y compris l'année, le numéro de dossier, la catégorie, l'espèce, le caractère, et la date de décès.
Ensuite, dans la requête principale SqlTOut, j'effectue plusieurs sélections avec UNION ALL pour agréger les résultats. Chaque sélection correspond à une catégorie spécifique :
- Pour CD, FA, et AD, j'utilise 1 ou 0 selon la catégorie.
- Pour ChFa, RtAd, adoptable, et NAdoptable, j'affecte 0 ou 1 selon les conditions, comme lorsqu'un animal est marqué comme adoptable ou non.
À la fin de la requête, je fais la somme des valeurs dans chaque colonne. Par exemple, si je prends une colonne avec des valeurs comme 1 (pour un animal vivant), 0 (pour un animal non concerné), et -1 (pour un animal décédé), la somme serait :
1+0+(−1)=0
Cette opération soustrait effectivement les décès du total, ce qui me permet de comprendre combien d'animaux sont comptabilisé pour chaque années et dans chaque catégorie.

Conclusion

En résumé, l'utilisation de 1, 0, et -1 me permet de gérer efficacement les différentes catégories d'animaux dans la base de données. Cela facilite l'agrégation et le comptage des résultats dans ma requête SQL, me permettant d'obtenir des totaux précis tout en prenant en compte les décès des animaux.

cathodique · 14 Octobre 2024

Bonjour à toutes et à tous,

Pour le partage, je vous donne ma solution. Notez bien que je ne suis arrivé à ce résultat que grâce à votre aide.
Et de recherches sur le forum et le net.
Je suis autodidacte et je fonctionne avec mes maigres acquis. Donc le code n'est pas au top mais fait le boulot.
Je sais qu'on peut l'améliorer et le simplifier. J'ai passé tellement de temps que je n'ose pas m'y aventurer.
Je pourrais vous répondre sur la finalité du code mais je ne pourrai pas vous expliquer certaines parties de code.
Accepte toutes critiques constructives. Merci pour indulgence.

Bonne journée.

Années	Dosier	Cat	Espece	Caractère	DateDC	Cd	Fa	Ad	ChFa	RtAd
2010	2010027	Cd	Chat	Non Adoptable	1

XL 2010 tableau de comptage sans doublon

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Accro

Pièces jointes

XLDnaute Barbatruc

XLDnaute Accro

1. Filtrage par catégorie (colonne Cat) et comptage des dossiers (colonne NoDossier)​

2. Retrait du filtre sur Cat et application d’un filtre sur la colonne Caractère​

3. Structuration de la requête SQL​

Conclusion​

1. Connexion à Excel via ADO​

2. Définition des sous-requêtes SQL​

SqlTOutSdoublon​

3. Requête principale SqlTOut​

Partie principale de SqlTOut​

Sous-requêtes dans SqlTOut​

4. Combinaison des résultats​

5. Requête SQL finale avec conditions WHERE​

6. Exécution des requêtes​

7. Conclusion​

Pièces jointes

XLDnaute Barbatruc

XLDnaute Accro

Explication de la Requête SQL​

Détails de la Requête​

Conclusion​

XLDnaute Barbatruc

Pièces jointes

Discussions similaires

Partager cette page

Statistiques des forums

1. Filtrage par catégorie (colonne Cat) et comptage des dossiers (colonne NoDossier)

2. Retrait du filtre sur Cat et application d’un filtre sur la colonne Caractère

3. Structuration de la requête SQL

Conclusion

1. Connexion à Excel via ADO

2. Définition des sous-requêtes SQL

SqlTOutSdoublon

3. Requête principale SqlTOut

Partie principale de SqlTOut

Sous-requêtes dans SqlTOut

4. Combinaison des résultats

5. Requête SQL finale avec conditions WHERE

6. Exécution des requêtes

7. Conclusion

Explication de la Requête SQL

Détails de la Requête

Conclusion