Please help with MS Access

[bcmind]

I have a Table:

Class Rep Sales Cost
AC 10 $5 $2
AC 39 $5 $2
AC 10 $6 $3
AC 39 $10 $3
QS 10 $2 $1
QS 39 $2 $1
QS 39 $2 $1
QS 10 $3 $2


I want this result:

Class Rep Total Sales Total Cost Profit Margin
AC 10 $11 $5 120%
AC 39 $15 $5 200%
QS 10 $5 $3 66.67%
QS 39 $4 $2 100%

Please teach me how to do this... TIA!!!

 

[LeiZaK]

In "SQL View" for a new query, enter the following:

SELECT [tablename].Class, [tablename].Rep, Sum([tablename]![Sales) AS [Total Sales], Sum([tablename]![Cost]) AS [Total Cost], Round((([Total Sales]-(IIf([Total Cost] Is Null,0,[Total Costl])))/[Total Sales]*100),2) AS [Profit Margin]
FROM [tablename]
GROUP BY [tablename].Class, [tablename].Rep;

I think that will do it...

Also make sure that in the "design view" your Sum and Round fields are set to "Expression" and the others are set to "Group By"

 

[bcmind]

LdiZak,

Thank you for your reply... and it works great... but now on the the next step...

I want this result:

Rep Total Sales Total Cost
10 $16 $8
39 $19 $7

Total Sales is coming from AC + QS (Total Sales)
Total Cost is coming from AC + QS (Total Cost)

TIA!!!!

 

[beggerking]

use Format( your calculation, "Percent")
to show percentages.

 

[LeiZaK]

Originally posted by: bcmind
LdiZak,

Thank you for your reply... and it works great... but now on the the next step...

I want this result:

Rep Total Sales Total Cost
10 $16 $8
39 $19 $7

Total Sales is coming from AC + QS (Total Sales)
Total Cost is coming from AC + QS (Total Cost)

TIA!!!!

Sure.

SELECT [tablename].Rep, Sum([tablename]![Sales) AS [Total Sales], Sum([tablename]![Cost]) AS [Total Cost]
FROM [tablename]
GROUP BY [tablename].Rep

 

[bcmind]

Originally posted by: LeiZaK

Originally posted by: bcmind
LdiZak,

Thank you for your reply... and it works great... but now on the the next step...

I want this result:

Rep Total Sales Total Cost
10 $16 $8
39 $19 $7

Total Sales is coming from AC + QS (Total Sales)
Total Cost is coming from AC + QS (Total Cost)

TIA!!!!

Sure.

SELECT [tablename].Rep, Sum([tablename]![Sales) AS [Total Sales], Sum([tablename]![Cost]) AS [Total Cost]
FROM [tablename]
GROUP BY [tablename].Rep

You are the best!

 

[bcmind]

Originally posted by: beggerking
use Format( your calculation, "Percent")
to show percentages.

I'm sorry... i'm kinda new to this... can you explain it in more detail?

 

[LeiZaK]

Originally posted by: bcmind

Originally posted by: LeiZaK

Originally posted by: bcmind
LdiZak,

Thank you for your reply... and it works great... but now on the the next step...

I want this result:

Rep Total Sales Total Cost
10 $16 $8
39 $19 $7

Total Sales is coming from AC + QS (Total Sales)
Total Cost is coming from AC + QS (Total Cost)

TIA!!!!

Sure.

SELECT [tablename].Rep, Sum([tablename]![Sales) AS [Total Sales], Sum([tablename]![Cost]) AS [Total Cost]
FROM [tablename]
GROUP BY [tablename].Rep

You are the best!

 

[LeiZaK]

Originally posted by: bcmind

Originally posted by: beggerking
use Format( your calculation, "Percent")
to show percentages.

I'm sorry... i'm kinda new to this... can you explain it in more detail?

SELECT [tablename].Class, [tablename].Rep, Sum([tablename]![Sales) AS [Total Sales], Sum([tablename]![Cost]) AS [Total Cost], Format((([Total Sales]-(IIf([Total Cost] Is Null,0,[Total Costl])))/[Total Sales]*100),"0.00%") AS [Profit Margin]
FROM [tablename]
GROUP BY [tablename].Class, [tablename].Rep;

Look where I updated the Profit margin section... I think that's how it works.

 

[beggerking]

Great job!!

I think the only thing is.. you need to actually type out "Percent" .. its a function constant..

 

[bcmind]

OK.... problem with synthax....

SELECT PCSLS.Class, PCSLS.RepNum, Sum(PCSLS.[Sales Amount]) AS [SumOfSales Amount], Sum(PCSLS.[Cost Amount]) AS [SumOfCost Amount], PCSLS.Profit, Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT]^100, "0.00%") AS [PROFIT MARGIN]

please help!

 

[KLin]

Is this a homework question? Sounds like one to me.

 

[bcmind]

Originally posted by: KLin
Is this a homework question? Sounds like one to me.

no... this is actually a real work question...

 

[LeiZaK]

Originally posted by: bcmind
OK.... problem with synthax....

SELECT PCSLS.Class, PCSLS.RepNum, Sum(PCSLS.[Sales Amount]) AS [SumOfSales Amount], Sum(PCSLS.[Cost Amount]) AS [SumOfCost Amount], PCSLS.Profit, Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT]^100, "0.00%") AS [PROFIT MARGIN]

please help!

Try the word "Percent" in place of the "0.00%" as beggarking mentioned and see if that works...

 

[bcmind]

Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT]^100, "PERCENT") AS [PROFIT MARGIN]

