Lookup a Value from a Table in Crosstab Query Column Reference

[RAJESHKUMAR R]

Dear Experts,

I have below SQL Statement (Crosstab_Query) which is working fine! The Support I need here is,

In the Output view after Part_Number Column, I would want few values to be looked up (As Column) from another table keeping Part_Number as Lookup_Value Reference.

Table Name is FG_PN_LISTS from which following data’s to be looked up!

Crosstab Query output view must in below sequence
1^st Column is Part_Number from Crosstab (As shown in below SQL Statement)
2^nd Column has to be “Group” Column Value from FG_PN_LISTS
3^rd Column has to be “Planner” Column Value from FG_PN_LISTS
4^th Column has to be “Lead_Time” Column Value From FG_PN_LISTS
5^th Column onwards balance Result from Crosstab (As shown in below SQL Statement)

Note:
Incase Lookup value Part_Number does not exist in FG_PN_LISTS Table, The cell has to be updated with “New”

Looking forwarded your amended SQL Query which meets my above expected Result!

PARAMETERS [Forms]![MENU]![FR_DATE] DateTime;
TRANSFORM Sum([ZVRR_TO_FR].CRD_Qty) AS SumOfQty
SELECT [ZVRR_TO_FR].Part_Number
FROM ZVRR_TO_FR
GROUP BY [ZVRR_TO_FR].Part_Number
PIVOT [ZVRR_TO_FR].CRD_Month;

Thanks & Regards,
Rajeshkumar R

[CJ_London]

please provide some example data and the result you want

Also not sure why you have a parameter you are not using

[June7]

Perhaps you just need to build another query that joins the CROSSTAB to FG_PN_Lists?

[RAJESHKUMAR R]

Dear Ajax & June7,

Thanks for your instant response, For Better understanding I have attached a Sample Database here!

In the attachment, there is a Crosstab Query called “Result”, In that I would want GROUP, PLANNER & LEAD TIME to be looked up after Product Column from FG_PN_LISTS Table , Keeping Product details as lookup value reference!

I have manually generated expected result, which I have shown in “Z_Expected Result (Only for Reference)” Table!

Kindly review and provide and suggestion!

Thanks & Regards,
Rajeshkumar R

[June7]

My suggestion still applies. Did you try?

SELECT Result.*, FG_PN_LISTS.Group, FG_PN_LISTS.Planner, FG_PN_LISTS.Lead_Time
FROM FG_PN_LISTS INNER JOIN Result ON FG_PN_LISTS.Part_Number = Result.Product;

Or do the JOIN in the UNION:

SELECT Product, Qty, CRD, Format([CRD], "yyyy_mm") AS YrMo, "FO" AS Cat, Planner, Lead_Time, Group FROM Firmorder INNER JOIN FG_PN_LISTS ON FirmOrder.Product=FG_PN_LISTS.Part_Number
UNION ALL SELECT Product, Qty*-1, CRD, Format([CRD], "yyyy_mm"), "FC", Planner, Lead_Time, Group FROM Forecast INNER JOIN FG_PN_LISTS ON Forecast.Product=FG_PN_LISTS.Part_Number;

Then CROSSTAB:

TRANSFORM Sum(Fore_Firm_UNION.Qty) AS SumOfQty
SELECT Fore_Firm_UNION.Product, Fore_Firm_UNION.Planner, Fore_Firm_UNION.Lead_Time, Fore_Firm_UNION.Group
FROM Fore_Firm_UNION
GROUP BY Fore_Firm_UNION.Product, Fore_Firm_UNION.Planner, Fore_Firm_UNION.Lead_Time, Fore_Firm_UNION.Group
PIVOT Fore_Firm_UNION.YrMo;

[RAJESHKUMAR R]

Dear,

Thank You So Much for suggestion, It really works well!

Thanks & Regards,
Rajeshkumar R