Bob Collison
asked on
Access 2010 Query Expression
Hi Experts,
This shouldn't be a problem for me but it is. I can't get the following to select properly.
The field is called [AGE_GROUP] and may contain the characters "A" or 'Y".
The Criteria row code is:
IIf([00_PARAMETER_DATA].[A LPHA_PARM_ 3]="B",Lik e "*",[00_PARAMETER_DATA].[A LPHA_PARM_ 3])
What should happen:
- If ALPHA_PARAMETER_3 = "B" then select all records. i.e. Value "A" and "Y" records.
- If ALPHA_PARAMETER_3 = "A" then select only value "A" records.
- If ALPHA_PARAMETER_3 = "Y" then select only value "Y" records.
What should the syntax be?
Thanks.
Bob Collison.
This shouldn't be a problem for me but it is. I can't get the following to select properly.
The field is called [AGE_GROUP] and may contain the characters "A" or 'Y".
The Criteria row code is:
IIf([00_PARAMETER_DATA].[A
What should happen:
- If ALPHA_PARAMETER_3 = "B" then select all records. i.e. Value "A" and "Y" records.
- If ALPHA_PARAMETER_3 = "A" then select only value "A" records.
- If ALPHA_PARAMETER_3 = "Y" then select only value "Y" records.
What should the syntax be?
Thanks.
Bob Collison.
ASKER
Hi Expert,
I'm not up to speed on SQL Code so I really need a solution using the Access Query Design Tool.
Thanks.
Bob C.
I'm not up to speed on SQL Code so I really need a solution using the Access Query Design Tool.
Thanks.
Bob C.
Since you haven't told us the tablename and fields of the table containing the [AGE_GROUP] column, I can only suggest example SQL.
If you change the table name from "mytable" to your actual table name and switch from the SQL view to the QBE designer view, it should at least give you a first start.
If you change the table name from "mytable" to your actual table name and switch from the SQL view to the QBE designer view, it should at least give you a first start.
or post your database
ASKER
Hi Expert,
Below is the SQL Code for your reference.
In actual fact there are 15 tables in the query so the SQL is very long and complex. The specific table where this field is located is [10_MEMBER_MSTR] which contains Organization Member records. The [AGE_GROUP] field identifies whether the Member is an Adult (A) or a Youth (Y).
The way this works is that the Form is accessing one Member Record. The User picks two Options (One or All) Members and Age Group (A (Adult), Y (Youth) or B (Both). The parameters are written to the [00_PARAMETER_DATA] Table and the query links this table to the Member Master Table via a common field [GENERIC_LINK], extracts the data and displays it in the report.
The criteria other than that associated with the [AGE_GROUP] field work correctly.
SELECT [00_PARAMETER_DATA].USER_O BJECT_KEY, [00_PARAMETER_DATA].SYS_US ER_KEY, [00_PARAMETER_DATA].OBJECT _KEY, [00_PARAMETER_DATA].ALPHA_ PARM_1, [00_PARAMETER_DATA].ALPHA_ PARM_2, [00_PARAMETER_DATA].ALPHA_ PARM_3, [00_PARAMETER_DATA].PADDED _PERIOD_KE Y, [10_MEMBER_MSTR].SYS_MEMBE R_KEY, [PADDED_PERIOD_KEY] & [10_MEMBER_MSTR].SYS_MEMBE R_KEY AS PERIOD_MEMBER_KEY, [10_MEMBER_MSTR].MMS_MEMBE R_ID, [10_MEMBER_MSTR].CREATE_ST AMP, [10_MEMBER_MSTR].SYS_MEMBE R_RECORD_T YPE, [02_SYS_MEMBER_STATUS_MSTR ].REG_STAT US_CD, [02_SYS_MEMBER_STATUS_MSTR ].SHORT_NA ME AS 02_SYS_MEMBER_STATUS_MSTR_ SHORT_NAME , [10_MEMBER_MSTR].MMS_SCREE N_STATUS_C D, [02_MMS_SCREEN_STATUS_MSTR ].LKUP_SHO RT_NAME, [02_SYS_MEMBER_TYPE_MSTR]. SYS_MEMBER _TYPE_CD, [10_MEMBER_MSTR].PRC_STATU S_CD, [02_SYS_PRC_STATUS_MSTR].S HORT_NAME, [10_MEMBER_MSTR].PRC_START _DATE, [10_MEMBER_MSTR].PRC_END_D ATE, [10_MEMBER_MSTR].VSS_STATU S_CD, [02_SYS_PRC_STATUS_MSTR_1] .SHORT_NAM E, [10_MEMBER_MSTR].VSS_START _DATE, [10_MEMBER_MSTR].VSS_EXPIR Y_DATE, [02_SYS_MEMBER_TYPE_MSTR]. SHORT_NAME AS 02_SYS_MEMBER_TYPE_MSTR_SH ORT_NAME, [10_MEMBER_MSTR].SALUTATIO N, [10_MEMBER_MSTR].SURNAME, [10_MEMBER_MSTR].FIRST_NAM E, [10_MEMBER_MSTR].MIDDLE_NA ME, [10_MEMBER_MSTR].INITIALS, [10_MEMBER_MSTR].NICKNAME, [10_MEMBER_MSTR].LANGUAGE_ 1, [10_MEMBER_MSTR].LANGUAGE_ 2, [10_MEMBER_MSTR].RELIGION, [10_MEMBER_MSTR].BIRTH_DAT E, [10_MEMBER_MSTR].AGE, [10_MEMBER_MSTR].AGE_GROUP , [10_MEMBER_MSTR].LAST_SCHO OL, [10_MEMBER_MSTR].LAST_GRAD E, [10_MEMBER_MSTR].HIGHEST_E DUCATION, [10_MEMBER_MSTR].GENDER_CD , [02_PARENTAL_ACCESS_MSTR]. PARENTAL_A CCESS_CD, [02_PARENTAL_ACCESS_MSTR]. SHORT_NAME AS 02_PARENTAL_ACCESS_MSTR_SH ORT_NAME, [10_MEMBER_MSTR].PHOTO_CON SENT_CD, [10_MEMBER_MSTR].HOME_EMAI L_1, [10_MEMBER_MSTR].HOME_EMAI L_2, [10_MEMBER_MSTR].HOME_PHON E_1, [10_MEMBER_MSTR].HOME_PHON E_2, [10_MEMBER_MSTR].CELL_PHON E_1, [10_MEMBER_MSTR].HOME_ADDR ESS_KEY, [03_GEO_ADDRESS_MSTR].ADDR _LN_1, [03_GEO_ADDRESS_MSTR].ADDR _LN_2, [03_GEO_ADDRESS_MSTR].ADDR _LN_3, [03_GEO_ADDRESS_MSTR].ADDR _LN_4, [03_GEO_ADDRESS_MSTR].ADDR _LN_5, [03_GEO_ADDRESS_MSTR].ADDR _LN_6, [10_MEMBER_MSTR].EMPLOYER_ NAME, [10_MEMBER_MSTR].JOB_TITLE , [10_MEMBER_MSTR].WORK_ADDR ESS_KEY, [03_GEO_ADDRESS_MSTR_1].AD DR_LN_1, [03_GEO_ADDRESS_MSTR_1].AD DR_LN_2, [03_GEO_ADDRESS_MSTR_1].AD DR_LN_3, [03_GEO_ADDRESS_MSTR_1].AD DR_LN_4, [03_GEO_ADDRESS_MSTR_1].AD DR_LN_5, [03_GEO_ADDRESS_MSTR_1].AD DR_LN_6, [10_MEMBER_MSTR].WORK_EMAI L_1, [10_MEMBER_MSTR].WORK_PHON E_1, [10_MEMBER_MSTR].WORK_PHON E_1_EXT, [10_MEMBER_MSTR].WORK_PHON E_2, [10_MEMBER_MSTR].WORK_PHON E_2_EXT, [10_MEMBER_MSTR].HEALTH_ID , [10_MEMBER_MSTR].MEDICAL_S UMMARY, [10_MEMBER_MSTR].MEDICAL_D ETAILS, [10_MEMBER_MSTR].NOTES, [10_MEMBER_MSTR].CONTACT_1 _KEY, [10_MEMBER_MSTR_1].SALUTAT ION AS C1_SALUTATION, [10_MEMBER_MSTR_1].SURNAME AS C1_SURNAME, [10_MEMBER_MSTR_1].FIRST_N AME AS C1_FIRST_NAME, [10_MEMBER_MSTR_1].MIDDLE_ NAME AS C1_MIDDLE_NAME, [10_MEMBER_MSTR_1].INITIAL S AS C1_INITIALS, [10_MEMBER_MSTR_1].HOME_PH ONE_1 AS C1_HOME_PHONE_1, [10_MEMBER_MSTR].CONTACT_1 _RELATIONS HIP, [10_MEMBER_MSTR].CONTACT_1 _ADDRESS_K EY, [03_GEO_ADDRESS_MSTR_2].AD DR_LN_1, [03_GEO_ADDRESS_MSTR_2].AD DR_LN_2, [03_GEO_ADDRESS_MSTR_2].AD DR_LN_3, [03_GEO_ADDRESS_MSTR_2].AD DR_LN_4, [03_GEO_ADDRESS_MSTR_2].AD DR_LN_5, [03_GEO_ADDRESS_MSTR_2].AD DR_LN_6, [10_MEMBER_MSTR].CONTACT_2 _KEY, [10_MEMBER_MSTR_2].SALUTAT ION AS C2_SALUTATION, [10_MEMBER_MSTR_2].SURNAME AS C2_SURNAME, [10_MEMBER_MSTR_2].FIRST_N AME AS C2_FIRST_NAME, [10_MEMBER_MSTR_2].MIDDLE_ NAME AS C2_MIDDLE_NAME, [10_MEMBER_MSTR_2].INITIAL S AS C2_INITIALS, [10_MEMBER_MSTR_2].HOME_PH ONE_1 AS C2_HOME_PHONE_1, [10_MEMBER_MSTR].CONTACT_2 _RELATIONS HIP, [10_MEMBER_MSTR].CONTACT_2 _ADDRESS_K EY, [03_GEO_ADDRESS_MSTR_3].AD DR_LN_1, [03_GEO_ADDRESS_MSTR_3].AD DR_LN_2, [03_GEO_ADDRESS_MSTR_3].AD DR_LN_3, [03_GEO_ADDRESS_MSTR_3].AD DR_LN_4, [03_GEO_ADDRESS_MSTR_3].AD DR_LN_5, [03_GEO_ADDRESS_MSTR_3].AD DR_LN_6, [10_MEMBER_MSTR].BASE_REC
FROM (((((00_PARAMETER_DATA INNER JOIN (((((((10_MEMBER_MSTR LEFT JOIN 03_GEO_ADDRESS_MSTR ON [10_MEMBER_MSTR].HOME_ADDR ESS_KEY = [03_GEO_ADDRESS_MSTR].ADDR ESS_KEY) LEFT JOIN 02_SYS_MEMBER_STATUS_MSTR ON [10_MEMBER_MSTR].REG_STATU S_CD = [02_SYS_MEMBER_STATUS_MSTR ].REG_STAT US_CD) LEFT JOIN 02_SYS_MEMBER_TYPE_MSTR ON [10_MEMBER_MSTR].SYS_MEMBE R_TYPE = [02_SYS_MEMBER_TYPE_MSTR]. SYS_MEMBER _TYPE_CD) LEFT JOIN 03_GEO_ADDRESS_MSTR AS 03_GEO_ADDRESS_MSTR_1 ON [10_MEMBER_MSTR].WORK_ADDR ESS_KEY = [03_GEO_ADDRESS_MSTR_1].AD DRESS_KEY) LEFT JOIN 03_GEO_ADDRESS_MSTR AS 03_GEO_ADDRESS_MSTR_2 ON [10_MEMBER_MSTR].CONTACT_1 _ADDRESS_K EY = [03_GEO_ADDRESS_MSTR_2].AD DRESS_KEY) LEFT JOIN 03_GEO_ADDRESS_MSTR AS 03_GEO_ADDRESS_MSTR_3 ON [10_MEMBER_MSTR].CONTACT_2 _ADDRESS_K EY = [03_GEO_ADDRESS_MSTR_3].AD DRESS_KEY) LEFT JOIN 02_PARENTAL_ACCESS_MSTR ON [10_MEMBER_MSTR].PARENTAL_ ACCESS_CD = [02_PARENTAL_ACCESS_MSTR]. PARENTAL_A CCESS_CD) ON [00_PARAMETER_DATA].GENERI C_LINK = [10_MEMBER_MSTR].GENERIC_L INK) LEFT JOIN 10_MEMBER_MSTR AS 10_MEMBER_MSTR_1 ON [10_MEMBER_MSTR].CONTACT_1 _KEY = [10_MEMBER_MSTR_1].SYS_MEM BER_KEY) LEFT JOIN 10_MEMBER_MSTR AS 10_MEMBER_MSTR_2 ON [10_MEMBER_MSTR].CONTACT_2 _KEY = [10_MEMBER_MSTR_2].SYS_MEM BER_KEY) LEFT JOIN 02_SYS_PRC_STATUS_MSTR ON [10_MEMBER_MSTR].PRC_STATU S_CD = [02_SYS_PRC_STATUS_MSTR].P RC_STATUS_ CD) LEFT JOIN 02_SYS_PRC_STATUS_MSTR AS 02_SYS_PRC_STATUS_MSTR_1 ON [10_MEMBER_MSTR].VSS_STATU S_CD = [02_SYS_PRC_STATUS_MSTR_1] .PRC_STATU S_CD) LEFT JOIN 02_MMS_SCREEN_STATUS_MSTR ON [10_MEMBER_MSTR].MMS_SCREE N_STATUS_C D = [02_MMS_SCREEN_STATUS_MSTR ].MMS_SCRE EN_STATUS_ CD
WHERE ((([00_PARAMETER_DATA].USE R_OBJECT_K EY)=[Forms ]![M-00-00 0 - Scout Admin System - Main Menu]![F-00-001 - System User Lookup SubForm].[Form]![SYS_USER_ KEY] & "R-10-210**") AND (([10_MEMBER_MSTR].SYS_MEM BER_KEY)=I If([00_PAR AMETER_DAT A].[ALPHA_ PARM_1]="A ll",[10_ME MBER_MSTR] .[SYS_MEMB ER_KEY],[0 0_PARAMETE R_DATA].[A LPHA_PARM_ 2])) AND (([10_MEMBER_MSTR].SYS_MEM BER_RECORD _TYPE)="PE R") AND (([10_MEMBER_MSTR].AGE_GRO UP)=IIf([0 0_PARAMETE R_DATA].[A LPHA_PARM_ 3]="B",([1 0_MEMBER_M STR].AGE_G ROUP) Like "*",[00_PARAMETER_DATA].[A LPHA_PARM_ 3])));
Thanks.
Bob C.
Below is the SQL Code for your reference.
In actual fact there are 15 tables in the query so the SQL is very long and complex. The specific table where this field is located is [10_MEMBER_MSTR] which contains Organization Member records. The [AGE_GROUP] field identifies whether the Member is an Adult (A) or a Youth (Y).
The way this works is that the Form is accessing one Member Record. The User picks two Options (One or All) Members and Age Group (A (Adult), Y (Youth) or B (Both). The parameters are written to the [00_PARAMETER_DATA] Table and the query links this table to the Member Master Table via a common field [GENERIC_LINK], extracts the data and displays it in the report.
The criteria other than that associated with the [AGE_GROUP] field work correctly.
SELECT [00_PARAMETER_DATA].USER_O
FROM (((((00_PARAMETER_DATA INNER JOIN (((((((10_MEMBER_MSTR LEFT JOIN 03_GEO_ADDRESS_MSTR ON [10_MEMBER_MSTR].HOME_ADDR
WHERE ((([00_PARAMETER_DATA].USE
Thanks.
Bob C.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Expert,
Your solution does work but it doesn't address how to configure it using Access Query Design for those of us who don't know SQL.
Thanks.
Bob C.
Your solution does work but it doesn't address how to configure it using Access Query Design for those of us who don't know SQL.
Thanks.
Bob C.
There are some things that the query design wizard can not help. One of them is writing complex expressions, such as your IIF() function. The IIF() function comes out of the VB environment. If you want to know more about this, then the VB language is what you should study.
ASKER
Hi Experts,
Having read you comments and the Grading Criteria I agree that 'B' is the correct grading.
Thanks.
Bob C.
Having read you comments and the Grading Criteria I agree that 'B' is the correct grading.
Thanks.
Bob C.
Looking at my IIF() function again, I realized it could be simplified as follows. The false clause really only needs to be a constant TRUE value. This should perform better.
WHERE ((([00_PARAMETER_DATA].USER_OBJECT_KEY)=[Forms]![M-00-000 - Scout Admin System - Main Menu]![F-00-001 - System User Lookup SubForm].[Form]![SYS_USER_KEY] & "R-10-210**")
AND (([10_MEMBER_MSTR].SYS_MEMBER_KEY)=IIf([00_PARAMETER_DATA].[ALPHA_PARM_1]="All",[10_MEMBER_MSTR].[SYS_MEMBER_KEY],[00_PARAMETER_DATA].[ALPHA_PARM_2])) AND (([10_MEMBER_MSTR].SYS_MEMBER_RECORD_TYPE)="PER") AND
(IIf([00_PARAMETER_DATA].[ALPHA_PARM_3] In ("A","Y"),[10_MEMBER_MSTR].[AGE_GROUP]=[00_PARAMETER_DATA].[ALPHA_PARM_3],True)=True) );
Why not an A?
When Bob wrote,
When Bob wrote,
What should the syntax be?my solution comment was exactly the syntax required.
ASKER
Hi Experts,
Although this worked and I can use it I can't figure out how to do another query that is very similar, so although it provided the correct result in my opinion the syntax is not understandable / re-usable by me.
Thanks.
Bob C.
Although this worked and I can use it I can't figure out how to do another query that is very similar, so although it provided the correct result in my opinion the syntax is not understandable / re-usable by me.
Thanks.
Bob C.
until you master the subject material, you will need to post new questions for every new query.
ASKER
OK
@Bob
The query wizard is a graphical user interface (dialog window) that actually creates SQL. If the SQL gets too complicated to render in the query wizard, you have to alter the SQL text without a GUI. Union queries are one type of query that can't be created/altered in the query wizard.
You should be able to open up the query in the query wizard and learn how my solution SQL is rendered in the query wizard. It will most likely be reflected in the bottom half of the grid.
Right clicking on some of the cells should display a pop-up menu. Look for the Build menu item. If you need help creating expressions, such as an IIF() function, this might be the place to start in the query wizard.
====================
Even if you had posted your database, the query wizard is not a window that can stay open during file transfer. The query wizard is not a mechanism with which an expert can communicate a solution to you on EE.
The query wizard is a graphical user interface (dialog window) that actually creates SQL. If the SQL gets too complicated to render in the query wizard, you have to alter the SQL text without a GUI. Union queries are one type of query that can't be created/altered in the query wizard.
You should be able to open up the query in the query wizard and learn how my solution SQL is rendered in the query wizard. It will most likely be reflected in the bottom half of the grid.
Right clicking on some of the cells should display a pop-up menu. Look for the Build menu item. If you need help creating expressions, such as an IIF() function, this might be the place to start in the query wizard.
====================
Even if you had posted your database, the query wizard is not a window that can stay open during file transfer. The query wizard is not a mechanism with which an expert can communicate a solution to you on EE.
ASKER
Hi Expert,
Thanks for the information.
Bob C.
Thanks for the information.
Bob C.
Open in new window