Results 1 to 17 of 17

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

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    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
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,044

    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
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    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
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    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
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    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
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    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
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    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
    Amsterdam...
    Posts
    5,343

    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
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    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
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    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
    Amsterdam...
    Posts
    5,343

    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
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    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
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    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

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

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

    Thanks. Actually, I would like to create a small database for footwear shop to prepare sales invoice, record purchase details and maintain an inventory of stock. So, how can I design my database?

  15. #15
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

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

    unless someone here has already done a system analysis on a footwear shop, and is willing to put it here
    you will have to do the system analysis yourself, and put it here
    so tell in minute detail the what, the why, the how, the when of a footwear shop
    how many footwear shop owners have you already interviewed to ask just what is needed to operate the shop

    have a look at the Northwind sample database maybe will give you some ideas
    do not put off till tomorrow what you can put off forever

  16. #16
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

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

    Have you practiced with normalization?


    First analysis is to capture all the fields you need. If there are any pre-defined reports that are required keep them close so you can confirm all the fields you write down are in the reports somewhere. Plus other fields not in the reports. Any source files? Add those fields.

    Apply normalization to the fields to group them together. Apply a second time; to further reduce holding duplicate data. These should give you a base for tables.

    Add primary keys.
    Add foreign keys where applicable or in separate tables to point one to the other (depending on the business rules).

    Try manually putting in one or two records and see if the structure works, If so - start on your forms.

    Rest is practicing how to hold data, best option to use for which situation, or favoured structure you prefer. You can only get this from experience... Best to try as many different ways as possible to see which works better for you and still gives good/accurate results..

    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...

  17. #17
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    985

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

    as with any sales database you will need the following tables.... this is the basics only


    -Stock, items, item count, item description
    -Sales, item sold, amount sold, price sold per item, customer sold too, date sold
    -purchases, item bought, amount bought, supplier bought from, price paid


    in reality you will have several other tables..... these tables will link suppliers, customers and then you will have preferably tables linking exactly which products you sold and how much you paid for them, etc etc ....

    it something you need to right down on paper and plan out, just take your needs, come up with a plan to record those needs and build what you need to do that.
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


Posting Permissions

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



Click Here to Expand Forum to Full Width