Calculate percentage

  • Thread starter Thread starter hoachen
  • Start date Start date
H

hoachen

Dear Experts, I need help on this calculation!
The SQL below were joined by two crosstab queries. I would like to add the
columns that will calculate the % on how many item issued per month and again
how many item received per that month as well as add a total per year
calculation for the entire year % received against total item issued per
year.

SELECT [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09] AS [Item Rcd on Sep 09]
FROM [Item Count by Month] LEFT JOIN [Item Received by Month] ON [Item Count
by Month].[Supplier#] = [Item Received by Month].[CustInfoProv#]
GROUP BY [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09];

Supplier# S.Name Aug Jul Sep Oct T.Open TClosed T.All RcdSep T.Rcd
525 ABC 2 48 2 30 81 1 82 1 1
796 JJK Co 8 0 5 13 0 13 5 5

Jul % Aug % Sept % Total % per year
0 0 50 1.22
0 0 100 38.46
 
You got to change your data from spreadsheet, having fields with dates as
names to relational database.
Table [Item by Date] ---
[Supplier#]
[SupplierName]
[Item Received]
[Item Open]
[Item Closed]
[Item Date]
 
I don't understand!! I have to make a table from the crosstab queries and
then ...

KARL DEWEY said:
You got to change your data from spreadsheet, having fields with dates as
names to relational database.
Table [Item by Date] ---
[Supplier#]
[SupplierName]
[Item Received]
[Item Open]
[Item Closed]
[Item Date]

--
Build a little, test a little.


hoachen said:
Dear Experts, I need help on this calculation!
The SQL below were joined by two crosstab queries. I would like to add the
columns that will calculate the % on how many item issued per month and again
how many item received per that month as well as add a total per year
calculation for the entire year % received against total item issued per
year.

SELECT [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09] AS [Item Rcd on Sep 09]
FROM [Item Count by Month] LEFT JOIN [Item Received by Month] ON [Item Count
by Month].[Supplier#] = [Item Received by Month].[CustInfoProv#]
GROUP BY [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09];

Supplier# S.Name Aug Jul Sep Oct T.Open TClosed T.All RcdSep T.Rcd
525 ABC 2 48 2 30 81 1 82 1 1
796 JJK Co 8 0 5 13 0 13 5 5

Jul % Aug % Sept % Total % per year
0 0 50 1.22
0 0 100 38.46
Click to expand...
Click to expand...
 
Do not use crosstab but go back to your raw data.
What is the table structure of your orignal data?

--
Build a little, test a little.


hoachen said:
I don't understand!! I have to make a table from the crosstab queries and
then ...

KARL DEWEY said:
You got to change your data from spreadsheet, having fields with dates as
names to relational database.
Table [Item by Date] ---
[Supplier#]
[SupplierName]
[Item Received]
[Item Open]
[Item Closed]
[Item Date]

--
Build a little, test a little.


hoachen said:
Dear Experts, I need help on this calculation!
The SQL below were joined by two crosstab queries. I would like to add the
columns that will calculate the % on how many item issued per month and again
how many item received per that month as well as add a total per year
calculation for the entire year % received against total item issued per
year.

SELECT [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09] AS [Item Rcd on Sep 09]
FROM [Item Count by Month] LEFT JOIN [Item Received by Month] ON [Item Count
by Month].[Supplier#] = [Item Received by Month].[CustInfoProv#]
GROUP BY [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09];

Supplier# S.Name Aug Jul Sep Oct T.Open TClosed T.All RcdSep T.Rcd
525 ABC 2 48 2 30 81 1 82 1 1
796 JJK Co 8 0 5 13 0 13 5 5

Jul % Aug % Sept % Total % per year
0 0 50 1.22
0 0 100 38.46
Click to expand...
Click to expand...
Click to expand...
 
There are two tables. One is stores information for all item rents (table1).
Another table is for the return item (Table2). For table2, the data were
compiled meaning the ItemRent# and Cust# matches the Cust# and itemRent#
return item. The structure of these two tables are pretty much the same,
except the date, the itemRent#. At which i would like to 1. count by month on
how many per month (total) the itemRent and 2. count by month on how many the
itemRetunrn per month (total) 3. the percentile of the return rate. 4. The
total percentile for the entire return rate on year to date.

Table1
Item#
Supplier#
RentItem
DateRent
QtyRent
WhenRentExp
CustID

Table2
Item#
ItemRent
Supplier#
QtyRentReturn
WhenReturn
CustID

KARL DEWEY said:
Do not use crosstab but go back to your raw data.
What is the table structure of your orignal data?

--
Build a little, test a little.


hoachen said:
I don't understand!! I have to make a table from the crosstab queries and
then ...

KARL DEWEY said:
You got to change your data from spreadsheet, having fields with dates as
names to relational database.
Table [Item by Date] ---
[Supplier#]
[SupplierName]
[Item Received]
[Item Open]
[Item Closed]
[Item Date]

--
Build a little, test a little.


:

Dear Experts, I need help on this calculation!
The SQL below were joined by two crosstab queries. I would like to add the
columns that will calculate the % on how many item issued per month and again
how many item received per that month as well as add a total per year
calculation for the entire year % received against total item issued per
year.

SELECT [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09] AS [Item Rcd on Sep 09]
FROM [Item Count by Month] LEFT JOIN [Item Received by Month] ON [Item Count
by Month].[Supplier#] = [Item Received by Month].[CustInfoProv#]
GROUP BY [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09];

Supplier# S.Name Aug Jul Sep Oct T.Open TClosed T.All RcdSep T.Rcd
525 ABC 2 48 2 30 81 1 82 1 1
796 JJK Co 8 0 5 13 0 13 5 5

Jul % Aug % Sept % Total % per year
0 0 50 1.22
0 0 100 38.46
Click to expand...
Click to expand...
Click to expand...
 
Try this --
SELECT Format([DateRent], "mmmm yyyy") AS [Rental], ItemRent,
Sum([Table1].[QtyRent]) AS Month_Rent, Sum([Table2].[QtyRentReturn]) AS
Month_Return, (Sum([Table2].[QtyRentReturn])/ Sum([Table1].[QtyRent])) AS
Month_Percent_Return, (SELECT (Sum([YY].[QtyRentReturn])/
Sum([XX].[QtyRent])) FROM Table1 AS [XX] LEFT JOIN Table2 AS [YY] ON
Format([XX].[DateRent], "yyyy") = Format([YY].[ WhenReturn], "yyyy") WHERE
Format([XX].[DateRent], "yyyy") = Format(Date())) AS Year_Percent_Return
FROM Table1 LEFT JOIN Table2 ON Table1.ItemRent = Table2.ItemRent AND
Format([Table1].[DateRent], "yyyymm") = Format([Table2].[ WhenReturn],
yyyymm")
GROUP BY Format([DateRent], "mmmm yyyy"), ItemRent
ORDER BY Format([DateRent], "yyyymm"), ItemRent;

--
Build a little, test a little.


hoachen said:
There are two tables. One is stores information for all item rents (table1).
Another table is for the return item (Table2). For table2, the data were
compiled meaning the ItemRent# and Cust# matches the Cust# and itemRent#
return item. The structure of these two tables are pretty much the same,
except the date, the itemRent#. At which i would like to 1. count by month on
how many per month (total) the itemRent and 2. count by month on how many the
itemRetunrn per month (total) 3. the percentile of the return rate. 4. The
total percentile for the entire return rate on year to date.

Table1
Item#
Supplier#
RentItem
DateRent
QtyRent
WhenRentExp
CustID

Table2
Item#
ItemRent
Supplier#
QtyRentReturn
WhenReturn
CustID

KARL DEWEY said:
Do not use crosstab but go back to your raw data.
What is the table structure of your orignal data?

--
Build a little, test a little.


hoachen said:
I don't understand!! I have to make a table from the crosstab queries and
then ...

:

You got to change your data from spreadsheet, having fields with dates as
names to relational database.
Table [Item by Date] ---
[Supplier#]
[SupplierName]
[Item Received]
[Item Open]
[Item Closed]
[Item Date]

--
Build a little, test a little.


:

Dear Experts, I need help on this calculation!
The SQL below were joined by two crosstab queries. I would like to add the
columns that will calculate the % on how many item issued per month and again
how many item received per that month as well as add a total per year
calculation for the entire year % received against total item issued per
year.

SELECT [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09] AS [Item Rcd on Sep 09]
FROM [Item Count by Month] LEFT JOIN [Item Received by Month] ON [Item Count
by Month].[Supplier#] = [Item Received by Month].[CustInfoProv#]
GROUP BY [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09];

Supplier# S.Name Aug Jul Sep Oct T.Open TClosed T.All RcdSep T.Rcd
525 ABC 2 48 2 30 81 1 82 1 1
796 JJK Co 8 0 5 13 0 13 5 5

Jul % Aug % Sept % Total % per year
0 0 50 1.22
0 0 100 38.46
Click to expand...
Click to expand...
Click to expand...
 
oh..what a lenghty query... Thank you for your time to do this. I tried it
and it gave me "You tried to execute a query that does not include the
specified expression 'Format([DateRent, "yyyymm") as part of an aggregate
function. I can't spot where the problem is.
There are no group by supplier#. I want to group by by supplier# on each
month and with the percent rate.
The item rent should match two fields, the cust# and item#. I guess i just
add another line after this Table1.ItemRent = Table2.ItemRent AND
Table1.cust# = Table2.csut#. Correct?

The format on the DateRent and WhenReturn with Date/Time dataType.


KARL DEWEY said:
Try this --
SELECT Format([DateRent], "mmmm yyyy") AS [Rental], ItemRent,
Sum([Table1].[QtyRent]) AS Month_Rent, Sum([Table2].[QtyRentReturn]) AS
Month_Return, (Sum([Table2].[QtyRentReturn])/ Sum([Table1].[QtyRent])) AS
Month_Percent_Return, (SELECT (Sum([YY].[QtyRentReturn])/
Sum([XX].[QtyRent])) FROM Table1 AS [XX] LEFT JOIN Table2 AS [YY] ON
Format([XX].[DateRent], "yyyy") = Format([YY].[ WhenReturn], "yyyy") WHERE
Format([XX].[DateRent], "yyyy") = Format(Date())) AS Year_Percent_Return
FROM Table1 LEFT JOIN Table2 ON Table1.ItemRent = Table2.ItemRent AND
Format([Table1].[DateRent], "yyyymm") = Format([Table2].[ WhenReturn],
yyyymm")
GROUP BY Format([DateRent], "mmmm yyyy"), ItemRent
ORDER BY Format([DateRent], "yyyymm"), ItemRent;

--
Build a little, test a little.


hoachen said:
There are two tables. One is stores information for all item rents (table1).
Another table is for the return item (Table2). For table2, the data were
compiled meaning the ItemRent# and Cust# matches the Cust# and itemRent#
return item. The structure of these two tables are pretty much the same,
except the date, the itemRent#. At which i would like to 1. count by month on
how many per month (total) the itemRent and 2. count by month on how many the
itemRetunrn per month (total) 3. the percentile of the return rate. 4. The
total percentile for the entire return rate on year to date.

Table1
Item#
Supplier#
RentItem
DateRent
QtyRent
WhenRentExp
CustID

Table2
Item#
ItemRent
Supplier#
QtyRentReturn
WhenReturn
CustID

KARL DEWEY said:
Do not use crosstab but go back to your raw data.
What is the table structure of your orignal data?

--
Build a little, test a little.


:

I don't understand!! I have to make a table from the crosstab queries and
then ...

:

You got to change your data from spreadsheet, having fields with dates as
names to relational database.
Table [Item by Date] ---
[Supplier#]
[SupplierName]
[Item Received]
[Item Open]
[Item Closed]
[Item Date]

--
Build a little, test a little.


:

Dear Experts, I need help on this calculation!
The SQL below were joined by two crosstab queries. I would like to add the
columns that will calculate the % on how many item issued per month and again
how many item received per that month as well as add a total per year
calculation for the entire year % received against total item issued per
year.

SELECT [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09] AS [Item Rcd on Sep 09]
FROM [Item Count by Month] LEFT JOIN [Item Received by Month] ON [Item Count
by Month].[Supplier#] = [Item Received by Month].[CustInfoProv#]
GROUP BY [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09];

Supplier# S.Name Aug Jul Sep Oct T.Open TClosed T.All RcdSep T.Rcd
525 ABC 2 48 2 30 81 1 82 1 1
796 JJK Co 8 0 5 13 0 13 5 5

Jul % Aug % Sept % Total % per year
0 0 50 1.22
0 0 100 38.46
Click to expand...
Click to expand...
Click to expand...
 
Try this --
SELECT Format([DateRent], "mmmm yyyy") AS [Rental], Table1.ItemRent,
Table1.CustID, Sum([Table1].[QtyRent]) AS Month_Rent,
Sum([Table2].[QtyRentReturn]) AS Month_Return,
(Sum([Table2].[QtyRentReturn])/ Sum([Table1].[QtyRent])) AS
Month_Percent_Return, (SELECT (Sum([YY].[QtyRentReturn])/
Sum([XX].[QtyRent])) FROM Table1 AS [XX] LEFT JOIN Table2 AS [YY] ON
Format([XX].[DateRent], "yyyy") = Format([YY].[ WhenReturn], "yyyy")
WHERE Format([XX].[DateRent], "yyyy") = Format(Date())) AS Year_Percent_Return
FROM Table1 LEFT JOIN Table2 ON Table1.ItemRent = Table2.ItemRent AND
Table1.CustID = Table2.CustID AND Format([Table1].[DateRent], "yyyymm") =
Format([Table2].[ WhenReturn], yyyymm")
GROUP BY Format([DateRent], "yyyymm"), Table1.CustID, Format([DateRent],
"mmmm yyyy"), Table1.ItemRent
ORDER BY Format([DateRent], "yyyymm"), Table1.CustID, Table1.ItemRent;

--
Build a little, test a little.


hoachen said:
oh..what a lenghty query... Thank you for your time to do this. I tried it
and it gave me "You tried to execute a query that does not include the
specified expression 'Format([DateRent, "yyyymm") as part of an aggregate
function. I can't spot where the problem is.
There are no group by supplier#. I want to group by by supplier# on each
month and with the percent rate.
The item rent should match two fields, the cust# and item#. I guess i just
add another line after this Table1.ItemRent = Table2.ItemRent AND
Table1.cust# = Table2.csut#. Correct?

The format on the DateRent and WhenReturn with Date/Time dataType.


KARL DEWEY said:
Try this --
SELECT Format([DateRent], "mmmm yyyy") AS [Rental], ItemRent,
Sum([Table1].[QtyRent]) AS Month_Rent, Sum([Table2].[QtyRentReturn]) AS
Month_Return, (Sum([Table2].[QtyRentReturn])/ Sum([Table1].[QtyRent])) AS
Month_Percent_Return, (SELECT (Sum([YY].[QtyRentReturn])/
Sum([XX].[QtyRent])) FROM Table1 AS [XX] LEFT JOIN Table2 AS [YY] ON
Format([XX].[DateRent], "yyyy") = Format([YY].[ WhenReturn], "yyyy") WHERE
Format([XX].[DateRent], "yyyy") = Format(Date())) AS Year_Percent_Return
FROM Table1 LEFT JOIN Table2 ON Table1.ItemRent = Table2.ItemRent AND
Format([Table1].[DateRent], "yyyymm") = Format([Table2].[ WhenReturn],
yyyymm")
GROUP BY Format([DateRent], "mmmm yyyy"), ItemRent
ORDER BY Format([DateRent], "yyyymm"), ItemRent;

--
Build a little, test a little.


hoachen said:
There are two tables. One is stores information for all item rents (table1).
Another table is for the return item (Table2). For table2, the data were
compiled meaning the ItemRent# and Cust# matches the Cust# and itemRent#
return item. The structure of these two tables are pretty much the same,
except the date, the itemRent#. At which i would like to 1. count by month on
how many per month (total) the itemRent and 2. count by month on how many the
itemRetunrn per month (total) 3. the percentile of the return rate. 4. The
total percentile for the entire return rate on year to date.

Table1
Item#
Supplier#
RentItem
DateRent
QtyRent
WhenRentExp
CustID

Table2
Item#
ItemRent
Supplier#
QtyRentReturn
WhenReturn
CustID

:

Do not use crosstab but go back to your raw data.
What is the table structure of your orignal data?

--
Build a little, test a little.


:

I don't understand!! I have to make a table from the crosstab queries and
then ...

:

You got to change your data from spreadsheet, having fields with dates as
names to relational database.
Table [Item by Date] ---
[Supplier#]
[SupplierName]
[Item Received]
[Item Open]
[Item Closed]
[Item Date]

--
Build a little, test a little.


:

Dear Experts, I need help on this calculation!
The SQL below were joined by two crosstab queries. I would like to add the
columns that will calculate the % on how many item issued per month and again
how many item received per that month as well as add a total per year
calculation for the entire year % received against total item issued per
year.

SELECT [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09] AS [Item Rcd on Sep 09]
FROM [Item Count by Month] LEFT JOIN [Item Received by Month] ON [Item Count
by Month].[Supplier#] = [Item Received by Month].[CustInfoProv#]
GROUP BY [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09];

Supplier# S.Name Aug Jul Sep Oct T.Open TClosed T.All RcdSep T.Rcd
525 ABC 2 48 2 30 81 1 82 1 1
796 JJK Co 8 0 5 13 0 13 5 5

Jul % Aug % Sept % Total % per year
0 0 50 1.22
0 0 100 38.46
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much for your help. It works great. One more question, can the
month be in the column heading instead of row heading?

KARL DEWEY said:
Try this --
SELECT Format([DateRent], "mmmm yyyy") AS [Rental], Table1.ItemRent,
Table1.CustID, Sum([Table1].[QtyRent]) AS Month_Rent,
Sum([Table2].[QtyRentReturn]) AS Month_Return,
(Sum([Table2].[QtyRentReturn])/ Sum([Table1].[QtyRent])) AS
Month_Percent_Return, (SELECT (Sum([YY].[QtyRentReturn])/
Sum([XX].[QtyRent])) FROM Table1 AS [XX] LEFT JOIN Table2 AS [YY] ON
Format([XX].[DateRent], "yyyy") = Format([YY].[ WhenReturn], "yyyy")
WHERE Format([XX].[DateRent], "yyyy") = Format(Date())) AS Year_Percent_Return
FROM Table1 LEFT JOIN Table2 ON Table1.ItemRent = Table2.ItemRent AND
Table1.CustID = Table2.CustID AND Format([Table1].[DateRent], "yyyymm") =
Format([Table2].[ WhenReturn], yyyymm")
GROUP BY Format([DateRent], "yyyymm"), Table1.CustID, Format([DateRent],
"mmmm yyyy"), Table1.ItemRent
ORDER BY Format([DateRent], "yyyymm"), Table1.CustID, Table1.ItemRent;

--
Build a little, test a little.


hoachen said:
oh..what a lenghty query... Thank you for your time to do this. I tried it
and it gave me "You tried to execute a query that does not include the
specified expression 'Format([DateRent, "yyyymm") as part of an aggregate
function. I can't spot where the problem is.
There are no group by supplier#. I want to group by by supplier# on each
month and with the percent rate.
The item rent should match two fields, the cust# and item#. I guess i just
add another line after this Table1.ItemRent = Table2.ItemRent AND
Table1.cust# = Table2.csut#. Correct?

The format on the DateRent and WhenReturn with Date/Time dataType.


KARL DEWEY said:
Try this --
SELECT Format([DateRent], "mmmm yyyy") AS [Rental], ItemRent,
Sum([Table1].[QtyRent]) AS Month_Rent, Sum([Table2].[QtyRentReturn]) AS
Month_Return, (Sum([Table2].[QtyRentReturn])/ Sum([Table1].[QtyRent])) AS
Month_Percent_Return, (SELECT (Sum([YY].[QtyRentReturn])/
Sum([XX].[QtyRent])) FROM Table1 AS [XX] LEFT JOIN Table2 AS [YY] ON
Format([XX].[DateRent], "yyyy") = Format([YY].[ WhenReturn], "yyyy") WHERE
Format([XX].[DateRent], "yyyy") = Format(Date())) AS Year_Percent_Return
FROM Table1 LEFT JOIN Table2 ON Table1.ItemRent = Table2.ItemRent AND
Format([Table1].[DateRent], "yyyymm") = Format([Table2].[ WhenReturn],
yyyymm")
GROUP BY Format([DateRent], "mmmm yyyy"), ItemRent
ORDER BY Format([DateRent], "yyyymm"), ItemRent;

--
Build a little, test a little.


:

There are two tables. One is stores information for all item rents (table1).
Another table is for the return item (Table2). For table2, the data were
compiled meaning the ItemRent# and Cust# matches the Cust# and itemRent#
return item. The structure of these two tables are pretty much the same,
except the date, the itemRent#. At which i would like to 1. count by month on
how many per month (total) the itemRent and 2. count by month on how many the
itemRetunrn per month (total) 3. the percentile of the return rate. 4. The
total percentile for the entire return rate on year to date.

Table1
Item#
Supplier#
RentItem
DateRent
QtyRent
WhenRentExp
CustID

Table2
Item#
ItemRent
Supplier#
QtyRentReturn
WhenReturn
CustID

:

Do not use crosstab but go back to your raw data.
What is the table structure of your orignal data?

--
Build a little, test a little.


:

I don't understand!! I have to make a table from the crosstab queries and
then ...

:

You got to change your data from spreadsheet, having fields with dates as
names to relational database.
Table [Item by Date] ---
[Supplier#]
[SupplierName]
[Item Received]
[Item Open]
[Item Closed]
[Item Date]

--
Build a little, test a little.


:

Dear Experts, I need help on this calculation!
The SQL below were joined by two crosstab queries. I would like to add the
columns that will calculate the % on how many item issued per month and again
how many item received per that month as well as add a total per year
calculation for the entire year % received against total item issued per
year.

SELECT [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09] AS [Item Rcd on Sep 09]
FROM [Item Count by Month] LEFT JOIN [Item Received by Month] ON [Item Count
by Month].[Supplier#] = [Item Received by Month].[CustInfoProv#]
GROUP BY [Item Count by Month].[Supplier#], [Item Count by
Month].SupplierName, [Item Count by Month].[Total All Item], [Item Received
by Month].[Total Item Received], [Item Count by Month].[Total Item Open],
[Item Count by Month].[Total Item Closed], [Item Count by Month].[Aug 09],
[Item Count by Month].[Jul 09], [Item Count by Month].[Oct 09], [Item Count
by Month].[Sep 09], [Item Received by Month].[Sep 09];

Supplier# S.Name Aug Jul Sep Oct T.Open TClosed T.All RcdSep T.Rcd
525 ABC 2 48 2 30 81 1 82 1 1
796 JJK Co 8 0 5 13 0 13 5 5

Jul % Aug % Sept % Total % per year
0 0 50 1.22
0 0 100 38.46
Click to expand...
Click to expand...
Click to expand...
 
Back
Top