top ten by month

[brandonlee via AccessMonster.com]

I would like to create a query using the following fields and I’m unsure of
the best way to approach it.

Table fields: (table has 16 months of data)
Access person, Transaction, symbol, trade date, quantity, price, principal
amount, account number, broker name.

First query: show top 10 transactions by “principal amount†for each month

Second query: weekly aggregation per access person over 50k in transactions
in a single security (access person buying a single security across multiple
accounts)

Third: daily aggregation per access person over 10k in transactions in a
single security (access person buying a single security across multiple
accounts)


your help is appreciated, thank you

 

[MGFoster]

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

If the "security" is indicated by the value of the 'symbol' column
(field) and the "principal amount" is the "price X quantity" then,
perhaps, these:

1. SELECT TOP 10 Month([trade date]) As MonthNbr,
[access person], [principal amount]
FROM table_name
ORDER BY [principal amount] DESC

2. SELECT [access person], DatePart("ww", [trade date]) As Week,
[symbol], SUM([principal amount]) As Totals
FROM table_name
GROUP BY [access person], DatePart("ww", [trade date]), [symbol]
HAVING SUM([principal amount]) > 50000

3. SELECT [access person], [trade date], [symbol],
SUM([principal amount]) As Totals
FROM table_name
GROUP BY [access person], [trade date], [symbol]
HAVING SUM([principal amount]) > 10000

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSTurmIechKqOuFEgEQIiZwCdFRB0SGRkBgAxGp/NoLsEgh4xNq8AniFj
kPj9/VvyhVyXk9AkZ5xyFO0o
=6adW
-----END PGP SIGNATURE-----

 

[John Spencer]

Top 10 by month

