I have this form that works
Code:
" SELECT TblLicence.*,
Nz (q.Royalty, 0) AS RoyaltyCalc,
Nz (q.CurrencyType, "") AS CurrencyType_Qry,
Nz (q.IPOwnerShare, 0) AS IPOwnerShare,
Nz (q.SAPOShare, 0) AS SAPOShare
FROM
(
(
TblLicence
INNER JOIN Profile ON TblLicence.Nr = Profile.Nr
)
INNER JOIN Plants ON TblLicence.PlantID = Plants.PlantID
)
LEFT JOIN qryRoyaltyCurrencySplit AS q ON TblLicence.PlantID = q.PlantID;"
I want to add the currency type to detect currency type automatically. I havetried serveral versions of this code and it doe not work.
From :
Code:
SELECT DISTINCTROW TblPlantsPrices.PlantID,
TblPlantsPrices.SoldAs,
Plants.Cultivar,
Plants.Clone,
Plants.Ipno,
Plants.PlantType,
Plants.VarietyList,
Plants.PhytoCert,
TblPlantsPrices.Winter,
TblPlantsPrices.Summer,
TblPlantsPrices.Autumn,
TblPlantsPrices.Grape,
TblPlantsPrices.NoSeason,
TblPlantsPrices.Price,
TblPlantsPrices.[Royalty/Levy],
TblPlantsPrices.CurrencyType AS OriginalCurrencyType,
TblPlantsPrices.[Royalty/Levy_LastYear],
TblPlantsPrices.SummerAll,
TblPlantsPrices.AutumnAll,
TblPlantsPrices.WinterAll,
TblPlantsPrices.GrapeAll,
TblPlantsPrices.Scion,
TblPlantsPrices.Root,
TblPlantsPrices.KWVAll,
TblPlantsPrices.Vat,
TblPlantsPrices.[Other Royalties],
TblPlantsPrices.Other_Royalties_LastYear,
TblPlantsPrices.Royalty AS Royalty_Original,
TblPlantsPrices.Levy,
TblLicence.PlantBreederRights,
IIf(
[PlantBreederRights] = Yes,
"Royalty",
IIf([PlantBreederRights] = No, "Levy", "")
) AS RorL,
TblPlantsPrices.ExchangeRateMonth,
TblPlantsPrices.RLCollectedBy,
Nz (qryRoyaltyCurrencySplit.Royalty, 0) AS Royalty,
Nz (qryRoyaltyCurrencySplit.CurrencyType, "") AS CurrencyType,
Nz (qryRoyaltyCurrencySplit.IPOwnerShare, 0) AS IPOwnerShare,
Nz (qryRoyaltyCurrencySplit.SAPOShare, 0) AS SAPOShare
FROM
(
(
Plants
INNER JOIN TblPlantsPrices ON Plants.PlantID = TblPlantsPrices.PlantID
)
LEFT JOIN TblLicence ON Plants.PlantID = TblLicence.PlantID
)
LEFTJOIN qryRoyaltyCurrencySplit ON Plants.PlantID = qryRoyaltyCurrencySplit.PlantID;
I tried
Code:
SELECT TblLicence.*,
Nz(q.Royalty, 0) AS RoyaltyCalc,
-- This subquery automatically fetches the CurrencyType from the TblPlantsPrices table for each plant.
-- The "TOP 1" ensures that you only get one result, preventing errors if a plant has multiple price entries.
(
SELECT
TOP 1 TblPlantsPrices.CurrencyType
FROM
TblPlantsPrices
WHERE
TblPlantsPrices.PlantID = TblLicence.PlantID
) AS CurrencyType_Automatic,
Nz(q.IPOwnerShare, 0) AS IPOwnerShare,
Nz(q.SAPOShare, 0) AS SAPOShare
FROM
(
(
TblLicence
INNER JOIN Profile ON TblLicence.Nr = Profile.Nr
)
INNER JOIN Plants ON TblLicence.PlantID = Plants.PlantID
)
LEFT JOIN qryRoyaltyCurrencySplit AS q ON TblLicence.PlantID = q.PlantID;
But it breaks my code.