Mutliple Criteria in Access query

[creyn]

I am trying to write a query for multiple criteria in the query. Can someone please tell me what I am doing wrong?

SELECT Deviations_Request.AppId, Deviations_Request.AppName, Deviations_Request.Datastdname, Deviations_Request.Devreqsubmitted, Deviations_Request.Nonadoptcomment, Deviations_Request.Nonadoptreasondesc, Deviations_Request.[Final OA Comment],
IIF([Deviations_Request.Nonadoptcomment] = "Taiwan is non GC country, DQIP is not in scope for Taiwan"
Or [Deviations_Request.Nonadoptcomment] = "India is not GC and DQIP is not in scope as of now"
Or [Deviations_Request.Nonadoptcomment] = "Data Standard adoption will happen along with Global Common migration. As of now there is no
confirmed plan available for GC migration and should be reviewed start of next year for any change."
Or [Deviations_Request.Nonadoptcomment] = "will be the part of G2C program."
Or [Deviations_Request.Nonadoptcomment] = "Will adopt once DQIP is capable on this standard."
Or [Deviations_Request.Nonadoptcomment] = "Adoption planned with Global Common"
Or [Deviations_Request.Nonadoptcomment] = "Adoption planned for future GC release"
Or [Deviations_Request.Nonadoptcomment] = "Adoption planned with GC v2"
Or [Deviations_Request.Nonadoptcomment] = "Adoption planned with future GC release"
Or [Deviations_Request.Nonadoptcomment] = "Adoption planned with GC v2. 07-Jun-2012: Arch team: User Maintainable based on RMR. System shall be enhanced to pass this information in all the upstream and downstream interfaces.ISO 2 byte numeric to be used as standard. Part of V7 scope"
Or [Deviations_Request.Nonadoptcomment] = "Adoption planned with GC v2. 07-Jun-2012: Arch team: Business maintained in ""Security subtype"" for SB and ""Product classification"" for MF. Data to be passed to DWH. No additional adoption is required,"
Or [Deviations_Request.Nonadoptcomment] = "Adoption planned with GC v2. 07-Jun-2012: Arch team: Lookup from RM. System shall be enhanced to capture the GOC code in addition to custoemr RC from RM and pass it to downstream interfaces.Part of V7 scope"
Or [Deviations_Request.Nonadoptcomment] = "Adoption planned with GC v2. 07-Jun-2012: Disc with Arch team: Pass through. Adoption is based on the source provided PRS Data to be passed to DWH. Planned with SEA NWO APBC11070347"
Or [Deviations_Request.Nonadoptcomment] = "Adoption planned with GC v2. 20-Jul-2012: Adoption planed for V7, pending V7 budget approve"
Or [Deviations_Request.Nonadoptcomment] = "Adoption planned with GC v2. 6-7-12: Discussion with Architecture team: User Maintained based on RMR. System shall be enhanced to pass this information in all the upstream/downstream interfaces. 3 byte Char to be used as standard
Part of V7 scope"
Or [Deviations_Request.Nonadoptcomment] = "Data Standard adoption will happen along with Global Common migration. As of now there is no
confirmed plan available for GC migration and should be reviewed start of next year for any change", "DQIP", ",
IIF([Deviations_Request.Nonadoptcomment] = "Citiplanner to get feed from RM before sending the same to downstream systems.", "CITI", "")) AS Information_Column
FROM Deviations_Request
GROUP BY Deviations_Request.AppId, Deviations_Request.AppName, Deviations_Request.Datastdname, Deviations_Request.Devreqsubmitted, Deviations_Request.Nonadoptcomment, Deviations_Request.Nonadoptreasondesc, Deviations_Request.[Final OA Comment], Deviations_Request.Information;

 

[james_lankford]

why are you using a "group by"

I don't see a "count" or "sum" or other function

get rid of the entire "group by" and just do "select distinct ..."

 

[stumac]

I would advise you to stop using the 'Deviations_Request.Nonadoptcomment' to try and gather a grouped reason for nonadopt. Instead I would add in another field - with only the options you would like to group by. I would then run a series of update queries to update that field as required for existing data. It may take a bit more time upfront but will definitely save you a lot off effort in the future if this dataset is being updated/added to.

Otherwise as more data is added you will continually have to update this query.

One issue with your current query is probably the number of characters that make up the 'Information Column', it looks like there is a 1024 character limit for this (There is in the design grid view anyway). If you cannot implement the additional field then consider using wildcards to shorten/reduce your OR statements

e.g.: [Deviations_Request.Nonadoptcomment] Like "Adoption*" - would probably get rid of quite a few of them
Also you can remove the table name from everywhere except the From statement: 'Deviations_Request.Nonadoptcomment' becomes 'Nonadoptcomment'

For info, details of Access limits: Access 2016 specifications - Access

 

[stumac]

Here is an example of how it could look using James suggestion and my suggestion:

SELECT DISTINCT AppId, AppName, Datastdname, Devreqsubmitted, Nonadoptcomment, Nonadoptreasondesc, [Final OA Comment], 
IIf((Nonadoptcomment Like "* GC *" Or 
Nonadoptcomment="will be the part of G2C program." Or 
Nonadoptcomment Like "*Adopt*"),"DQIP",IIf(Nonadoptcomment Like"Citi*","CITI","")) AS Information_Column
FROM Deviations_Request;

You may need to adjust the like statements if you have data that will match one of them erroneously.

 

[creyn]

Thank you. That worked.