Discussion :

[Themis69]

Bonjour,

Je developpe actuellement une petite application sous ACCESS 2007 servant a gerer des contrats pour une societe commerciale.

En simplifiant, j'ai trois tables :
1 - Table Customers (ce sont les clients de ma societe qui ont signe avec elle des contrats)
Cust_ID (cle)
Cust_Name (nom du client)
Cust_Spe (domaine d'activite du client . C'est un champ a valeurs multiples puisqu'ACCESS 2007 l'autorise)

2 - Table Contracts (les contrats signes entre ma societe et les clients precedents)
Ct_Nr (cle)
Ct_Name
Ct_Effectivedate (date d'entree en vigueur)
Ct_Term (date de fin du contrat)
Ct_Status (statut du contrat = actif/expire/resilier...)
Cust_ID (le client parti au contrat - lie a la table precedente)

3 - Table Fees (A chaque contrat sont associes des frais factures au client)
Fee_ID
Fee_Cat (categorie de frais)
Fee_Serv (type de prestation)
Fee_Ct_Nr (contrat auquel ces frais sont associes - lie a la table precedente)

Mon probleme:

J'ai realise un moteur de recherche des contrats selon certains criteres (le moteur est construit sur le tres bon exemple de Cafeine : http://cafeine.developpez.com/access...echerchemulti/

J'ai deux soucis actuellement :

- Lorsqu'un contrat n'a pas de Fee (aucun enregistrement dans la table Fees), le Contrat n'apparait pas dans les resultats de la recherche alors meme qu'il devrait apparaitre du fait des criteres saisis (par exemple le nom du contrat)
- 2eme probleme plus complique : les resultats dans le tableau affichent plusieurs fois le meme contrat... en fait, chaque contrat apparait autant de fois qu'il y de Fees attachees a celui-ci

Autrement dit : pas de Fees associes = pas de contrat liste apres la recherche
3 Fees associees au contrat = le meme contrat liste 3 fois

J'ai essaye de supprimer ces doublons de l'affichage en recourant a DISTINCT et DISTINCTROW sur le champ Ct_Nr, mais cela ne fait rien voire bloque la requete. J'ai egalement essaye des GROUP BY Ct_Nr HAVING ... mais la encore sans succes.

Je vous mets ci-dessous ma requete.
Un tres grand merci par avance si vous avez des pistes a me proposer pour avoir un affichage clair.

Julien

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Private Sub RefreshQuery()
Dim SQL As String
Dim SQLWhere As String
 
SQL = "SELECT Contracts.Ct_Nr, Customers.Cust_Name, Contracts.Ct_Name, Contracts.Ct_Effectivedate, Contracts.Ct_Term, Contracts.Ct_Status, Contracts.Cust_ID, Customers.Cust_Spe, Fees.Ct_Nr, Fees.Fee_Cat, Fees.Fee_Serv FROM (Customers INNER JOIN Contracts ON Customers.Cust_ID = Contracts.Cust_ID) INNER JOIN Fees ON Fees.Ct_Nr = Contracts.Ct_Nr WHERE Contracts!Ct_Nr <> 0 "
 
If Me.txt_SearchContract <> "" Then
SQL = SQL & "And Contracts!Ct_Name like '*" & Me.txt_SearchContract & "*' "
End If
If Me.txt_SearchDate <> "" Then
SQL = SQL & "And Contracts!Ct_Effectivedate like '*" & Me.txt_SearchDate & "*' "
End If
If Me.txt_SearchTerm <> "" Then
SQL = SQL & "And Contracts!Ct_Term like '*" & Me.txt_SearchTerm & "*' "
End If
If Me.txt_SearchStat <> "" Then
SQL = SQL & "And Contracts!Ct_Status = '" & Me.txt_SearchStat & "' "
End If
If Me.txt_SearchCust <> "" Then
SQL = SQL & "And Customers!Cust_Name like '*" & Me.txt_SearchCust & "*' "
End If
If Me.txt_SearchServ <> "" Then
SQL = SQL & "And Fees!Fee_Cat = '" & Me.txt_SearchServ & "' "
End If
If Me.txt_SearchFees <> "" Then
SQL = SQL & "And Fees!Fee_Serv = '" & Me.txt_SearchFees & "' "
End If
If Me.txt_SearchIndustry <> "" Then
SQL = SQL & "And Customers!Cust_Spe.value like '*" & Me.txt_SearchIndustry & "*' "
End If
 
SQLWhere = Trim(Right(SQL, Len(SQL) - InStr(SQL, "Where ") - Len("Where ") + 1))
SQL = SQL & ";"
 
Me.lstResults.RowSource = SQL
Me.lstResults.Requery
 
End Sub

[elbj]

Bonsoir

Je ne connais pas trop Access mais je vais essayer de te répondre.

1° problème : les Contrats qui n'apparaissent pas lorsqu'ils n'ont pas de Fees
Le problème vient de ta jointure avec la table Fees. Essaye avec un LEFT JOIN Fees au lieu de INNER JOIN Fees.

2° problème : les Contrats apparaissant plusieurs fois
C'est normal que ce résultat apparaisse. Tu réalises une jointure entre Contracts et Fees. Mets toi à la place du moteur Access. A chaque fois qu'il va trouver une ligne dans Fees il va rajouter les infos correspondantes de Contract, que pourrait-il faire d'autre puisque tu lui demandes, dans ton SELECT, d'afficher des informations de Contract. Un DINSTINCT ne t'apportera rien. D'ailleurs, si tu as un client avec plusieurs contrats je te parie que les informations de ce client apparaissent autant de fois qu'il a de contrat.
En fait tu devrais présenter ton résultat de recherche en deux listes différentes : une par contrat et l'autre, liée à la première, par fee. Ainsi ta recherche va populer la première liste et un évènement de sélection d'une ligne de celle-ci génèrera la recherche des Fees qui sont liées.

Voilou, j'espère avoir pu t'aider.

Cordialement

[Themis69]

Merci Christophe pour cette réponse.
Ta solution au premier probleme avec un LEFT JOIN marche parfaitement et maintenant, même les contrats sans Fees sont affichés.

Je comprends le pourquoi du second problème. Il me semblait effectivement que ca venait de ça. En revanche, j'ai pris note de ta proposition de modification, mais malheureusement, cela ne me parle pas bcp et je ne sais pas comment transformer cela en SQL pour access.

Merci pour ton aide si tu en as le temps pour expliciter au novice que je suis, la manière de faire cela.
Bien cordialement,
Julien

En fait tu devrais présenter ton résultat de recherche en deux listes différentes : une par contrat et l'autre, liée à la première, par fee. Ainsi ta recherche va populer la première liste et un évènement de sélection d'une ligne de celle-ci génèrera la recherche des Fees qui sont liées.

[elbj]

Je t'en prie

Reprenons le contexte. Tu as une liste de critères que l'utilisateur peut renseigner pour effectuer des recherches. A partir de ces critères ta fonction RefreshQuery() va construire dynamiquement une requête SQL pour fournir des données à une liste.

Tes critères concernent les colonnes :

• Contracts!Ct_Name
• Contracts!Ct_Effectivedate
• Contracts!Ct_Term
• Contracts!Ct_Status
• Customers!Cust_Name
• Fees!Fee_Cat
• Fees!Fee_Serv
• Customers!Cust_Spe.value

Et ta projection (les colonnes à afficher) concerne les colonnes :

• Contracts.Ct_Nr
• Customers.Cust_Name
• Contracts.Ct_Name
• Contracts.Ct_Effectivedate
• Contracts.Ct_Term
• Contracts.Ct_Status
• Contracts.Cust_ID
• Customers.Cust_Spe
• Fees.Ct_Nr
• Fees.Fee_Cat
• Fees.Fee_Serv

Tu dois te dire que ce sont deux choses bien différentes ! Ce n'est pas parce qu'une colonne est dans les critères qu'elle doit être dans la projection. Donc tu va garder la même liste de critères mais tu vas changer ta projection ainsi :

• Contracts.Ct_Nr
• Customers.Cust_Name
• Contracts.Ct_Name
• Contracts.Ct_Effectivedate
• Contracts.Ct_Term
• Contracts.Ct_Status
• Contracts.Cust_ID
• Customers.Cust_Spe

Là tu peux utiliser DISTINCT, GROUP BY... pour obtenir une seule ligne par contrat. Mais où sont donc les Fees ? Dans une autre liste !

Je suppose que tu affiches ta liste dans une grille (visuellement parlant), donc lorsque l'utilisateur sélectionne une ligne de cette grille, tu devrais pouvoir intercepter l'évènement. A partir de là tu peux envoyer une requête SQL cherchant tous les Fees du contrat dont la ligne a été cliquée et afficher le résultat dans une autre grille.

C'est une relation MAITRE/DETAIL. Renseigne toi là dessus, je suis persuadé qu'il y a un moyen d'implémenter ça sous Access.

Bon courage

[Themis69]

Encore merci pour cette réponse Christophe.
Je vois la difference entre les deux.

En revanche, dans notre tableau de resultat (ce que tu appelles "projection", j'imagine ?) nous ne listons deja que certains champs et pas l'ensemble de ceux present dans la requete.

Ainsi, notre tableau de resultat n'affiche que les champs Ct_Nr;Cust_Name;Ct_Name;Ct_Effectivedate, Ct_Term, Ct_Status; soit 6 champs seulement.

Reste que je ne sais pas comment ajouter les instructions DISTINCT ou GROUP BY sur ce tableau comme tu le proposes si j'ai bien compris, car des qu'un champ est rempli dans le formulaire de recherche, le tableau de resultat est immediatement mis a jour en fonction des criteres saisis.

Au chargement du formulaire, le tableau affiche par defaut tous les contrats grace a une requete tres basique lors du chargement du formulaire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Private Sub Form_Load()
 
Me.lstResults.RowSource = "SELECT Contracts.Ct_Nr, Customers.Cust_Name, Contracts.Ct_Name, Contracts.Ct_Effectivedate, Contracts.Ct_Term, Contracts.Ct_Status, Contracts.Cust_ID FROM Contracts INNER JOIN Customers ON Contracts.Cust_ID=Customers.Cust_ID WHERE Contracts!Ct_Nr <> 0 ORDER BY Cust_Name ASC "
Me.lstResults.Requery
 
End Sub

Lors de ce chargement, il n'y a pas de doublons puisque les Fees ne sont pas inclus dans la requete.

Par contre, des la presence de criteres on bascule sur la requete qui me pose probleme.
Un grand merci si tu peux m'indiquer où implementer les eventuelles instructions pour que le tableau de resultats filtre les doublons comme tu le suggeres.

Julien

[elbj]

La projection d'une requête ce sont les colonnes retournées par la requête, ce qui se trouve entre le SELECT et le FROM.

Pour l'utilisation de DISTINCT et GROUP BY, je t'invite à regarder dans les Tutoriels SQL présents sur www.developpez.com

As tu modifié la requête de RefreshQuery() pour ne laisser apparaitre dans la projection que les colonnes que je te conseillais ?

Cordialement

[Themis69]

Un tres grand merci Christophe, ca marche parfaitement en limitant les champs dans la requete a ceux que tu m'as suggere.

Tout est parfait.
Encore merci.

julien