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

Question

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!

I think you need to rephrase this completely, and to list sample input and desired output. — Gustav, Oct 06 '16 at 09:27
So from your sample data, do you want the same number of rows, but row 1 should have a value of 20 instead of 78? Or do you want to omit row 1? — MoondogsMaDawg, Oct 06 '16 at 14:34

score 0 · Answer 1 · answered Oct 06 '16 at 11:41

0

Could you please clarify what exactly is your issue?

Why do you use "grouping" anyway?

Why do you not simply include the Division to your output report?

answered Oct 06 '16 at 11:41

snibbo

179
11

score 0 · Accepted Answer · answered Oct 20 '16 at 03:45

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.

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

2 Answers2