Hi, I'm trying to merge 2 "transform" query into another one... instead of having three queries i'd have 1.
I could have used a three query method, however in my current situation, it wont work because for every query I have to change the conditions and columns in the "where". sometimes a query might be:
col1 <= 5 or col1 = 5. or sometimes I'll have to add some filters on col2 or sometimes col2 will not be filtered at all so it won't appear in the "where".
to bypass this bug(?), I'm using querydefs to erase and create new queries everytime I need to execute my query. I don't like this method. its not elegant and its possible that it could fail in a multi-user environment (if the users share the same application)...
I cant have dynamic operators in a static access query, so I need to create the query on the fly in a vba procedure. therefore this method is the best.(imo)
Anyway, the real problem is here:
SELECT
*
FROM
([000 Cat_Emploi_SD_Table Équivalence]
LEFT JOIN (
TRANSFORM
Sum(ANNEE_EN_COURS.HR_FIN) AS SommeDeHR_FIN
SELECT
*
FROM
ANNEE_EN_COURS
PIVOT
ANNEE_EN_COURS.MOIS) AS tEnCours
ON
([000 Cat_Emploi_SD_Table Équivalence].GR_EMPLOI = tEnCours.GREMPLOI) AND
([000 Cat_Emploi_SD_Table Équivalence].CAT_EMPLOI = tEnCours.CAT_EMPLOI))
LEFT JOIN (
TRANSFORM
Sum(ANNEE_PRECEDENTE.HR_FIN) AS SommeDeHR_FIN
SELECT
*
FROM
ANNEE_PRECEDENTE
WHERE
(((Mid([CC_LIB],1,5))="30053") AND
((ANNEE_PRECEDENTE.MOIS)<="005") AND
((ANNEE_PRECEDENTE.ANNEE)="2002"))
PIVOT
ANNEE_PRECEDENTE.MOIS) as tPrecedente
ON
([000 Cat_Emploi_SD_Table Équivalence].GR_EMPLOI = tPrecedente.GREMPLOI) AND
([000 Cat_Emploi_SD_Table Équivalence].CAT_EMPLOI = tPrecedente.CAT_EMPLOI)
normally, such a query would work!
however access refuses to execute it because of the Transform and Pivot statements.
I get an error while parsing the FROM if Transform and Pivot are present. if I remove them it will work.
how can I fix this?
I could have used a three query method, however in my current situation, it wont work because for every query I have to change the conditions and columns in the "where". sometimes a query might be:
col1 <= 5 or col1 = 5. or sometimes I'll have to add some filters on col2 or sometimes col2 will not be filtered at all so it won't appear in the "where".
to bypass this bug(?), I'm using querydefs to erase and create new queries everytime I need to execute my query. I don't like this method. its not elegant and its possible that it could fail in a multi-user environment (if the users share the same application)...
I cant have dynamic operators in a static access query, so I need to create the query on the fly in a vba procedure. therefore this method is the best.(imo)
Anyway, the real problem is here:
SELECT
*
FROM
([000 Cat_Emploi_SD_Table Équivalence]
LEFT JOIN (
TRANSFORM
Sum(ANNEE_EN_COURS.HR_FIN) AS SommeDeHR_FIN
SELECT
*
FROM
ANNEE_EN_COURS
PIVOT
ANNEE_EN_COURS.MOIS) AS tEnCours
ON
([000 Cat_Emploi_SD_Table Équivalence].GR_EMPLOI = tEnCours.GREMPLOI) AND
([000 Cat_Emploi_SD_Table Équivalence].CAT_EMPLOI = tEnCours.CAT_EMPLOI))
LEFT JOIN (
TRANSFORM
Sum(ANNEE_PRECEDENTE.HR_FIN) AS SommeDeHR_FIN
SELECT
*
FROM
ANNEE_PRECEDENTE
WHERE
(((Mid([CC_LIB],1,5))="30053") AND
((ANNEE_PRECEDENTE.MOIS)<="005") AND
((ANNEE_PRECEDENTE.ANNEE)="2002"))
PIVOT
ANNEE_PRECEDENTE.MOIS) as tPrecedente
ON
([000 Cat_Emploi_SD_Table Équivalence].GR_EMPLOI = tPrecedente.GREMPLOI) AND
([000 Cat_Emploi_SD_Table Équivalence].CAT_EMPLOI = tPrecedente.CAT_EMPLOI)
normally, such a query would work!
however access refuses to execute it because of the Transform and Pivot statements.
I get an error while parsing the FROM if Transform and Pivot are present. if I remove them it will work.
how can I fix this?
