Forum Discussion
Access Query
Folks...
I don't know whether I can submit question here but would appreciate if anyone would help.
I have a data as below:
| Id | Account Type | Buy_Quantity | Sell_Quantity |
| 1001 | A | 5 | 0 |
| 1001 | B | 0 | 2 |
| 2001 | A | 4 | 0 |
| 2001 | B | 0 | 6 |
| 3001 | A | 0 | 7 |
| 3001 | B | 8 | 0 |
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.
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.
- 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.
- 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
Thanks Arnel_gp. I will try this.
