Help with Query for new user

Only been working on access for a few months. so bare with me.

Basic Background: Working a Database to produce invoice for a small
jewellery company. Have customers and they are seperated into two lots,
agent and local pricing structure. So on each customer in the customers
table is account type which = either Agent or Local.

On my Orders table I have the customer name and address set up to
automatically come up once company name chosen. also the account Type comes
up with information.

In order details i have part number, description and price etc. when the
price comes up i would like to add this query in the Price box:

If Account type = agent, use Agent Price on products list, if account type =
Local use LocalPrice on products list.... make sense?

Second part of query, is that each customer has a discount percent off their
selected price lst. so will need to ad on the above query to reduce price by
the selected percent discount.

(foot note)
in the customers table under Discount I have just used data type number and
put in 12 or whatever, it is not formatted to a % . Is that necessary, or
should they all read the discount percent followed by % sign?

Thanks for your time. Sorry but this query is beyond me to work out! but
its one of the last things i need to do.

On Jul 15, 8:49*pm, Meredith9053
wrote:
Only been working on access for a few months. *so bare with me.

Basic Background: *Working a Database to produce invoice for a small
jewellery company. *Have customers and they are seperated into two lots,
agent and local pricing structure. *So on each customer in the customers
table is account type which = either Agent or Local.

On my Orders table I have the customer name and address set up to
automatically come up once company name chosen. *also the account Type comes
up with information.

In order details i have part number, description and price etc. *when the
price comes up i would like to add this query in the Price box:

If Account type = agent, use Agent Price on products list, if account type =
Local use LocalPrice on products list.... *make sense?

Price = iif([Account Type]="agent", AgentPrice, LocalPrice)

Second part of query, is that each customer has a discount percent off their
selected price lst. *so will need to ad on the above query to reduce price by
the selected percent discount.

Price= (1 - PercentDiscount) * iif([Account Type]="agent", AgentPrice,
LocalPrice)

(foot note)
in the customers table under Discount I have just used data type number and
put in 12 or whatever, it is not formatted to a % . *Is that necessary, or
should they all read the discount percent followed by % sign?

Thanks for your time. *Sorry but this query is beyond me to work out! *but
its one of the last things i need to do.

firstly, Thanks so very much for your respnse. I appreciate it tremendously.

Ok, I wish I could tell you it worked, but did not.

Obviously I have done something wrong, but do no know what, so how about I
tell you what i did.

I opened form in design view. added a text box near the part number and
description called UnitPrice. in data control source I typed:

UnitPrice = iif([Account Type]="agent", AgentPrice, LocalPrice)

When I endered the query, i have a little green dot on the text box (sorry
dont know what its called.) options we

Invalid Countrol Property: Control Source

Expression must begin with (=)
(tried = iif([Account Type]="agent", AgentPrice, LocalPrice) didnt work
either)

Edit Controls Control soruce Property

Edit Forms record Source Property

