Apply number to proper space

[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

 

[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;