DSum function

[Guest]

i have a table called "t_daily_reject". i want use this table to make a
query. i will select three field from this table, that is "kind_of_defect",
"qty" and "date". "date" i will change to month format. then i want to
calculate the percentage for each kind of defect which by month. i have using
the DSum function to calculate because i want to get the total of that. but
it was sum all. how i can calculate the total for each month from the query?
i was type the formula as %Reject[qty]/DSum([qty],"t_daily_reject"))*100
i know i did not set the criteria, but i dont know how to set it. can
anybody help me?

thanks!

 

[Guest]

I would push this to a report where you can more easily calculate percentages
of group or individual records.

 

[John Spencer]

SELECT Format([Date],"YYYY-MM") as DefMonth
, Kind_of_Defect
, Sum(qty) as NumDefects
, Sum(Qty)/
(SELECT Sum(Qty)
FROM t_Daily_Reject as Temp
WHERE Format(Temp.Date, "YYYY-MM") = Format(Main.Date,"YYYY-MM") )
as Ratio
FROM t_Daily_Reject as Main
GROUP BY Format([Date],"YYYY-MM"), Kind_of_Defect

A More efficient method M I G H T be to use Three queries.
Query one would calculate the total number of errors for each month
query one saved as RejectTotals
SELECT Format([Date],"YYYY-MM") as DefMonth
, Sum(Qty) as TotalDefects
FROM t_Daily_Reject as Temp
GROUP BY Format([Date],"YYYY-MM")

Query Two saved as RejectKind calculates monthly totals by kind
SELECT Format([Date],"YYYY-MM") as DefMonth
, Kind_of_Defect
, Sum(qty) as NumDefects
FROM t_Daily_Reject
GROUP BY Format([Date],"YYYY-MM")
, Kind_of_Defect

Query three combines the two previous queries

SELECT RejectKind.DefMonth
, RejectKind.Kind_of_Defect
, RejectKind.NumDefects
, Sum(RejectKind.NumDefects)/ RejectTotals.TotalDefects as Ratio
FROM RejectKind INNER JOIN RejectTotals
ON RejectKind.DefMonth =RejectTotals.DefMonth

If you need instructions on how to build these three queries using the query
grid, post back.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I would push this to a report where you can more easily calculate
percentages
of group or individual records.

--
Duane Hookom
Microsoft Access MVP

i have a table called "t_daily_reject". i want use this table to make a
query. i will select three field from this table, that is
"kind_of_defect",
"qty" and "date". "date" i will change to month format. then i want to
calculate the percentage for each kind of defect which by month. i have
using
the DSum function to calculate because i want to get the total of that.
but
it was sum all. how i can calculate the total for each month from the
query?
i was type the formula as
%Reject[qty]/DSum([qty],"t_daily_reject"))*100
i know i did not set the criteria, but i dont know how to set it. can
anybody help me?

thanks!

 

[Guest]

Hi John,

Thanks! I have make it and it work exact as what I want. Thanks so much!

SELECT Format([Date],"YYYY-MM") as DefMonth
, Kind_of_Defect
, Sum(qty) as NumDefects
, Sum(Qty)/
(SELECT Sum(Qty)
FROM t_Daily_Reject as Temp
WHERE Format(Temp.Date, "YYYY-MM") = Format(Main.Date,"YYYY-MM") )
as Ratio
FROM t_Daily_Reject as Main
GROUP BY Format([Date],"YYYY-MM"), Kind_of_Defect

A More efficient method M I G H T be to use Three queries.
Query one would calculate the total number of errors for each month
query one saved as RejectTotals
SELECT Format([Date],"YYYY-MM") as DefMonth
, Sum(Qty) as TotalDefects
FROM t_Daily_Reject as Temp
GROUP BY Format([Date],"YYYY-MM")

Query Two saved as RejectKind calculates monthly totals by kind
SELECT Format([Date],"YYYY-MM") as DefMonth
, Kind_of_Defect
, Sum(qty) as NumDefects
FROM t_Daily_Reject
GROUP BY Format([Date],"YYYY-MM")
, Kind_of_Defect

Query three combines the two previous queries

SELECT RejectKind.DefMonth
, RejectKind.Kind_of_Defect
, RejectKind.NumDefects
, Sum(RejectKind.NumDefects)/ RejectTotals.TotalDefects as Ratio
FROM RejectKind INNER JOIN RejectTotals
ON RejectKind.DefMonth =RejectTotals.DefMonth

If you need instructions on how to build these three queries using the query
grid, post back.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I would push this to a report where you can more easily calculate
percentages
of group or individual records.

--
Duane Hookom
Microsoft Access MVP

i have a table called "t_daily_reject". i want use this table to make a
query. i will select three field from this table, that is
"kind_of_defect",
"qty" and "date". "date" i will change to month format. then i want to
calculate the percentage for each kind of defect which by month. i have
using
the DSum function to calculate because i want to get the total of that.
but
it was sum all. how i can calculate the total for each month from the
query?
i was type the formula as
%Reject[qty]/DSum([qty],"t_daily_reject"))*100
i know i did not set the criteria, but i dont know how to set it. can
anybody help me?

thanks!