SELECT [Principal Amount], Month([Trade Date])
FROM [Your Table] As A
WHERE [Principal Amount] in
(SELECT Top 10 [Principal Amount]
FROM [Your table] as Temp
WHERE Month(Temp.[Trade Date] = Month(A.[Trade Date])
ORDER BY [Principal Amount] Desc)

You need to further define your requirements for the second and third
query. For instance, is the 50K in transactions over the entire
database or is it limited to the aggregate period or is it limited up to
the end of the aggregate period. Same thing goes for the third query.

Also what fields are you using to identify a single security (symbol?)
and what are you using for aggregation (Principal Amount?)

With your table names and field names you are going to have to use
multiple stacked queries.

First Query: Identify persons and total transactions over the entire
database.

SELECT [Access Person] as Purchaser, Symbol
, Sum([Principal Amount] as TotalPurchase
FROM [Your Table]
GROUP BY [Access Person] as Purchaser, Symbol
HAVING Sum([Principal Amount]) > 50000

That is just a start.

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

 

[brandonlee via AccessMonster.com]

still having trouble with this query... my sql is not that strong, i use the
query tool in access. so if you could explain the process using that tool i
think it would help me a LOT.

Fields
• Trade Date
• Principal Amount
• Employee
• Symbol
• Quantity
• Price
• Account Number
• Broker Name

Create Query/Reports

1. All single transactions valued at $50K or more, per month
• If there are none or less than 10 transactions over $50K, the
report will show the top 10 largest $ transactions (principal amount).

2. Weekly aggregation per employee of over $200K in transactions in a single
account

3. Weekly aggregation per employee of over $50k in transactions across
multiple accounts (employee can have multiple a/c #) in a single security
(symbol)

Top 10 by month

SELECT [Principal Amount], Month([Trade Date])
FROM [Your Table] As A
WHERE [Principal Amount] in
(SELECT Top 10 [Principal Amount]
FROM [Your table] as Temp
WHERE Month(Temp.[Trade Date] = Month(A.[Trade Date])
ORDER BY [Principal Amount] Desc)

You need to further define your requirements for the second and third
query. For instance, is the 50K in transactions over the entire
database or is it limited to the aggregate period or is it limited up to
the end of the aggregate period. Same thing goes for the third query.

Also what fields are you using to identify a single security (symbol?)
and what are you using for aggregation (Principal Amount?)

With your table names and field names you are going to have to use
multiple stacked queries.

First Query: Identify persons and total transactions over the entire
database.

SELECT [Access Person] as Purchaser, Symbol
, Sum([Principal Amount] as TotalPurchase
FROM [Your Table]
GROUP BY [Access Person] as Purchaser, Symbol
HAVING Sum([Principal Amount]) > 50000

That is just a start.

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

I would like to create a query using the following fields and I’m unsure of
the best way to approach it.

[quoted text clipped - 14 lines]

your help is appreciated, thank you

 

[John Spencer]

The first example will show the top 10 transactions.
--- Add your table to a new query
--- Add Principal Amount and Trade Date to the fields
--- Change Trade Date to read
TheMonth: Month([Trade Date])
--- In the criteria under Principal amount you will need to type
IN ((SELECT Top 10 [Principal Amount] FROM [Your table Name] as Temp
WHERE Month(Temp.[Trade Date]) = Month([Your Table Name].[Trade Date]) ORDER
BY [Principal Amount] Desc)
--- On the next criteria line down under Principal amount enter

=50000

That query will return up to 12 months and will combine the months for
multiple years into one result.

If you need years and months you can change it to
--- Add your table to a new query
--- Add Principal Amount and Trade Date to the fields
--- Change Trade Date to read
TheMonth: Format([Trade Date],"YYYY-MM")
--- In the criteria under Principal amount you will need to type
IN ((SELECT Top 10 [Principal Amount] FROM [Your table Name] as Temp
WHERE Format(Temp.[Trade Date],"yyyy-mm") = Format([Your Table Name].[Trade
Date],"yyyy-mm") ORDER BY [Principal Amount] Desc)
--- On the next criteria line down under Principal amount enter

=50000

IF that works perhaps we can move onto your other queries
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[brandonlee via AccessMonster.com]

I have it working correctly, thank you very much! can you advise on the
other queries? thx

The first example will show the top 10 transactions.
--- Add your table to a new query
--- Add Principal Amount and Trade Date to the fields
--- Change Trade Date to read
TheMonth: Month([Trade Date])
--- In the criteria under Principal amount you will need to type
IN ((SELECT Top 10 [Principal Amount] FROM [Your table Name] as Temp
WHERE Month(Temp.[Trade Date]) = Month([Your Table Name].[Trade Date]) ORDER
BY [Principal Amount] Desc)
--- On the next criteria line down under Principal amount enter

=50000

That query will return up to 12 months and will combine the months for
multiple years into one result.

If you need years and months you can change it to
--- Add your table to a new query
--- Add Principal Amount and Trade Date to the fields
--- Change Trade Date to read
TheMonth: Format([Trade Date],"YYYY-MM")
--- In the criteria under Principal amount you will need to type
IN ((SELECT Top 10 [Principal Amount] FROM [Your table Name] as Temp
WHERE Format(Temp.[Trade Date],"yyyy-mm") = Format([Your Table Name].[Trade
Date],"yyyy-mm") ORDER BY [Principal Amount] Desc)
--- On the next criteria line down under Principal amount enter

=50000

IF that works perhaps we can move onto your other queries
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

still having trouble with this query... my sql is not that strong, i use the
query tool in access. so if you could explain the process using that tool i

[quoted text clipped - 22 lines]

multiple accounts (employee can have multiple a/c #) in a single security
(symbol)

 

[John Spencer]

Sorry, did not have time to go build the other queries and instructions. Plus
I am not sure what your criteria are. This query will return data for number
2. I'm not sure it actually answers your question

SELECT [Account Number], Employee, Symbol
, Format([Trade Date],"yyyy-ww") as TheWeek
, Sum([Principal Amount]) as TheTotal
FROM [Your Table Name] as T
GROUP BY [Account Number], Employee, Symbol
, Format([Trade Date],"yyyy-ww")
HAVING Sum([Principal Amount]) > 200000

Build the query in design view
-- Add your table to a new query
-- Add Account Number, Employee, Trade Date, Symbol and Principal Amount
-- Edit Trade Date to read
TheWeek: Format([Trade Date],"yyyy-ww")
-- Select View: Totals
-- Change Group By to SUM under Principal Amount
-- Enter > 200000 as Criteria under Principal Amount

Possible modifications:
If you want to limit the result to a specific time frame (for instance the Jan
to Dec of 2007)
-- Add the Trade Date field a SECOND Time
-- Change Group by to WHERE
-- Enter criteria under the SECOND trade date
Between #2007-01-01# and #2007-12-31#

If you want the total for an employee but ignoring the stock, remove Symbol
field from the query.

The third query is probably a modification of the above but you would need to
remove the Account Number field from the query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County