Help with Data Control Source

[Meredith9053]

New User ok!

need to write a query and not sure how.

want it to say basically.

If "AccountType" = Local (from the Products Table) the use LocalPrice from
the Products table or if "AccountType" = Agent then use AgentPrice from the
Products table.

I have a products table that has my items in it, and I have a local price
and an agent price. so need it to automatically bring up the price for the
item when selected.

product table headings are:
ProductID (primary Key) Autonumber
ProductName
ProductDescription
LocalPrice
AgentPrice

All customers are either agent or local, and never change once set.

Also just to add insult to injury on our price structure. each client has a
set percentage rate off the Price.

So also when the price comes up I will need to refer the customer table
again to see the Discount percentage and apply that discount to the Unit
Price.

Orders Table Headings are:
OrderID (Primary Key) Autonumber
CustomerID
Account Number
Company Name
Address etc

Order Details Table Heading are:
Order ID(Primary Key)
ProductID(Primary Key)
ProductName
ProductDescription
Unit Price (determined by query I need to build) ie, if local price, thats
where I want it to end up.)
Quantity
Discount (Percentage Discount off Unit Price)

Customers Table heading are:
Customer ID (Primary Key)
CompanyName Address et
AccountType (either Local or Agent)
Discount (Percentage off Unit Price) (have this as a number field, with just
12 in the data not 12% or anything like that. Do I need 12% and set data
type to percent or anything?

I have probably given too much info, but better too much than not enough.

Thanks so much for your time.
Meredith

 

[scubadiver]

I am not sure whether you can put field names into the true or false (Ive
never tried) but give it a go. It assume you only either have 'local' or
'agent'

expr1: IIF([accounttype]="local",[localprice],[agentprice])