IIf function

[lmcc007]

In my tblAddresses table I have a lookup field called TypeofAddress (Business, Delivery, Home, Mailing…)

I am trying to tell it to display only one type out of the bunch: TypeofAddress 1. If TypeofAddress is not equaled to 1 then display TypeofAddress 2 and so on.

I created a query using tblAddresses. The Criteria for TypeofAddress is:

IIf([TypeofAddressID]=1,[TypeofAddressID],[TypeofAddressID]=2 Xor
([Address].[TypeofAddressID])=3 Xor
([Address].[TypeofAddressID])=7 Xor
([Address].[TypeofAddressID])=8 Xor
([Address].[TypeofAddressID])=9 Xor
([Address].[TypeofAddressID])=10 Xor
([Address].[TypeofAddressID])=11 Xor
([Address].[TypeofAddressID])=12 Xor
([Address].[TypeofAddressID])=13 Xor
([Address].[TypeofAddressID])=14 Xor
([Address].[TypeofAddressID])=15 Xor
([Address].[TypeofAddressID])=16)

I just discovered that it is not counting all my records.

Is there a better way to get this information?

Thanks!

 

[Remou]

How about:

SELECT ID, Address, AddressType
FROM Addresses As A
WHERE ID In 
    (SELECT Top 1 ID 
     FROM Addresses As B 
     WHERE A.ID=B.ID 
     ORDER BY B.AddressType)

I hope I have that right, it should be the usual SQL for selecting TOP n from each group, which appears to be what you want. It does require a unique ID.

http://lessthandot.com

 

[lmcc007]

Okay Remou,

I think I got it by creating two queries.

To get the first address record per company, I created a query using tblAddress with two fields. Saved the below as qryFirstAddresses:

Field: AddressID CompanyID
Table: tblAddress tblAddress
Total: First Group By

To get the additional addresses per company, I created another query, which includes the below fields and saved it as qryAddlAddresses:

Field: AddressID CompanyID [Address]![AddressID]
Table: tblAddress tblAddress
Criteria: <>[qryFirstAddresses]!
[FirstOfAddressID]