Apply number to proper space

  • Thread starter Thread starter Alain
  • Start date Start date
A

Alain

Hi,

scenario: I have a query that returns multiple same id with a specific spaceno
(id=1 and spaceno = 1, id = 1 and spaceno = 2, id = 1 and spaceno = 3 and so
on)
each spaces has different rent values

I can manage to apply the required amount to spaceno 1 if it's in the
results,
park: iif(spaceno = 1 then park,"").
My problem is if spaceno 1 is not in the results, I need to apply the
required amount to spaceno2 and if spaceno 1 and 2 does not exists then it
need to be applied to spaceno3 ......

I have tried many different ways to get to the results I need but no
success, the closest is the following:
Parking2:
IIf([basicrental].[spaceno]=DMin("spaceNo","Q-BasicRentalSpaceNo","IdBranch =
basicrental.idbranch"),[Park])

The DMin does return the right value I need but it does not show on my
spaceno2 since for this specific idbranch, spaceno1 does not exists.

Can anyone help?

Thanks
Alain
 
here is the complete query

SELECT BasicRental.IdBranch, BasicRental.SF, BasicRental.AnnualRental,
BasicRental.AnnualOtherExp, BasicRental.OperExp, BasicRental.Taxes,
BasicRental.SpaceNo, IIf(BasicRental.spaceNo<>1,"",[Park]) AS Parking,
IIf([basicrental].[spaceno]=DMin("spaceNo","Q-BasicRentalSpaceNo","IdBranch =
basicrental.idbranch"),[Park]) AS Parking2, [Q-RecapParkingTaxes].Park,
[Park]/[SF] AS ParkSF, [AnnualRental]/[SF] AS RentSF, [AnnualOtherExp]/[SF]
AS OexSF, [OperExp]/[SF] AS OpexSF, [Taxes]/[SF] AS TaxSF,
BasicRental.MthToMth, BasicRental.SpaceNo, BasicRental.Floors,
BasicRental.From, BasicRental.To, tblContact.BuildingProvince,
tblContact.BuildingCountry,
nz([AnnualRental],0)+nz([AnnualOtherExp],0)+nz([OperExp],0)+nz([Taxes],0)+Nz([Park],0)
AS GOC, [GOC]/[SF] AS GOCSF,
TaxCalcTot([BuildingProvince],[BuildingCountry],[GOC]-Nz([Park],0)) AS Tax,
[Q-RecapParkingTaxes].TaxPark, [GOC]+Nz([Tax],0)+nz([TaxPark],0) AS NET,
[NET]/[SF] AS NetSF, [TaxPark]/[SF] AS TaxParkSF
FROM ((Branch LEFT JOIN [Q-RecapParkingTaxes] ON Branch.IdBranch =
[Q-RecapParkingTaxes].IdBranch) LEFT JOIN BasicRental ON Branch.IdBranch =
BasicRental.IdBranch) LEFT JOIN tblContact ON Branch.IdBranch =
tblContact.IdBranch
WHERE (((BasicRental.From)<=Now()) AND ((BasicRental.To)>=Now())) OR
(((BasicRental.MthToMth)=True))
ORDER BY BasicRental.IdBranch, BasicRental.SpaceNo, BasicRental.SpaceNo,
BasicRental.From;
 
Back
Top