Windows 7 64bit 
Access 2013
Hello,
Am new to the Access and trying to merge below the 2 queries into one. but am not able to the same. Given the details below... Please create a subquery for the below and do the needful.
1st Query:
SELECT distinct A.[Order Number], A.[Sales document], A.[Prodno _salesdoc Combi], IIf(IsNull([B].[Created Date]),"Yes",[B].[Created Date]) AS Expr1
FROM Serialized_Report_final_AND_Mapping_filds AS A LEFT JOIN BEX_Report AS B ON A.[Prodno _salesdoc Combi] = B.[Pdt+SO# Combination];
2nd Query:
SELECT DISTINCT Y.[Order Number],Y.[Sales document],Y.[Prodno _salesdoc Combin], IIf(Y.[Created Dates]="Yes",IIf(IsNull(Z.[Created Date]),"Sales Order not found","Sales order found with product mismatch"),Y.[Created Dates]) AS Expr5
FROM 30_days_BEXReport AS Y LEFT JOIN BEX_Report AS Z ON Y.[Sales document] = Z.[Sales document];
And [Order Number] is the unique field.
I inserted 2nd Query in the 1st query where its required as below;
SELECT distinct A.[Order Serial1 Number], A.[Sales document], A.[Prodno _salesdoc Combi], IIf(IsNull([B].[Created Date]),(SELECT DISTINCT Y.[Order Serial1 Number],Y.[Sales document],Y.[Prodno _salesdoc Combin], IIf(Y.[Created Dates]="Yes",IIf(IsNull(Z.[Created Date]),"Sales Order not found","Sales order found with product mismatch"),Y.[Created Dates]) AS Expr5
FROM 30_days_BEXReport AS Y LEFT JOIN BEX_Report AS Z ON Y.[Sales document] = Z.[Sales document]),[B].[Created Date]) AS Expr1
FROM Serialized_Report_final_AND_Mapping_filds AS A LEFT JOIN BEX_Report AS B ON A.[Prodno _salesdoc Combi] = B.[Pdt+SO# Combination];
By running the above subquery, i have got this error
" You have written a subquery that can return more than one field without using the EXISTS reserverd word in the main query's FROM caluse. Revise the SELECT Statement of the subquery to request one new field."
Reply With Quote
Originally Posted by orange
