Forum Discussion

APandya's avatar
APandya
Copper Contributor
Aug 09, 2022

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:

IdAccount TypeBuy_QuantitySell_Quantity
1001A50
1001B02
2001A40
2001B06
3001A07
3001B80

 

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.

 

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor

    APandya 

     

    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.

       

     

    • APandya's avatar
      APandya
      Copper Contributor
      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.
      • George_Hepworth's avatar
        George_Hepworth
        Silver Contributor
        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

Resources