iif statement help

[label027]

Good morning everyone,


I am creating a database right now that I will attempt to explain to you. There is a table (lets call it table 1) that I pull info into about a bunch of different companies including the currency code used for transactions. I also have a table (table 2) that has a list of approved companies that can use the various currency codes.

For the end result I want to have all the info from table 1 displayed and another field titled Approved that says Yes or No the company is approved.

I originally thought the easiest thing to do is list ALL the companies in table 2 with a yes/no approval field and just use that for the final query, but companies are added and dropped all the time, and I don't want to keep updating the table.

Is there a way to list just the approved firms in table 2 and do some kind of iif statement that basically says if the firm is listed in table 2, yes, otherwise no?

[jzwp11]

By normalization rules if table 1 and 2 contain essentially the same type of data, they should be combined into 1 table.

Now if a company is approved for multiple currency codes and those currency codes can apply to multiple companies then that describes a many-to-many relationship which requires 2 additional tables as follows

tblCurrencyCodes
-pkCurrencyCodeID primary key, autonumber
-txtCurrencyCode

tblCustomerCurrencyCodes
-pkCustCurrCodeID primary key, autonumber
-fkCustomerID foreign key to tblCustomers (table 1 or 2 in your case)
-fkCurrencyCodeID foreign key to tblCurrencyCodes

I personnally would only include a record in tblCustomerCurrencyCodes for the approved codes for each customer. If a record does not exist in tblCustomerCurrencyCodes then you know the customer is not approved to use that currency code.