no go

 

[LeiZaK]

Originally posted by: bcmind
Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT]^100, "PERCENT") AS [PROFIT MARGIN]

no go

Can you do without the percent sign?

 

[beggerking]

Originally posted by: bcmind
Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT]^100, "PERCENT") AS [PROFIT MARGIN]

no go

try delete ^100 out...

 

[KLin]

Originally posted by: bcmind
Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT]^100, "PERCENT") AS [PROFIT MARGIN]

no go

Use formatpercent() instead of format().

FormatPercent([SUMOFSALES AMOUNT]-iif(IsNull([SUMOFCOST AMOUNT]), 0, [SUMOFCOST AMOUNT]/[SUMOFSALES AMOUNT])) AS [PROFIT MARGIN]

EDIT: it would be better not to include spaces in field names (ie, instead of [PROFIT MARGIN] you should use ProfitMargin, etc.)

 

[LeiZaK]

Originally posted by: KLin

Originally posted by: bcmind
Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT]^100, "PERCENT") AS [PROFIT MARGIN]

no go

Use formatpercent() instead of format().

FormatPercent([SUMOFSALES AMOUNT]-iif(IsNull([SUMOFCOST AMOUNT]), 0, [SUMOFCOST AMOUNT]/[SUMOFSALES AMOUNT])) AS [PROFIT MARGIN]

EDIT: it would be better not to include spaces in field names (ie, instead of [PROFIT MARGIN] you should use ProfitMargin, etc.)

I know that... and you know that, but don't go pushing your nomenclature around here.

 

[KLin]

So did my suggestion work?

 

[6000SUX]

I'm surprised no one remarked on the lack of a primary key.

 

[bcmind]

Originally posted by: beggerking

Originally posted by: bcmind
Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT]^100, "PERCENT") AS [PROFIT MARGIN]

no go

try delete ^100 out...

Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT], "PERCENT") AS [PROFIT MARGIN]

no go

 

[bcmind]

Originally posted by: KLin

Originally posted by: bcmind
Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT]^100, "PERCENT") AS [PROFIT MARGIN]

no go

Use formatpercent() instead of format().

FormatPercent([SUMOFSALES AMOUNT]-iif(IsNull([SUMOFCOST AMOUNT]), 0, [SUMOFCOST AMOUNT]/[SUMOFSALES AMOUNT])) AS [PROFIT MARGIN]

EDIT: it would be better not to include spaces in field names (ie, instead of [PROFIT MARGIN] you should use ProfitMargin, etc.)

no go

 

[bcmind]

Originally posted by: 6000SUX
I'm surprised no one remarked on the lack of a primary key.

everything is on the same table... no other table is involved... do I still need a Primary Key?

 

[bcmind]

just to refresh everyone's mind... this works w/o calculating the "Percent"

I just need the Percent to work..

SELECT LauraAllClassAMT1.RepNum, Sum(LauraAllClassAMT1![SumOfSales Amount]) AS [TOTAL SALES Amount], Sum(LauraAllClassAMT1![SumOfCost Amount]) AS [TOTAL COST], Sum(LauraAllClassAMT1!Profit) AS PROFIT
FROM LauraAllClassAMT1
GROUP BY LauraAllClassAMT1.RepNum;