any ideas on how to prioritize the row with criteria with the same material ? I need to capture the value of SG(division) in my query. I have base query of only 227,288 rows, and from the raw file that Im getting these values below is about 300,00++, my problem is whenever Im running my query with the base number, it only captures the first material from rows.
ID MATERIAL DIVISION SCOP(price)
1 a9000 FR390 90
2 b6790 TB900 20
3 a9000 SG2011 35
4 b6790 EU8089 90
from above example out of 227,288 files, for material a9000 the Price value I'm getting is 90 when supposedly I need to have the value of 35 from SG2011, other than SG2011, every values from other division is okay, i.e for b6790 it's okay to have the price value of 20. division SG2011 price is prioritize for materials.
I'm using MS Access 2007, I'm doing things in Design view or SQL view. wish you guys could help me with simple codes.. (iff..statements or simply in the design view..) or SQL view in Ms Access
Edit
Below is my SQL query (query test - this doesn't left join the base query because it doesn't meet the number of rows but I was able to show the multiple material)
SELECT Base_CSC.ID, Base_CSC.Material, SC_and_Purchase_Price.[Logistic Ref], SC_and_Purchase_Price.[Commercial Ref], Left([Division],2) & ([Commercial Ref]) AS [Comm Ref], Left([Division],2) & ([Logistic Ref]) AS [Log Ref], IIf(Left([Comm Ref],'2')="SG",[SCOP],'') AS [SCOP SG], SC_and_Purchase_Price.[SC in AUD] AS SCOP
FROM Base_CSC LEFT JOIN SC_and_Purchase_Price ON (Base_CSC.Material = SC_and_Purchase_Price.[Commercial Ref]) AND (Base_CSC.Material = SC_and_Purchase_Price.[Logistic Ref])
ORDER BY Base_CSC.ID;
Query Test - Image of my test query to show the materials. as shown Material : LV433620 has 3 result for material. but once I run my final query
Final Output Query - Image of my output query
LV433620 material took the value of the first line when supposedly I needed the output of SCOP SG - (I just made scop SG field to distinguish) so Instead of 257.42. I need SCOP value 215.50 for material LV433620.
below is for Final output query SQL view
SELECT Base_CSC.ID, Base_CSC.Material, Base_CSC.[Vendor Reference], Base_CSC.[Default Vendor], First(IIf(IsNull([scop3_v2.SCOP]),[scop3_v2_1.SCOP],[scop3_v2.SCOP])) AS SCOP
FROM (Base_CSC LEFT JOIN scop3_v2 AS scop3_v2_1 ON Base_CSC.Material = scop3_v2_1.scop1.SCOPLookup) LEFT JOIN scop3_v2 ON Base_CSC.Material = scop3_v2.scop2.SCOPLookup
GROUP BY Base_CSC.ID, Base_CSC.Material, Base_CSC.[Vendor Reference], Base_CSC.[Default Vendor]
ORDER BY Base_CSC.ID;
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.