Is this possible

[amauricio2]

ok I will start by stating the I already know that the table structure is all wrong,I know. So here goes masters. I have a query that would like to have a create query field evaluate field data in a field called "Display location" then I would like based on the content of Display location field for example "WINE WOOD BOX" then the field would pull the data in another field
TEST1: IIf([MTCQ].[Display Location]="WINE WOOD BOX",[REQUIRED SIDESTACKS],"0")
Now this works but I want to then do a 2nd then a 3rd lookup So now and when I enter a 2nd it looks like this TEST1: IIf([MTCQ].[Display Location]="WINE ENDCAP, POD, SIDESTACK, OR WOOD BOX",[REQUIRED SIDESTACKS],"q") Or IIf([MTCQ].[Display Location]="WINE WOOD BOX",[REQUIRED WOOD BOXES])
This time I get -1

 

[dhookom]

I would not create a complex expression in a query. Consider passing the Display Location, Required Sidestacks, and Required Wood Boxes fields to a user-defined function that would return the correct value. To create the function, it would be important to know the data types of the fields and expected return value.

It looks like your IIf() statement might return a number or text. IMHO, your expression should return only a single data type.


Duane
Hook'D on Access
MS Access MVP

 

[amauricio2]

ok here goes I uploaded a file to better assist. I am using a query called BDMSSQBS and the end result is the report called ZZPlanner_StoreNEW the table is called MTCQ, The Display Location will decide which field to compare to the Ranking field. Does that make sense? and thanks for even trying to help me. I really appreciate it.

Display location is a text field and the required fields will be Number. Ulitmately at the the I will compare my ranking field against the Test1 field so I can decide which field to populate([Display_STatus] or"Approved Flex Display")

 

[dhookom]

How about providing use the exact field names involved, their data types, if they might be null, and how they are used to derive a specific value of a specific data type.

Duane
Hook'D on Access
MS Access MVP

 

[amauricio2]

I have this in a query but it wasn't not working only because I need to add one more thing a criteria, I need the Display_Status to look at another field called Display location where one of these fields names is located and then run the comparison based on the field. so for example:
"Ranking"- Number field (Example= 2)
"Required Endcaps" - Number field (Example= 4)
"Required Sidestacks"- Number field (Example= 1)
"Display Location" -Text (Example= "WINE POD OR SIDESTACK")
"Display Status"- Text (Example= "Required Displays")
So for instance I want to create a field that will look at Display location and based on display location will look (for this example) at the Required Sidestack and return the number 1.

 

[dhookom]

I thought you would provide about 5 records with the significant field values, logic of the calculation/expression, and desired results.

Duane
Hook'D on Access
MS Access MVP

 

[amauricio2]

DISPLAY LOCATION REQ ENDCAP REQ SIDESTACK REQUIRED RANKING DISPLAY STATUS
wine endcap 1

 

[amauricio2]

ok sorry for submitting too early without sufficient info, but here goes I uploaded a sample of the query are you able to look at it? The fields are in the upload have the field currently in my DB

Actually the post 11 Dec 08 11:47 does have actual data to the right and i am just wanting a way to pass through the value of the field that the display location has listed so i can perform another formula. I really , really need to figure this out please help

iif
"Display Location" = WINE POD OR SIDESTACK, then go pull the value in the "Required Sidestacks" field is 1 then the field I am creating call it "Display Status" will populate value 1, or iif this time
"Display Location" = WINE WOOD BOX, then go pull the value in the "Required WOOD BOX" which is 14 then the field I am creating call it "Display Status" will populate value 14 and so on..
the

 

[amauricio2]

attachment now included

 

[Remou]

Do you want:

SELECT MZtest.[DISPLAY LOCATION], 
MZtest.[REQUIRED ENDCAPS], 
MZtest.[REQUIRED SIDESTACKS], 
MZtest.[REQUIRED WOOD BOXES], 
IIf([Display Location]="WINE ENDCAP, POD, SIDESTACK, OR WOOD BOX",[REQUIRED SIDESTACKS],
IIf([Display Location]="WINE WOOD BOX",[REQUIRED WOOD BOXES],0)) AS TEST1
FROM MZtest

Or
something more on these lines?

SELECT MZtest.[DISPLAY LOCATION], 
MZtest.[REQUIRED ENDCAPS], 
MZtest.[REQUIRED SIDESTACKS], 
MZtest.[REQUIRED WOOD BOXES], 
IIf(InStr([Display Location],"SIDESTACK")>0 And InStr([Display Location],"WOOD BOX")>0,[REQUIRED SIDESTACKS],
IIf([Display Location]="WINE WOOD BOX",[REQUIRED WOOD BOXES],Null)) AS TEST1
FROM MZtest;

 

[amauricio2]

good question and GREAT ANSWERS but if you will notice I am merely a novice at this don't know where it would go.. .In the query or the code and then where would i put it and how. Thanks alot Remou I really appreciate your help on this

 

[Remou]

Just paste it into an SQL window for now, and see if it gets what you want, then we can see what other fun we can have with these queries.

 

[dhookom]

Maybe someone else will open your file. I expected to see about 5 different records with the desired display. Do you understand how to write any code (no offense intended, just asking)? It would help if you could provide something a little more structured like:

If [Display Location] = "WINE POD OR SIDESTACK" Then

End If

I don't know if this statement:
[blue]then go pull the value in the "Required Sidestacks" field is 1[/blue]
should actually be:
[blue]then go pull the value in the "Required Sidestacks" field if 1 [/blue]

When posting in these fora you might want to use []s to identify field names and "s to delimit text values.

Duane
Hook'D on Access
MS Access MVP

 

