Query (1 Viewer)

Shauqeen

Registered User.
Local time
Today, 11:50
Joined
Oct 13, 2013
Messages
10
Can someone help me on my issue? I have four fields in a query Two fields are calculated and two fields are name and amount I want one field to calculate only selected names on the name field. any Ideas
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:50
Joined
Oct 29, 2018
Messages
21,358
Hi. Can you show us the SQL statement for your query? Maybe you can use an IIf() statement.
 

Shauqeen

Registered User.
Local time
Today, 11:50
Joined
Oct 13, 2013
Messages
10
in my query
field 1 is from a table and field 2 is Field 2:[field1]*.623
now I want to select some record from field 1 to be calculated in field 2 and some not. how do I achieve this
 

plog

Banishment Pending
Local time
Today, 13:50
Joined
May 11, 2011
Messages
11,611
Please demonstrate your issue with data. We will need 2 sets:

A. Starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us the data you expect the query to return when you feed in the data from A.

Again, 2 sets of data--not 1 set and an explanation. 2 sets of data that relate to each other.
 

Shauqeen

Registered User.
Local time
Today, 11:50
Joined
Oct 13, 2013
Messages
10
The table name is sales
filed names are Name, amount
now how can I create a query in which I can select some customer with a discount and some with no discount and calculate their total accordingly
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:50
Joined
Oct 29, 2018
Messages
21,358
The table name is sales
filed names are Name, amount
now how can I create a query in which I can select some customer with a discount and some with no discount and calculate their total accordingly

Hi. Sounds like you need one more field to designate who’s getting discounts.
 

Shauqeen

Registered User.
Local time
Today, 11:50
Joined
Oct 13, 2013
Messages
10
like yes/no field and if yes then how do I tell calculated field to select those customer
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:50
Joined
Oct 29, 2018
Messages
21,358
like yes/no field and if yes then how do I tell calculated field to select those customer

Hi. Yes, something like that. Once you have something you can use to determine which records belong to which category, then you can use it as a criteria in your query.
 

Shauqeen

Registered User.
Local time
Today, 11:50
Joined
Oct 13, 2013
Messages
10
I created a yes and no field in my sales table, and create a calculated field in a query, now when I run a query it still calculates non discount customers
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:50
Joined
Oct 29, 2018
Messages
21,358
I created a yes and no field in my sales table, and create a calculated field in a query, now when I run a query it still calculates non discount customers

Can you show us the SQL statement of your query, please? Thanks.
 
Last edited:

Shauqeen

Registered User.
Local time
Today, 11:50
Joined
Oct 13, 2013
Messages
10
whereas my yes or no field name is [discount] and my gross amount field is [Amount] and my calculation in another field (I don't know how to write an expression) What I am trying to do is to calculate only selected customer from my yes or no field. what I did was
iif([discount] than[amount]*.0625.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:50
Joined
Oct 29, 2018
Messages
21,358
whereas my yes or no field name is [discount] and my gross amount field is [Amount] and my calculation in another field (I don't know how to write an expression) What I am trying to do is to calculate only selected customer from my yes or no field. what I did was
iif([discount] than[amount]*.0625.
Ah, try instead:
Code:
IIf([discount],[amount]*.0625,0)
 

Users who are viewing this thread

Top Bottom