Access Query

Copper Contributor


I don't know whether I can submit question here but would appreciate if anyone would help.

I have a data as below:

IdAccount TypeBuy_QuantitySell_Quantity


The greater quantity of either Buy or Sell wins Account Type. For example, in ID 1001, since Buy is greater than Sell so Account Type for 1001 is A whereas in ID 2001, Sell is more than Buy so Sells's Account Type ("B") applies to Account 2001. Below is the expected output.


5 Replies



First, we do not encourage storing calculated values in tables. That means you should not store "A" or "B" if they depend solely on the calculation from the "Buy" and "Sell" fields. 


BTW, what happens if the two are equal, i.e Buy and Sell are the same? The sample data seems to show that you are recording 0 and a non-zero value, which has other implications about the table design, actually.


You CAN, however, easily DISPLAY the calculation in a query or on a form or in a report. That's the proper way to do it.


Here is the syntax for such a query:


SELECT [ID], [Buy_Quantity], [Sell_Quantity], Iif([Buy_Quantity]>[Sell_Quantity],"A","B") AS [Account_Type]

FROM tblYourTableNameGoesHere


All of that said, if I interpret what is actually going on here, your table is not designed as it should be. (It looks more like a spreadsheet than a relational database table).


I would think THIS is a more appropriate design:


ID     TransactionType TransactionQuantity

1001      Buy                       5

1001      Sell                        2

2001      Buy                        4

2001      Sell                         6



and so on.

As a matter of fact, it looks very much like "A" and "Buy" mean the same thing (duplicate names) as do "B" and "Sell". Account Type, being duplicate, is redundant. And we don't encourage storing redundant values in a table.




you can also use 2 queries (query2 as the final query).

Thanks George. Unfortunately, I’m relying on source data so it is hard for me to change the structure but thanks for your valuable input.

Thanks Arnel_gp. I will try this.

In that case, use the original SQL I proposed, modifying table and field names.
SELECT [ID], [Buy_Quantity], [Sell_Quantity], Iif([Buy_Quantity]>[Sell_Quantity],"A","B") AS [Account_Type]
FROM tblYourTableNameGoesHere