What else can I provide for you to help work this out? (i havnt dealt with
the % issue yet. shall do later.

Meredith



" wrote:

On Jul 15, 8:49 pm, Meredith9053
wrote:
Only been working on access for a few months. so bare with me.

Basic Background: Working a Database to produce invoice for a small
jewellery company. Have customers and they are seperated into two lots,
agent and local pricing structure. So on each customer in the customers
table is account type which = either Agent or Local.

On my Orders table I have the customer name and address set up to
automatically come up once company name chosen. also the account Type comes
up with information.

In order details i have part number, description and price etc. when the
price comes up i would like to add this query in the Price box:

If Account type = agent, use Agent Price on products list, if account type =
Local use LocalPrice on products list.... make sense?

Price = iif([Account Type]="agent", AgentPrice, LocalPrice)

Second part of query, is that each customer has a discount percent off their
selected price lst. so will need to ad on the above query to reduce price by
the selected percent discount.

Price= (1 - PercentDiscount) * iif([Account Type]="agent", AgentPrice,
LocalPrice)

(foot note)
in the customers table under Discount I have just used data type number and
put in 12 or whatever, it is not formatted to a % . Is that necessary, or
should they all read the discount percent followed by % sign?

Thanks for your time. Sorry but this query is beyond me to work out! but
its one of the last things i need to do.

OK, first things first: you are not creating a query here, you are
attempting to create an expression to use in a control source property.
Queries are built using the Query Builder which is available on the
Queries tab or via the Form/Report data source wizard. That is the
reason pietlinden's suggestion did not work for you: his advice was
geared toward creating a calculated column in a query.

Now that we have that out of the way, you are really close to a
solution: you know you have to use the iif() function and you seem to
basically understand what needs to be used for its arguments. To get you
the rest of the way, I suggest you use the Expression Builder, which you
can launch via the button next to the control source property textbox on
the Property page. Play with it for a while, using it to select the
field and function names required for your expression.

You said this
=iif([Account Type]="agent", AgentPrice, LocalPrice)
"didn't work, either". Could you explain the symptoms you experienced
without using generic terms like "didn't work", please? This is pretty
close to what you need ... I think the only thing needed is to surround
all the field names with brackets, like this:
=iif([Account Type]="agent", [AgentPrice], [LocalPrice])

Use the Expression Builder to verify this. i have no idea what error you
got or if it had anything to do with field names, etc.


Meredith9053 wrote:
firstly, Thanks so very much for your respnse. I appreciate it
tremendously.

Ok, I wish I could tell you it worked, but did not.

Obviously I have done something wrong, but do no know what, so how
about I tell you what i did.

I opened form in design view. added a text box near the part number
and description called UnitPrice. in data control source I typed:

UnitPrice = iif([Account Type]="agent", AgentPrice, LocalPrice)

When I endered the query, i have a little green dot on the text box
(sorry dont know what its called.) options we

Invalid Countrol Property: Control Source

Expression must begin with (=)
(tried = iif([Account Type]="agent", AgentPrice, LocalPrice) didnt
work either)

Edit Controls Control soruce Property

Edit Forms record Source Property

What else can I provide for you to help work this out? (i havnt
dealt with the % issue yet. shall do later.

Meredith



" wrote:

On Jul 15, 8:49 pm, Meredith9053
wrote:
Only been working on access for a few months. so bare with me.

Basic Background: Working a Database to produce invoice for a small
jewellery company. Have customers and they are seperated into two
lots, agent and local pricing structure. So on each customer in
the customers table is account type which = either Agent or Local.

On my Orders table I have the customer name and address set up to
automatically come up once company name chosen. also the account
Type comes up with information.

In order details i have part number, description and price etc.
when the price comes up i would like to add this query in the Price
box:

If Account type = agent, use Agent Price on products list, if
account type = Local use LocalPrice on products list.... make
sense?

Price = iif([Account Type]="agent", AgentPrice, LocalPrice)

Second part of query, is that each customer has a discount percent
off their selected price lst. so will need to ad on the above
query to reduce price by the selected percent discount.

Price= (1 - PercentDiscount) * iif([Account Type]="agent",
AgentPrice, LocalPrice)

(foot note)
in the customers table under Discount I have just used data type
number and put in 12 or whatever, it is not formatted to a % . Is
that necessary, or should they all read the discount percent
followed by % sign?

Thanks for your time. Sorry but this query is beyond me to work
out! but its one of the last things i need to do.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Thanks for your reply Bob, I see what you are saying for the most part, but
as i said new to access - love it - but clearly have buckets to learn.

How I think I can best help you help me is to give you a clear picture of
what i have done so far.

I have the usual tables for customer and products also order details orders
and shippers etc.

i have a form that you fill out to invoice products called the orders, - the
top part of my order has customer name address, account type, date, purchase
order details etc, and i also have a orders subform for the product details
pricing and such.

The record source for this form is:

SELECT OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate,
Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion,
Orders.ShipPostalCode, Customers.CompanyName, Customers.Address,
Customers.City, Customers.Region, Customers.PostalCode,
Customers.AccountNumber, Customers.AccountType
FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID;

In the Orders Subform I have a combo box (combo 5) on the ProductName witha
row source of:

SELECT Products.ProductID, Products.ProductName, Products.ProductDescription
FROM Products ORDER BY Products.[ProductName];

ProductDescription control source is:

=[Combo5].[Column](2)

I have a Test box with UnitPrice the control source on that is:

=IIf([Forms]![Orders]![AccountType]="Agent",[Products]![AgentPrice],[Products]![LocalPrice])

I would also like to run the discount expression here to: (refresher, each
client, as well as being assigned Agent or Local price list, is also given a
percentage off that price - this percentage is in the customers table -
called Discount)

When I say (iff expression doesnt work) i mean that when i go back toform
view all i see in the UnitPrice is #Name? or something like that, not $12.50
or whatever, as I am wanting.


Hope this helps you to help me, as I have worked really hard to get to this
stage, and if I cant go further it was all for nothing. Not your probelm I
know, but would just dearly love to commplete what i set out to do!

Thanks so much...

Meredith