RE: Crosstab Query
- From: Andre Adams <AndreAdams@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 27 Aug 2007 12:56:02 -0700
Alright.
The table that I've based this crosstab on has the following fields:
Branch Number: Account Prefix
Account Number: Actual account number
Account Name: This is the actual Managers Name
Symbol: This is the Symbol associated with the actual trade
Description1: This is the actual description of the symbol
Cusip: This is the 9 Digit Alpha-numberic identifier associated with the
symbol
SettleDate: This is the date the trade cleared.
Tran: This shows whether the trade was a Buy or Sell
Shrs/Contr: This shows how many shares were with this trade
Trade Price: This is the price of the shares bought or sold
Commission Amount: This is the Commission generated from the trade
Bltr: This distiguishes what exchange the trade was executed
Rep No: This shows who the representative on the account is.
Clearing Charge: This is the fee associated with clearing the trade.
The fields that I need generated are not populated from this table. I need
to create them manually. And they are:
1) Percentage change in 2007 vs 2006.
2) Commission Dollar Difference from 2007 vs 2006.
3) Year Ending 2006 Commission.
4) Projected 2007 Commission
5) Percent of 2007 YTD commission to projected 2007
6) Year 2006 Ranking of each client as of 2006
7) Year 2007 Ranking of each client as of 2007.
"Duane Hookom" wrote:
We still don't know much about your tables, fields, and data. As per my.
previous posting: "Do you have some table and field names?"
What is the SQL of your crosstab?
--
Duane Hookom
Microsoft Access MVP
"Andre Adams" wrote:
I understand.
The information is not related to the executives, it's relayed to them.
Didn't make that clear, my apologies. I guess what I'm trying to say is
this. Within the Cross Tab Query, I need the columns below to show,
automatically through the setup or manually through design. Access is
telling me that it cannot add any extra columns once I've set up the columns
through the cross-tab query wizard. Please let me know if there is another
way to do this.
"Duane Hookom" wrote:
Do you have some table and field names? It looks like you have all the stuff
you need without creating a crosstab.
I'm lost when you state "relate commissions to the executives" and then
don't have any column or row information that has anything to do with
"executives".
--
Duane Hookom
Microsoft Access MVP
"Andre Adams" wrote:
Hey guys,
I have one more report to create before I am totally finished! This is the
biggee...the master of all my reports. I'm doing a cross-tab query that
deals with my year vs. year data. I'm using a 2 year comparison to relate
commissions to the executives. These are the colums that I need in the
database.
Columns:
Year to date 2006 Already in Database
Year to date 2007 Already in Database
Percentage change The difference between 2007 and
2006
Dollar change The difference between 2007
and 2006
Year End 2006 Commission The previous years total yearly
commission
Projected 2007 Commission Within the Database
Percentage to Projected Comm Percent to the current years Projected
goal
Rank 2006 Each Account Rank as of 2006
Rank 2007 Each Account Rank as of 2007
Rows:
Account Names Each client that we do business
with.
My problem is, Access wont allow me to create extra column. It says that it
already has a column header and won't take a new one. How do I overcome this
obstacle. Any ideas guys? You all have been a great help to me thus far and
I appreciate everything. Please help me get over this last hump and I'll
look like a genius on your guys behalf!
Thanks,
Andre Adams
- Follow-Ups:
- RE: Crosstab Query
- From: Duane Hookom
- RE: Crosstab Query
- References:
- RE: Crosstab Query
- From: Andre Adams
- RE: Crosstab Query
- From: Duane Hookom
- RE: Crosstab Query
- Prev by Date: RE: Crosstab Query
- Next by Date: Changing query definition
- Previous by thread: RE: Crosstab Query
- Next by thread: RE: Crosstab Query
- Index(es):
Relevant Pages
|
