Calculating


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Calculating

  1. #1
    Gus Guest

    Calculating


    I am trying to use SQL in MS Access to gather specific data from a table and
    transfer it to another table so as to create a report that will group the
    data and calculate the grand totals. Can this all be done in the SQL statement?
    I know I can SELECT ... INTO, but can I also get it to calculate the numbers
    and dump them into the table as totals?

    I am doing contract work and they want this done in MS Access.

    Thanks for any help!

  2. #2
    Qbert Guest

    Re: Calculating


    "Gus" <gpadres@comstor.com> wrote:
    >
    >I am trying to use SQL in MS Access to gather specific data from a table

    and
    >transfer it to another table so as to create a report that will group the
    >data and calculate the grand totals. Can this all be done in the SQL statement?
    > I know I can SELECT ... INTO, but can I also get it to calculate the numbers
    >and dump them into the table as totals?
    >
    >I am doing contract work and they want this done in MS Access.
    >
    >Thanks for any help!

    Gus, have you tried using the sum function and group by statments to group
    your values together?

    Assume a table of the following:
    VehicleID VehicleType VehicleCost
    1 Car $2,000
    2 Truck $4,000
    3 Car $5,000

    You could:
    Select sum(VehicleCost) as TotalCost, VehicleType
    FROM tablename
    GROUP BY VehicleType

    Results
    VehicleCost VehicleType
    $7,000 Car
    $4,000 Truck


  3. #3
    Gus Guest

    Re: Calculating


    "Qbert" <luke_Davis_76@hotmail.com> wrote:
    >
    >"Gus" <gpadres@comstor.com> wrote:
    >>
    >>I am trying to use SQL in MS Access to gather specific data from a table

    >and
    >>transfer it to another table so as to create a report that will group the
    >>data and calculate the grand totals. Can this all be done in the SQL statement?
    >> I know I can SELECT ... INTO, but can I also get it to calculate the numbers
    >>and dump them into the table as totals?
    >>
    >>I am doing contract work and they want this done in MS Access.
    >>
    >>Thanks for any help!

    >Gus, have you tried using the sum function and group by statments to group
    >your values together?
    >
    >Assume a table of the following:
    >VehicleID VehicleType VehicleCost
    >1 Car $2,000
    >2 Truck $4,000
    >3 Car $5,000
    >
    >You could:
    >Select sum(VehicleCost) as TotalCost, VehicleType
    >FROM tablename
    >GROUP BY VehicleType
    >
    >Results
    >VehicleCost VehicleType
    >$7,000 Car
    >$4,000 Truck
    >


    QBert: That is what I am looking for! I will test to see if it works.
    I am using a table with NO KEYS! I assume, i can SUM multiple columns,
    etc. I hope I can also group y multiple columns.

    The table i have looks like this

    Date CustomerID SalesRepID PartID Qty Price Cost Margin$ Margin%
    4/6/01 TSY1 127 SVSTA 3 $150 35 115 30%

    The problem is that duplicate values can exist in every column (no key).
    Also I need to group by sales Rep in that SalesReps within 100's = West
    and 300's = East. Also, I need to only calculate qty,price,cost,m$ and m%.

    So, that the final table looks something like this:

    Category Qty Price Cost Margin$ Margin%
    Staging-East 200 $6,789 $4,500 $2,289 64%
    Staging-West 150 $4,300 $2,500 $1,800 43%

    etc, etc etc

    So, anyhow....I am about 30% there I hope your solution either works
    or gets me closer to what they want.

    Thanks again!
    Gus

  4. #4
    Qbert Guest

    Re: Calculating


    Gus,
    Select sum(QTY) as T_Qty, Sum(Price) as T_Price, Sum(Cost) as T_Cost,
    Sum(Price)-Sum(Cost) as Margin$, Sum(Cost)/sum(Price) as T_Margin%
    FROM tblName
    GROUP BY left(SalesRepID,1) 'I assume that Sales RepID is ONLY a 3 digit
    field

    I'm not sure what you mean by duplicate values you may want to use a distinct
    to eliminate duplicate rows.... but again, I'm not sure what your doing...

    >The table i have looks like this
    >
    >Date CustomerID SalesRepID PartID Qty Price Cost Margin$ Margin%
    >4/6/01 TSY1 127 SVSTA 3 $150 35 115 30%
    >
    >The problem is that duplicate values can exist in every column (no key).
    > Also I need to group by sales Rep in that SalesReps within 100's = West
    >and 300's = East. Also, I need to only calculate qty,price,cost,m$ and

    m%.
    >
    >So, that the final table looks something like this:
    >
    >Category Qty Price Cost Margin$ Margin%
    >Staging-East 200 $6,789 $4,500 $2,289 64%
    >Staging-West 150 $4,300 $2,500 $1,800 43%
    >
    >etc, etc etc
    >
    >So, anyhow....I am about 30% there I hope your solution either works
    >or gets me closer to what they want.
    >
    >Thanks again!
    >Gus



  5. #5
    Gus Guest

    Re: Calculating


    Qbert:

    I did that and it worked. Thanks for your help! As for the duplicate values,
    I am getting the data from AS/400 flat database, so unfortunately, data gets
    duplicated and I can't eliminate as it would alter their totals. I am attempting
    to create "virtual" tables that contain a PK and a description column and
    then I will try and use SQL to join the tables using the HAVING statement.

    Thanks again for your help

    -Gus


    "Qbert" <luke_davis_76@hotmail.com> wrote:
    >
    >Gus,
    >Select sum(QTY) as T_Qty, Sum(Price) as T_Price, Sum(Cost) as T_Cost,
    >Sum(Price)-Sum(Cost) as Margin$, Sum(Cost)/sum(Price) as T_Margin%
    >FROM tblName
    >GROUP BY left(SalesRepID,1) 'I assume that Sales RepID is ONLY a 3 digit
    >field
    >
    >I'm not sure what you mean by duplicate values you may want to use a distinct
    >to eliminate duplicate rows.... but again, I'm not sure what your doing...



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center