Calculating

[Gus]

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!

[Qbert]

"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

[Gus]

"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

[Qbert]

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

[Gus]

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