upgraded Access 03 to 07, joined query now won't work

[bendixen]

I recently upgraded from Access 03 to Access 07. Most of my queries linking to ODBC tables I previously created in 03 are working fine. There are a couple that do not now. The ones that do not have left joins between some of the tables in them. The tables are pulled in from an ODBC source. The joins work when I made a new test table within Access. Are there any known issues in 2007 regarding this? I'm about at the end of my sanity on this one.

The SQL of the query is this...

SELECT GRANTACCT_ACCOUNTING_CONTACT.CONTACT_NAME, GL_GL_WHOKEY_IACT_SUMRY.GL_GRANT_PGM_ID_ELEM_CD AS [G/P ID], GL_GL_WHOKEY_IACT_SUMRY.GL_GRANT_PGM_NBR_ELEM_CD AS [G/P Number], Last(GRANTACCT_GRANT_ACCOUNT.GRANT_PRINCIPLE_INVESTIGATOR) AS PI, Last(GL_GL_WHOKEY_IACT_SUMRY.GL_ORG_ELEM_CD) AS LastOfGL_ORG_ELEM_CD, Last(GL_GL_WHOKEY_IACT_SUMRY.GL_ORG_DESC) AS LastOfGL_ORG_DESC, Last(GL_GL_WHOKEY_IACT_SUMRY.GL_DEPT_ELEM_CD) AS LastOfGL_DEPT_ELEM_CD, Last(GL_GL_WHOKEY_IACT_SUMRY.GL_DEPT_DESC) AS LastOfGL_DEPT_DESC, First(GRANTACCT_GRANT_ACCOUNT.GRANT_BEGIN_DT) AS FirstOfGRANT_BEGIN_DT, First(GRANTACCT_GRANT_ACCOUNT.GRANT_END_DT) AS FirstOfGRANT_END_DT, First(GL_GL_WHOKEY.WHOKEY_CLOSE_DT) AS FirstOfWHOKEY_CLOSE_DT, GL_GL_WHOKEY_IACT_SUMRY.ACCTG_PRD, GL_GL_WHOKEY_IACT_SUMRY.ACCTG_FISC_YR, GL_GL_WHOKEY_IACT_SUMRY.GL_GRANT_PGM_ID_ELEM_CD, GRANTACCT_GRANT_MGMT.GRANT_PROJECT_PERIOD_CD, GL_GL_WHOKEY_IACT_SUMRY.GL_FUND_ELEM_CD, GL_GL_WHOKEY_IACT_SUMRY.BUDGET_REF
FROM (((GL_GL_WHOKEY_IACT_SUMRY LEFT JOIN GRANTACCT_GRANT_ACCOUNT ON (GL_GL_WHOKEY_IACT_SUMRY.GL_DEPT_ELEM_CD = GRANTACCT_GRANT_ACCOUNT.GL_DEPT_ELEM_CD) AND (GL_GL_WHOKEY_IACT_SUMRY.GL_ORG_ELEM_CD = GRANTACCT_GRANT_ACCOUNT.GL_ORG_ELEM_CD) AND (GL_GL_WHOKEY_IACT_SUMRY.ACCTG_FISC_YR = GRANTACCT_GRANT_ACCOUNT.ACCTG_FISC_YR) AND (GL_GL_WHOKEY_IACT_SUMRY.ACCTG_PRD = GRANTACCT_GRANT_ACCOUNT.ACCTG_PRD) AND (GL_GL_WHOKEY_IACT_SUMRY.GL_GRANT_PGM_ELEM_CD = GRANTACCT_GRANT_ACCOUNT.GL_GRANT_PGM_ELEM_CD) AND (GL_GL_WHOKEY_IACT_SUMRY.GL_FUND_ELEM_CD = GRANTACCT_GRANT_ACCOUNT.GL_FUND_ELEM_CD)) INNER JOIN GL_GL_WHOKEY ON (GL_GL_WHOKEY_IACT_SUMRY.GL_CORP_ELEM_CD = GL_GL_WHOKEY.GL_CORP_ELEM_CD) AND (GL_GL_WHOKEY_IACT_SUMRY.GL_FUND_ELEM_CD = GL_GL_WHOKEY.GL_FUND_ELEM_CD) AND (GL_GL_WHOKEY_IACT_SUMRY.GL_ORG_ELEM_CD = GL_GL_WHOKEY.GL_ORG_ELEM_CD) AND (GL_GL_WHOKEY_IACT_SUMRY.GL_DEPT_ELEM_CD = GL_GL_WHOKEY.GL_DEPT_ELEM_CD) AND (GL_GL_WHOKEY_IACT_SUMRY.GL_SUB_DEPT_ELEM_CD = GL_GL_WHOKEY.GL_SUB_DEPT_ELEM_CD) AND (GL_GL_WHOKEY_IACT_SUMRY.GL_GRANT_PGM_ELEM_CD = GL_GL_WHOKEY.GL_GRANT_PGM_ELEM_CD) AND (GL_GL_WHOKEY_IACT_SUMRY.GL_FUNCTION_ELEM_CD = GL_GL_WHOKEY.GL_FUNCTION_ELEM_CD) AND (GL_GL_WHOKEY_IACT_SUMRY.ACCTG_PRD = GL_GL_WHOKEY.ACCTG_PRD) AND (GL_GL_WHOKEY_IACT_SUMRY.ACCTG_FISC_YR = GL_GL_WHOKEY.ACCTG_FISC_YR)) INNER JOIN GRANTACCT_GRANT_MGMT ON (GL_GL_WHOKEY_IACT_SUMRY.ACCTG_FISC_YR = GRANTACCT_GRANT_MGMT.ACCTG_FISC_YR) AND (GL_GL_WHOKEY_IACT_SUMRY.ACCTG_PRD = GRANTACCT_GRANT_MGMT.ACCTG_PRD) AND (GL_GL_WHOKEY_IACT_SUMRY.GL_GRANT_PGM_ELEM_CD = GRANTACCT_GRANT_MGMT.GL_GRANT_PGM_ELEM_CD)) INNER JOIN GRANTACCT_ACCOUNTING_CONTACT ON (GRANTACCT_GRANT_MGMT.ACCTG_CONTACT_CD = GRANTACCT_ACCOUNTING_CONTACT.ACCTG_CONTACT_CD) AND (GRANTACCT_GRANT_MGMT.ACCTG_FISC_YR = GRANTACCT_ACCOUNTING_CONTACT.ACCTG_FISC_YR) AND (GRANTACCT_GRANT_MGMT.ACCTG_PRD = GRANTACCT_ACCOUNTING_CONTACT.ACCTG_PRD)
WHERE (((GL_GL_WHOKEY_IACT_SUMRY.GL_INST_ACCT_ELEM_CD)="1000") AND ((GRANTACCT_GRANT_MGMT.GRANT_PROJECT_PERIOD_CD) Is Null Or (GRANTACCT_GRANT_MGMT.GRANT_PROJECT_PERIOD_CD)=" "))
GROUP BY GRANTACCT_ACCOUNTING_CONTACT.CONTACT_NAME, GL_GL_WHOKEY_IACT_SUMRY.GL_GRANT_PGM_ID_ELEM_CD, GL_GL_WHOKEY_IACT_SUMRY.GL_GRANT_PGM_NBR_ELEM_CD, GL_GL_WHOKEY_IACT_SUMRY.ACCTG_PRD, GL_GL_WHOKEY_IACT_SUMRY.ACCTG_FISC_YR, GL_GL_WHOKEY_IACT_SUMRY.GL_GRANT_PGM_ID_ELEM_CD, GRANTACCT_GRANT_MGMT.GRANT_PROJECT_PERIOD_CD, GL_GL_WHOKEY_IACT_SUMRY.GL_FUND_ELEM_CD, GL_GL_WHOKEY_IACT_SUMRY.BUDGET_REF, GRANTACCT_GRANT_ACCOUNT.ACCTG_PRD, GRANTACCT_GRANT_ACCOUNT.ACCTG_FISC_YR
HAVING (((GL_GL_WHOKEY_IACT_SUMRY.ACCTG_PRD)=[Enter Accounting Period]) AND ((GL_GL_WHOKEY_IACT_SUMRY.ACCTG_FISC_YR)=[Enter Fiscal Year]) AND ((GL_GL_WHOKEY_IACT_SUMRY.GL_FUND_ELEM_CD)="520") AND ((GRANTACCT_GRANT_ACCOUNT.ACCTG_PRD)=[Enter Accounting Period]) AND ((GRANTACCT_GRANT_ACCOUNT.ACCTG_FISC_YR)=[Enter Fiscal Year]));

Thanks!

 

[dhookom]

I would create a form for all of the dynamic criteria rather than using parameter prompts. In addition, you might need to use Query->Parameters and enter the data types of the parameters.

Also, I would move all of the criteria to the WHERE clause rather than in the HAVING clause. The HAVING clause should be reserved for criteria against aggregated values.

Duane
Hook'D on Access
MS Access MVP