To modify code to get some calculations in 2 additional columns using Select query ..-VBForums
Results 1 to 13 of 13

Thread: To modify code to get some calculations in 2 additional columns using Select query ..

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Dec 2014
    Posts
    226

    To modify code to get some calculations in 2 additional columns using Select query ..

    How to modify the following code to get the result as in the attached image. Here two new columns to be added to the DataGridView with some calculations (which is hard part since it require IF condition in Select statement).

    Need two more fields: ______ As TaxAmount, _______PriceWithTax

    Price field is hard for me. Because, if the box value is more than zero, then the price is "One Box Price" or if the box value is zero then it is "One Piece Price". How to modify the code to get New Fields TaxAmount & PriceWithTax?


    Code:
    Dim StrSqlItems As String = "Select ModelNumber, Box, PackingPerBox, Quantity, Price, TotalAmount, TaxRate from ItemsDatabaseTable ORDER BY ModelNumber ASC"
    Attached Images Attached Images   
    Last edited by VS2013; Jan 9th, 2018 at 04:34 PM.

  2. #2
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    846

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    Hi,

    goes like this...

    I put 19% Tax in my Query
    Code:
    SELECT Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, ([Order Details].[UnitPrice]*[Quantity]) AS TotalPrice, Format([TotalPrice]/100*19,"#,##0.00") AS xTaxFromTotal, [TotalPrice]+[xTaxFromTotal] AS TotalAll
    FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID;
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3
    Fanatic Member
    Join Date
    Jun 2014
    Posts
    1,019

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    Price field is hard for me. Because, if the box value is more than zero, then the price is "One Box Price" or if the box value is zero then it is "One Piece Price". How to modify the code to get New Fields TaxAmount & PriceWithTax?
    the easy solution is having 2 different product numbers for the same product
    if sold per box : ProdB
    if sold per piece: ProdP
    do not put off till tomorrow what you can put off forever

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Dec 2014
    Posts
    226

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    The problem now is, almost more than 3,000 records were added. Is there any solution to use if condion to resolve this issue?

  5. #5
    Fanatic Member
    Join Date
    Jun 2014
    Posts
    1,019

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    The problem now is, almost more than 3,000 records were added.
    if the simple fact of adding 3,000 records causes a problem
    then the real problem is probably bad design

    Is there any solution to use if condion to resolve this issue?
    resolve what issue ?
    do not put off till tomorrow what you can put off forever

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Dec 2014
    Posts
    226

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    So, you mean there is no way to use the same database (as it is) to achieve the result.

  7. #7
    Fanatic Member
    Join Date
    Jun 2014
    Posts
    1,019

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    if you mean you want to achieve the result as in your original question in post#1
    then ChrisE already told you how in post#2

    if you want to achieve something else
    then clearly explain what you want to achieve

    oh,btw
    So, you mean there is no way to use the same database (as it is) to achieve the result.
    i, for one, have not the faintest idea of the design of your database
    so, i do not have the faintest idea if there is a way to use the same database (as it is) to achieve the result.
    also i do not have the faintest idea what you want as the result
    do not put off till tomorrow what you can put off forever

  8. #8
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,217

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    Your table in the image looks, well wrong... but maybe I'm reading it wrong.
    Code:
    SELECT
          ModelNumber,
          Box,
          PackingPerBox,
          Quantity,
          Price,
          TotalAmount,
          TaxRate
      FROM 
          ItemsDatabaseTable
     ORDER BY 
          ModelNumber ASC
    If the box is > 0 - then the price is box price
    if the box is 0 then use the unit price

    Your query doesn't say where these two prices are held. I assume its in a prices table with a price field for unit and a price field for box.

    Code:
    SELECT
          sq.ModelNumber,
          sq.Box,
          sq.PackingPerBox,
          sq.Quantity,
          sq.BoxPrice,
          sq.UnitPrice,
          sq.price,
          sq.TotalAmount,
          sq.TaxRate,
          sq.TotalAmount*sq.TaxRate AS TaxAmount,
          (sq.TotalAmount*sq.TaxRate)+sq.TotalAmount AS TotalPriceWithTax
      FROM
    (
    SELECT
          i.ModelNumber,
          i.Box,
          i.PackingPerBox,
          i.Quantity,
          i.BoxPrice,
          i.UnitPrice,
          CASE 
             WHEN Box=0 THEN p.unitprice
             ELSE p.boxprice
          END AS Price,
          CASE 
             WHEN Box=0 THEN p.unitprice * quantity
             ELSE p.boxprice * quantity
          END AS TotalAmount,
          i.TaxRate
      FROM 
          ItemsDatabaseTable AS i INNER JOIN Prices AS p ON i.itemid = p.itemid
    ) AS sq
    The above won't work, same reason as IkkeEnGij stated, we dont know the structure of the tables. But perhaps it will give you an idea how to make it work.
    This is only a possible way, and depends on the database you are using (you didnt say which). It would be best to try different ways and you decide which is the best for you so if anyone asks you can explain your decision.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  9. #9
    Fanatic Member
    Join Date
    Jun 2014
    Posts
    1,019

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    Miss moonshine has a small beer shop
    so she buys by sixpacks and sels by the bottle
    after a while several clients want to buy by sixpack to
    so she buys by box(=4 sixpacks) and sels by sixpack and by bottle
    after a while several clients want to buy by box to
    so she buys by pallet(=60 boxes) and sels by box,by sixpack and by bottle
    after a while some clients want to buy by pallet to
    etc...
    one would think that after a while miss moonshine would be a very wealthy woman
    not so...the programmer becomes very wealthy
    the program and the database need constantly very expensive updates
    the employees using the program need constant re-education
    the program becomes so complicated the employees make constantly errors
    in the end miss moonshine goes bankrupt

    but:
    miss shinemoon (miss moonshine's friend)
    used the simple solution, that every supermarket on the planet uses
    and became a very wealthy woman
    do not put off till tomorrow what you can put off forever

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Dec 2014
    Posts
    226

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    I am going to create a new MS Access Database Tables like in the following images. But I don't know where can I add some extra fields in the Tables to assign Foreign Keys.
    Attached Images Attached Images   

  11. #11
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,217

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    I would suggest you take the prices out of the products table, and add a start date... that way should the price change you wont need to rebuild the tables... perhaps will cause other problems

    serial no isnt in the products, just sales perhaps, productid (field) replaces the red box. If you add the prices into another lookup table, just put the priceid as another field...

    example :
    your sales table becomes something like :
    Code:
    salesid pk
    custid fk
    deliveryaddress..fields
    invoicenro
    invoicedate
    serialnro
    productid fk
    priceid fk
    taxid fk
    discount
    Code:
    --pricestable--
    priceid pk
    typeid fk? (table of box, pallet, unit etc..)
    iswholesale (true or false) < change to lookup table to if more options
    productid
    startdate
    price
    Code:
    --tax table--
    taxid
    startdate
    taxperc
    This is from your tables btw.. not experience, but I think several people on here do a lot with finance tables, might be better if you ask nicely for them to suggest better table structures (from their experience) and why.
    Or do as many different ways to see which cause problems and where...

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Dec 2014
    Posts
    226

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    Thanks for your kind support & time. I will edit tables as per your suggestion.

    Actually, I would like to start a New MS Access Database in a professional way (which I don't know until now) to avoid any flaws while getting reports as I was advised to do so in previous posts.

    So, it is my humble request to all to guide me in creating a database (for Account purpose of Sales, Purchase & Inventory). I am not fully aware of how many tables that really required to build it.

  13. #13
    Fanatic Member
    Join Date
    Jun 2014
    Posts
    1,019

    Re: To modify code to get some calculations in 2 additional columns using Select quer

    I am sure a lot of people here are willing to help you
    problem is no one knows exactly what you want, or what it is going to be used for
    so, if you put here a very detailed descrition of the what, the why, the how
    i am sure people will be willing to help you
    iow: put here the system analysis you have done

    if all you want is
    Actually, I would like to start a New MS Access Database in a professional way
    actually there are a miriad of books on database design
    did you go to the bib ?
    did you google for database design ?
    do not put off till tomorrow what you can put off forever

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.