Access 2003 modifies (corrupts) query when saved...

[Bpfh]

Hi,

I have a query built in the Access query builder and I have noticed some
strange happenings.

My query has 2 sets of criteria separated by an OR (somthing like this: ...
having ... and..., and... OR and... and...)
but when saved, the order is changed (and looks like this: ... having ...
and..., and..., and... OR and...) - as the order is changed, the query
results are wrong...

For the moment, we can correct this by rewriting the criteria, but it is
very easy to forget...

I can post before/after queries if my explanations are not clear enough!

Cheers,
Daniel

 

[Guest]

1) always use brackets: never depend on the order
or precedence of the operators. Order and precedence
are not defined for Jet SQL.

2) If the criteria is complex, it can be simplified. If that
is the case, repost with the full criteria.

(david)

 

[Bpfh]

Hi David,

The query was generated by the query interface, and not written by hand in
SQL, and runs perfectly until saved & reopened: Access reorganises the query,
breaking it.

Here is the SQL code for the intial and Access-reorganised versions (large
query). I have broken up the query for better understanding. The differences
follow the HAVING instruction where part of the query that would appear on
the second criteria line of the query builder is moved to become an extra
argument of the first criteria line.

1) Correct query:
SELECT
sales_global_courant.source_donnees,
source_donnees.lib_source,
dates.fy_quarter,
agence_departement_region.agence,
sales_global_courant.no_client_direct,
source_donnees_nom_sap_code.nom_client_direct,
Sum(sales_global_courant.quantite) AS SommeDequantite,
famille_produits_mpg.MPG_Famille,
famille_produits_mpg.MPG,
sales_global_courant.regroupement_GD
FROM
famille_produits_mpg
INNER JOIN (source_donnees
INNER JOIN (source_donnees_nom_sap_code
INNER JOIN (agence_departement_region
INNER JOIN (dates
INNER JOIN (famille_produits
INNER JOIN sales_global_courant
ON
famille_produits.code_article = sales_global_courant.code_article)
ON dates.date = sales_global_courant.date)
ON agence_departement_region.code_dept = sales_global_courant.code_dept)
ON source_donnees_nom_sap_code.code_sap =
sales_global_courant.no_client_direct)
ON source_donnees.source_donnees = sales_global_courant.source_donnees)
ON famille_produits_mpg.MPG = famille_produits.MPG
GROUP BY
sales_global_courant.source_donnees,
source_donnees.lib_source,
dates.fy_quarter,
agence_departement_region.agence,
sales_global_courant.no_client_direct,
source_donnees_nom_sap_code.nom_client_direct,
famille_produits_mpg.MPG_Famille,
famille_produits_mpg.MPG,
sales_global_courant.regroupement_GD
HAVING
(((sales_global_courant.source_donnees)="1")
AND
((dates.fy_quarter)="FY06.Q4")
AND
((agence_departement_region.agence) Not In ("Export","Toulouse"))
AND
((famille_produits_mpg.MPG) In ("62","6H"))
AND
((sales_global_courant.regroupement_GD) Is Null Or
(sales_global_courant.regroupement_GD)=""))
OR
(((sales_global_courant.source_donnees)="1")
AND
((dates.fy_quarter)="FY06.Q4")
AND
((agence_departement_region.agence)="Toulouse")
AND
((sales_global_courant.no_client_direct)<>"165902")
AND
((famille_produits_mpg.MPG) In ("62","6H"))
AND
((sales_global_courant.regroupement_GD) Is Null Or
(sales_global_courant.regroupement_GD)=""))
ORDER BY
agence_departement_region.agence;


2) After Access reorg:
SELECT
sales_global_courant.source_donnees,
source_donnees.lib_source,
dates.fy_quarter,
agence_departement_region.agence,
sales_global_courant.no_client_direct,
source_donnees_nom_sap_code.nom_client_direct,
Sum(sales_global_courant.quantite) AS SommeDequantite,
famille_produits_mpg.MPG_Famille,
famille_produits_mpg.MPG,
sales_global_courant.regroupement_GD
FROM
famille_produits_mpg
INNER JOIN (source_donnees
INNER JOIN (source_donnees_nom_sap_code
INNER JOIN (agence_departement_region
INNER JOIN (dates INNER JOIN (famille_produits
INNER JOIN sales_global_courant
ON
famille_produits.code_article = sales_global_courant.code_article)
ON dates.date = sales_global_courant.date)
ON agence_departement_region.code_dept = sales_global_courant.code_dept)
ON source_donnees_nom_sap_code.code_sap =
sales_global_courant.no_client_direct)
ON source_donnees.source_donnees = sales_global_courant.source_donnees)
ON famille_produits_mpg.MPG = famille_produits.MPG
GROUP BY
sales_global_courant.source_donnees,
source_donnees.lib_source, dates.fy_quarter,
agence_departement_region.agence,
sales_global_courant.no_client_direct,
source_donnees_nom_sap_code.nom_client_direct,
famille_produits_mpg.MPG_Famille,
famille_produits_mpg.MPG,
sales_global_courant.regroupement_GD
HAVING
(((sales_global_courant.source_donnees)="1")
AND
((dates.fy_quarter)="FY06.Q4")
AND
((agence_departement_region.agence) Not In ("Export","Toulouse"))
AND
((famille_produits_mpg.MPG) In ("62","6H"))
AND
((sales_global_courant.regroupement_GD) Is Null Or
(sales_global_courant.regroupement_GD)=""
Or ((sales_global_courant.regroupement_GD) Is Null Or
(sales_global_courant.regroupement_GD)="")))
OR
(((sales_global_courant.source_donnees)="1")
AND
((dates.fy_quarter)="FY06.Q4")
AND
((agence_departement_region.agence)="Toulouse")
AND
((sales_global_courant.no_client_direct)<>"165902")
AND
((famille_produits_mpg.MPG) In ("62","6H")))
ORDER BY
agence_departement_region.agence;

Cheers,
Daniel

 

[Dale Fye]

Since most of the field have the same criteria, how about modifying your
HAVING clause as:

HAVING sales_global_courant.source_donnees="1"
AND dates.fy_quarter="FY06.Q4"
AND famille_produits_mpg.MPG In ("62","6H")
AND (sales_global_courant.regroupement_GD Is Null Or
sales_global_courant.regroupement_GD="")
AND (agence_departement_region.agence Not In ("Export","Toulouse")
OR
(agence_departement_region.agence="Toulouse" AND
sales_global_courant.no_client_direct<>"165902"))

HTH
Dale

 

[Marshall Barton]

Why are you using a GROUP BY clause when you are not using
an aggregate function?

With or without the GROUP BY clause, you need to change the
HAVING clause to a WHERE clause.