• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Please help with MS Access

B

bcmind

Senior member
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!!!
 
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"
 
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!!!!
 
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!!!!
Click to expand...

Sure.

SELECT [tablename].Rep, Sum([tablename]![Sales) AS [Total Sales], Sum([tablename]![Cost]) AS [Total Cost]
FROM [tablename]
GROUP BY [tablename].Rep
 
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!!!!
Click to expand...

Sure.

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


You are the best!
 
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!!!!
Click to expand...

Sure.

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


You are the best!
Click to expand...

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

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

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;
Click to expand...

Look where I updated the Profit margin section... I think that's how it works.
 
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!
 
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!
Click to expand...

Try the word "Percent" in place of the "0.00%" as beggarking mentioned and see if that works...
 
Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT]^100, "PERCENT") AS [PROFIT MARGIN]

no go 🙁

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

no go 🙁
Click to expand...

Can you do without the percent sign? 😛
 
Originally posted by: bcmind
Format((([SUMOFSALES AMOUNT]-(lf(([SUMOFCOST AMOUNT] is null, 0, [SUMOFCOST AMOUNT])))/[SUMOFSALES AMOUNT]^100, "PERCENT") AS [PROFIT MARGIN]

no go 🙁
Click to expand...

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

no go 🙁
Click to expand...

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.)
 
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 🙁
Click to expand...

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.)
Click to expand...

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

 
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 🙁
Click to expand...

try delete ^100 out...
Click to expand...

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

no go 🙁
 
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 🙁
Click to expand...

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.)
Click to expand...


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

everything is on the same table... no other table is involved... do I still need a Primary Key?
 
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;

 
You must log in or register to reply here.

TRENDING THREADS

Back
Top