Anne Anne - 1 month ago 10
SQL Question

Sort Order by with Criteria in MS Access Design View / SQL

I am very new to MS Access,
I'm working on automating report using ms access, where user is importing raw excel files, each files is used to get the data to a specific field. My problem is there are multiple different divisions(i.e SG, BR, US, EU, PH) which incorporates in Materials. there is instances where there is multiple same material number but different divisions. I need to get the material value which division belongs to SG. what users do manually to identify SG is to concatenate division with Material since the only lookup in the master query or output file is the Material number, division field is not included. for example

Material Division Value Concat
89098 BR092 78 BR09289098
89098 SG879 20 SG87989098
90921 EU939 80 EU93090921


Since I'm having trouble to get the number of lines to the base number of the output what I'm doing in design view is to set the total to First instead of Group By, so basically what number comes first is what the query is getting .. I need to have the Material number with Value of SG if they belong to the same material, as for the other divisions they have no problem they have to retain their values. I've tried using
Division: First
((IIf(Left([Concat],2)='SG','1',IIf(Left([Concat],2)='BR','2','3'))))

then set it to ascending but doesn't work out. The material is still getting the value of BR division.
I need this to be in design view code or sql view code.
It'll be a great help or more over you'll be saving my a$$ if someone can give me the answer. Thanks!

Answer

SOLVED: from the posted problem, I've created series of queries. from the raw source table I've created 1st: Material with Unique Count (done this with criteria 1) 2nd: Material with NO SG division and 3rd: Material with SG division,

with having this code under the query of Material with NO SG

`SCOPE: IIf([Query_SC_and_Purchase_Price_Scope_SG].[SCOPE] Is Null,[Query_SC_and_Purchase_Price_Scope_NO_SG].[SCOPE],[Query_SC_and_Purchase_Price_Scope_SG].[SCOPE])`

This for Material with SG Code query

   SCOP: IIf([Query_SC_and_Purchase_Price_Scope_SG].[SCOPE] Is Null,[Query_SC_and_Purchase_Price_Scope_NO_SG].[SCOPE],[Query_SC_and_Purchase_Price_Scope_SG].[SCOPE])

Gathering all those datas and checking I've created a UNION ALL query

 `SELECT Query_SC_and_Purchase_Price_Scope_Unique.* 
    FROM Query_SC_and_Purchase_Price_Scope_Unique
    UNION ALL
    SELECT Query_SC_and_Purchase_Price_Scope_SGNOSG_SG.* 
    FROM Query_SC_and_Purchase_Price_Scope_SGNOSG_SG
    UNION ALL SELECT Query_SC_and_Purchase_Price_Scope_SGNOSG_NOSG.*
    FROM Query_SC_and_Purchase_Price_Scope_SGNOSG_NOSG;
    `

I've come with my correct values.