Avatar of Frank Freese
Frank FreeseFlag for United States of America asked on

adding values in stacked bar chart

Folks,
I have a stacked bar chart where I would like to add the values and display the total above the bar chart. I need to add AssemblyPerUnit + TotalHousing.
Here's my code that I use to create the chart:

SELECT tblDailyProduction.ProductionDate, qryRearAxleAssemblyPerUnitB26.AssemblyPerUnit, qryRearAxleAssemblyTotalHousingB26.TotalHousing
FROM (tblDailyProduction INNER JOIN qryRearAxleAssemblyPerUnitB26 ON tblDailyProduction.ProductionDate = qryRearAxleAssemblyPerUnitB26.ProductionDate) INNER JOIN qryRearAxleAssemblyTotalHousingB26 ON tblDailyProduction.ProductionDate = qryRearAxleAssemblyTotalHousingB26.ProductionDate
WHERE (((tblDailyProduction.ProductionDate) BETWEEN [Forms]![frmDailyReport]![txtStartDate] AND [Forms]![frmDailyReport]![txtEndDate]))
GROUP BY tblDailyProduction.ProductionDate, qryRearAxleAssemblyPerUnitB26.AssemblyPerUnit, qryRearAxleAssemblyTotalHousingB26.TotalHousing
ORDER BY tblDailyProduction.ProductionDate;

I've tried an unbound control but could not get it to work.
Microsoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Jeffrey Coachman

fh_freese,

Basically You need to also plot the total (AssemblyPerUnit + TotalHousing) as a dummy series.

Here is a sample.

Let me know if it is what you wanted

JeffCoachman
Access-EEQ23433478AddTotalSumToS.mdb
ASKER
Frank Freese

this looks to be exactly what i wanted - i'll study it and let you know, many thanks
ASKER
Frank Freese

boag200:
Here's my changes but I did not get any data. I added the UNION statement and tried to add values. Please comment.

SELECT tblDailyProduction.ProductionDate, qryDiffCarrierCasePerUnitB26.[CarrierCasePerUnitB26] AS [Carrier Case], qryDiffCasePerUnitB26.[DiffCasePerUnitB26] AS [Diff Case], qryPerUnitBuildTimeStdDiffCarrierAssemblyB26.DiffCarAsemblyBuildTime AS [Diff Assembly]
FROM (qryPerUnitBuildTimeStdDiffCarrierAssemblyB26 INNER JOIN (qryDiffCasePerUnitB26 INNER JOIN qryDiffCarrierCasePerUnitB26 ON qryDiffCasePerUnitB26.ProductionDate = qryDiffCarrierCasePerUnitB26.ProductionDate) ON qryPerUnitBuildTimeStdDiffCarrierAssemblyB26.ProductionDate = qryDiffCasePerUnitB26.ProductionDate) INNER JOIN tblDailyProduction ON qryDiffCarrierCasePerUnitB26.ProductionDate = tblDailyProduction.ProductionDate
WHERE (((tblDailyProduction.ProductionDate) BETWEEN [Forms]![frmDailyReport]![txtStartDate] AND [Forms]![frmDailyReport]![txtEndDate]))
GROUP BY tblDailyProduction.ProductionDate, qryDiffCarrierCasePerUnitB26.[CarrierCasePerUnitB26], qryDiffCasePerUnitB26.[DiffCasePerUnitB26], qryPerUnitBuildTimeStdDiffCarrierAssemblyB26.DiffCarAsemblyBuildTime;
UNION Select Total, Sum(qryDiffCarrierCasePerUnitB26.[CarrierCasePerUnitB26] + qryDiffCasePerUnitB26.[DiffCasePerUnitB26] + qryPerUnitBuildTimeStdDiffCarrierAssemblyB26.DiffCarAsemblyBuildTime);
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jeffrey Coachman

fh_freese,

Can you post the output of this query?

Turn it into a MakeTable Query.
Run the Make Table query.
It will create a tbals that I can work with.
Put the table in a sample DB and post it here.

JeffCoachman
ASKER
Frank Freese

Jeff:
I will honor your request but it will need to be tomorrow -
Many thanks for hanging with me!
Frank
ASKER
Frank Freese

Jeff,
Here's the compressed file. There is one form frmDailyReport to excute. It will launch the report in preview. I noticed another problem in that my scale reads:
0
1
1
2
2
3
3
4
Which I do not know why. The first need was to add totals in the stacked bar chart and the second is the scale. Thanks for looking into this. All queries have been include. The date range is 5/1/08 - 5/23/08 in the frmDailyReport
stacked-bar-total.zip
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

Thanks,

I will look at this tonight

JeffCoachman
ASKER
Frank Freese

Do not look at the scale problem....I solved it by simply changing the number format to include a decimal. That;s solved. Many thanks...good luck
Jeffrey Coachman

Your file seems to have locked my computer,

I will reboot an try again tomorrow
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Frank Freese

Let me resend the file. I may have forget to set some of the relationships in my queries. This happened to me but I thought you got a good file. I apologize.
stacked-bar-total.zip
Jeffrey Coachman

fh_freese,

No, it was not your file, it was my computer.
I've been having trouble with lockups since upgrading to IE7

Jeff
ASKER
Frank Freese

Jeff,
I have found a solution to placing totals above a stacked bar chart. Interested?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

sure
ASKER
Frank Freese

I kept looking at your solution as it related to what I was wanting to do. It dawned on me that Access provides the data but when a graph is required ownership of the formatting is passed to Excel. The solution was found at:

http://www.andypope.info/charts/StackColTotal.htm

Although this is excel when as I followed these step I got exactly what I wanted.

ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question