[amauricio2]

Thank you Duane for the direction I really appreciate it, Remou your suggestion was PERFECT, now what I need to know from either one of you would be how to Nest, include, add to my bigger query??? Thanks guys

 

[Remou]

Please post an attempt, even if it does not work the way you want it to work.

 

[amauricio2]

Well I guess my question is really can I put it into another query, remember that was just one field in a query, if i run the SQL it does the job now i just need to make it a field this is what I believe it to be something like for the field:
NewStatus: (SELECT MZtest.[DISPLAY LOCATION], MZtest.[REQUIRED ENDCAPS], MZtest.[REQUIRED SIDESTACKS], MZtest.[REQUIRED WOOD BOXES], IIf([Display Location]="WINE ENDCAP, POD, SIDESTACK, OR WOOD BOX",[REQUIRED wood boxes],IIf([Display Location]="WINE WOOD BOX",[REQUIRED WOOD BOXES],0)) AS TEST1 FROM MZtest)

I actually ran this one just like this in my new field and it returned the following:
You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.


So here is what I have so far in my original query
SELECT MTCQ.[ST COUNT], MTCQ.[CORPORATE NUMBER], MTCQ.[STORE NAME], MTCQ.GROUP, MTCQ.CLUSTER, MTCQ.REGION, MTCQ.DIVISION, MTCQ.DISTRIBUTOR, MTCQ.[RNDC REGION], MTCQ.[GLAZERS REGION], MTCQ.[START DATE], MTCQ.[END DATE], MTCQ.MONTH, MTCQ.YEAR, MTCQ.[DISPLAY LOCATION], MTCQ.[DISPLAY TYPE], MTCQ.[TOTAL DISPLAYS], MTCQ.[NUMBER OF ENDCAPS], MTCQ.[NUMBER OF SIDESTACKS], MTCQ.[NUMBER OF WOOD BOXES], MTCQ.[REQUIRED ENDCAPS], MTCQ.[REQUIRED SIDESTACKS], MTCQ.[REQUIRED WOOD BOXES], MTCQ.[FLEX ENDCAPS], MTCQ.[FLEX SIDESTACKS], MTCQ.[FLEX WOOD BOXES], MTCQ.UPC, MTCQ.[ITEM DESCRIPTION], MTCQ.[PRICING STARTS], MTCQ.[PRICING ENDS], MTCQ.TPR, MTCQ.[DIRECT IMPORT], MTCQ.[STORE PRORATION], MTCQ.[CASE PACK], MTCQ.SIZE, MTCQ.VARIETAL, MTCQ.[AVERAGE POG RETAIL], MTCQ.[RETAIL PRICE PER BOTTLE], MTCQ.[COST PER BOTTLE], MTCQ.[SUGGESTED DEAL RETAIL], MTCQ.[DEAL COST PER BOTTLE], MTCQ.[AD SUPPORT], MTCQ.DEMOS, MTCQ.[STORE POG], MTCQ.COMMENTS, MTCQ.[BDM STATUS], MTCQ.RANKING, Tbl_AvgPlanRetail.[AvgOfSUGGESTED DEAL RETAIL], [SALAS - REQUIRED TABLE].REQUIRED, Tbl_Compliance.[REQUIRED CASES], MTCQ.tncp, Tbl_AvgPlanRetail.MARGIN, IIf([REQUIRED]=0,[DISPLAY STATUS],IIf([RANKING]>([REQUIRED]),"APPROVED FLEX DISPLAYS",[DISPLAY STATUS])) AS DISPLAY_STATUS, MTCQ.[DISPLAY STATUS], MTCQ.[TOTAL APPROVED], IIf([tncp]=0,[Required Cases],[tncp]) AS SUGPRO, MTCQ.INPOG, MTCQ.Measurestatus, (SELECT MZtest.[DISPLAY LOCATION],MZtest.[REQUIRED ENDCAPS],
MZtest.[REQUIRED SIDESTACKS],
MZtest.[REQUIRED WOOD BOXES],
IIf(InStr([Display Location],"SIDESTACK")>0 And InStr([Display Location],"WOOD BOX")>0,[REQUIRED SIDESTACKS],
IIf([Display Location]="WINE WOOD BOX",[REQUIRED WOOD BOXES],Null)) AS TEST1
FROM MZtest) AS NewStatus
FROM Tbl_Compliance RIGHT JOIN (Tbl_AvgPlanRetail INNER JOIN ([CONTROL TABLE BY STORE] INNER JOIN ([SALAS - REQUIRED TABLE] INNER JOIN MTCQ ON [SALAS - REQUIRED TABLE].[CORPORATE NUMBER] = MTCQ.[CORPORATE NUMBER]) ON ([CONTROL TABLE BY STORE].[BDM STATUS] = MTCQ.[BDM STATUS]) AND ([CONTROL TABLE BY STORE].MONTH = MTCQ.MONTH) AND ([CONTROL TABLE BY STORE].YEAR = MTCQ.YEAR)) ON (Tbl_AvgPlanRetail.[CORPORATE NUMBER] = [SALAS - REQUIRED TABLE].[CORPORATE NUMBER]) AND (Tbl_AvgPlanRetail.YEAR = MTCQ.YEAR) AND (Tbl_AvgPlanRetail.MONTH = MTCQ.MONTH) AND (Tbl_AvgPlanRetail.[BDM STATUS] = MTCQ.[BDM STATUS])) ON Tbl_Compliance.[DISPLAY LOCATION] = MTCQ.[DISPLAY LOCATION]
WHERE (((MTCQ.[CORPORATE NUMBER])=24));