[求助] 請各位大俠指點----如何在查詢中進行累加計算?

Susie

有一個商店經營的水果品種如下:
Item:        Apple        Banana        Pear        Watermelon
Qty.:        200        400        300        700
Rate:        12.5%        25%        18.75%        43.75%

現需要做一個查詢,使水果按數量由高至低排序,並且占有率要進行累加,直至100%
Item:                Watermelon         Banana        Pear        Apple
Qty.:                700         400        300        200
Rate:                43.75%         25%        18.75%        12.5%
accumulation:        43.75%         68.75%        87.5%        100%

由于需要在一個圖表中同時出現直條圖和折線圖,所以這個累加結果很重要.[em01]

cattjiu

我用Excel做了那图,但不能同时有直条和折线的.而且两图在一起本来没有很大意义,看起来百分率没有直观的变化显示.

Susie

版主,以上只是我實際工作的一個簡化例子.我為公司的品質部做一個小的數據庫,要求根據
每天輸入的檢查報告, 生成每個產品月份的百拉圖.圖表對于我們似乎意義不大, 但對于品質部就不同了,他們需要根據百拉圖控製次品率.

cnwang

你是在做柏拉图吧，用Dsum即可。

之前有讨论过。。。

Susie

請問這位版主,我怎樣能找到這方面的資料.

Susie

斑竹,如果你找到關于柏拉圖的資料能給我發郵件嗎?我的郵箱是:susie.lee@chiaphua.com.hk

cattjiu

在这里. http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q208373


ACC2000: How to Create a Pareto Chart Using Microsoft Graph
The information in this article applies to:
Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).


Summary
A Pareto chart provides a simple way of analyzing categorical data. In a Pareto chart, categories are arranged in descending order from left to right as histogram columns, based upon a selected numerical criterion, from highest to lowest values. Then, an ascending plot line for cumulative percentage is overlaid on the histogram chart. In this way, you can quickly determine which (small number of) categories make up a significant percentage of the total value.

This article shows how to create a basic Pareto chart, using the Sales By Category query in the sample database Northwind.mdb.
More Information
Creating a Pareto Chart
To make a Pareto chart in Microsoft Access 2000, follow these sets of steps.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.


Create the Data Source for the Chart
Start Microsoft Access and open the sample database Northwind.mdb.
In the Database window, click Queries, and then select Sales By Category. On the Edit menu, click Copy, and then click Paste. Save the copy of the Sales By Category table as qrySalesByCategory.
Open the qrySalesByCategory query in Design view. Delete the CategoryID field and the ProductName field from the QBE grid. Set the Sort order of the ProductSales field to Descending.
On the Query menu, click Make Table to change the query to a Make Table query. In the Make Table dialog box, type tblSalesByCategory in the Table Name box.
Save the query and run it. Accept the message to paste rows to a new table.
Close the query.
In the Database window, click Queries, and then click New to create a new query. In the New Query dialog box, select Design View, and then click OK. In the Show Table dialog box, click tblSalesByCategory, click Add, and then click Close.
Drag the CategoryName field to the QBE grid.
In the next column on the QBE grid, type the following in the Field row:
ProductSalesTotal: [ProductSales]
In the next column on the QBE grid, type the following in the Field row: NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example.
CumPct: DSum("[ProductSales]","tblSalesByCategory", _
"[ProductSales]>=" & [ProductSalesTotal] _
& "")/DSum("[ProductSales]","tblSalesByCategory")
Save the query as qrySalesByCategoryPareto, and then close it.
Create a Chart Form
In the Database window, click Forms, and then click New to create a new form. In the New Form dialog box, click Chart Wizard, select qrySalesByCategoryPareto as the query where the object's data comes from, and then click OK.
Move all three available fields to the Fields for Chart box. Click Next.
Select Line Chart as the type of chart to use. Click Next.
Double-click SumofProductSalesTotal, and then click None under Summarize.
Click the CumPct field, and drag it underneath the ProductSalesTotal field. (Do not replace ProductSalesTotal, but add CumPct to the Data section.)
Double-click the SumofCumPct field, and then select None under Summarize. Click Next.
For the chart title, type Sales by Category - Pareto, and then click Finish.
Modify the Chart Design
On the View menu, click Design View.
On the View menu, click Properties to display the form property sheet, if it is not already visible.
On the Form property sheet, set the Width to 8".
Click the Detail section, and then set the Height to 6".
Click the Unbound Object Frame that contains the chart, and then set Height to 6" and Width to 8".
Right-click the chart, point to Chart Object, and then click

Susie

Thank you for your help!