Summing top 3 results

[Guest]

I have a table for truck sales in canada by make of truck (there is about 8
makes). The records are for monthly sales. I would like to make a table
that has date descending in the left most column (in order to show most
recent results first), and a sum of the top three makes for that month in
column 2 (the top sellers change from month to month).

The data is stored in a large data table (named DTA, with the fields
DTE-date, KEY-items key, and VLE-value) and the descriptions are stored in an
items table (named ITEMS). These tables are linked on the item "key".
Therefore, a simple select query would look like this:

SELECT dta.dte, items.key FROM....WHERE items.key like 'CLASS8C' & '*';

The CLASS8C line restricts results to only those items pertaining to class 8
truck sales in canada. The keys for the various makes look like this:
CLASS8C_FRT, CLASS8C_PTRBLT, etc....

So, in brief, I want the query to display the sum of the top three makes for
each month - how would I do this???

Thanks,

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Brother, your naming conventions are very bad. You should be naming
things for what they are not what they do.

DTE - Date of what: sales date, birthdate, holiday?
Key - This describes what the column does in the DB, not what the data
is, and it is a SQL reserved word.
VLE - What type of value: sales amount, item cost, neck size...?

Then your Items.Key values violates the 1NF (First Normal From): "Each
cell should be atomic" - there should be only 1 item in each column
value. "CLASS8C_FRT" represents 2 items: the Class 8 truck and the
truck type (FRT, PTRBLT, etc.).

Your table design should look something like this:

CREATE TABLE TruckSales (
sales_date DATETIME NOT NULL,
truck_class CHAR(10) NOT NULL,
truck_type VARCHAR(10) NOT NULL,
sales_amount DECIMAL(8,2) NOT NULL,
CONSTRAINT PK_TruckSales
PRIMARY KEY (sales_date, truck_class, truck_type)
)

"I would like to make a table ...." Actually, you want a View (query in
Access-speak).

You really need to read a book on database design. I recommend
_Database Design for Mere Mortals_ by Hernandez.

Having said all that, here is a solution to your problem, using your
column names (untested):

SELECT Month(dte) As SalesMonth, [Key] As TruckType,
Sum(vle) As TotalSales
FROM DTA As D
WHERE dte BETWEEN #1/1/2006# AND #12/31/2006#
AND [key] IN
(SELECT TOP 3 [key]
FROM (SELECT [key], Sum(vle) As Sales
FROM DTA
WHERE [key] Like "CLASS8C*"
AND Month(dte) = Month(D.dte)
GROUP BY [key]
ORDER BY Sum(vle) DESC
) As B
)
GROUP BY Month(dte), [Key]

Don't change to Design view once this is in the SQL view, 'cuz Access
will try to put []s where the ()s are. This will cause an error when
the query runs. If that happens just change the []s around the 2nd
SELECT statements in the IN () predicate expression to ()s. Be sure to
change the ]. to ) - without the period.

There is a possibility of a tie in the TOP 3, which means the result
will have more than 3 "TruckTypes" w/ 2 or more that have equal total
sales amounts.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRK1ttYechKqOuFEgEQJ27QCgpBFLc6U9w56MfvE/wkSdGMtIjHoAoIFR
AyRpdEwelAHzTgq5L19j8C0c
=3wxZ
-----END PGP SIGNATURE-----

 

[Guest]

Thanks for the help pertinent to my question, i expect that it will work - i
didnt know you could select records from another query all within the same
query.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Brother, your naming conventions are very bad. You should be naming
things for what they are not what they do.

DTE - Date of what: sales date, birthdate, holiday?
Key - This describes what the column does in the DB, not what the data
is, and it is a SQL reserved word.
VLE - What type of value: sales amount, item cost, neck size...?

Then your Items.Key values violates the 1NF (First Normal From): "Each
cell should be atomic" - there should be only 1 item in each column
value. "CLASS8C_FRT" represents 2 items: the Class 8 truck and the
truck type (FRT, PTRBLT, etc.).

Your table design should look something like this:

CREATE TABLE TruckSales (
sales_date DATETIME NOT NULL,
truck_class CHAR(10) NOT NULL,
truck_type VARCHAR(10) NOT NULL,
sales_amount DECIMAL(8,2) NOT NULL,
CONSTRAINT PK_TruckSales
PRIMARY KEY (sales_date, truck_class, truck_type)
)

"I would like to make a table ...." Actually, you want a View (query in
Access-speak).

You really need to read a book on database design. I recommend
_Database Design for Mere Mortals_ by Hernandez.

Having said all that, here is a solution to your problem, using your
column names (untested):

SELECT Month(dte) As SalesMonth, [Key] As TruckType,
Sum(vle) As TotalSales
FROM DTA As D
WHERE dte BETWEEN #1/1/2006# AND #12/31/2006#
AND [key] IN
(SELECT TOP 3 [key]
FROM (SELECT [key], Sum(vle) As Sales
FROM DTA
WHERE [key] Like "CLASS8C*"
AND Month(dte) = Month(D.dte)
GROUP BY [key]
ORDER BY Sum(vle) DESC
) As B
)
GROUP BY Month(dte), [Key]

Don't change to Design view once this is in the SQL view, 'cuz Access
will try to put []s where the ()s are. This will cause an error when
the query runs. If that happens just change the []s around the 2nd
SELECT statements in the IN () predicate expression to ()s. Be sure to
change the ]. to ) - without the period.

There is a possibility of a tie in the TOP 3, which means the result
will have more than 3 "TruckTypes" w/ 2 or more that have equal total
sales amounts.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRK1ttYechKqOuFEgEQJ27QCgpBFLc6U9w56MfvE/wkSdGMtIjHoAoIFR
AyRpdEwelAHzTgq5L19j8C0c
=3wxZ
-----END PGP SIGNATURE-----

I have a table for truck sales in canada by make of truck (there is about 8
makes). The records are for monthly sales. I would like to make a table
that has date descending in the left most column (in order to show most
recent results first), and a sum of the top three makes for that month in
column 2 (the top sellers change from month to month).

The data is stored in a large data table (named DTA, with the fields
DTE-date, KEY-items key, and VLE-value) and the descriptions are stored in an
items table (named ITEMS). These tables are linked on the item "key".
Therefore, a simple select query would look like this:

SELECT dta.dte, items.key FROM....WHERE items.key like 'CLASS8C' & '*';

The CLASS8C line restricts results to only those items pertaining to class 8
truck sales in canada. The keys for the various makes look like this:
CLASS8C_FRT, CLASS8C_PTRBLT, etc....

So, in brief, I want the query to display the sum of the top three makes for
each month - how would I do this???