Again, my apologies for not being as clear as I intended to be. My responses
are below as, hopefully in a clear cut manner that will not frustrate you.
Duane Hookom said:
I'm sorry, every time I try to formulate an answer, I get frustrated at
either my lack of being able to ask for information or your inability to read
or answer my questions.
When I asked "grouped by some field or fields" I would expect you to state:
I want the numbers grouped by Account or Branch or Symbol or Rep or other
field(s).
Within the table names that I gave you, I have a field that I've called
"Account Name". I'd like the commissions grouped by "Account Name" but with
one amendment. I import all the data into Access once a week, so, for the
current year that the commission is grouped it'll always be Year to date.
However, in the previous year, it seems to combine all the trades that's
already in the database; which is the entire year. I'd like to be able to
give a date range for the previous year to appear within this report.
When I asked percentage of what, again I wanted you to come back and state:
I am summing FieldX.
"between the 2 years" is a difference of 365 days
The percentage change has to be a manual calculation because I don't have a
field listed within the database for it. As is the field that it's
calculated from. I want to insert a column that will read, "Year over Year
difference". It will be the dollar difference between Year 2006 and Year
2007. Once that column is created, I'm then able to calculate a percentage
change between the years. The equation would read (Year over Year
difference/Year 2006). It would be the Percentage change in commissions from
the previous Year to date to the current Year to date.
Did you give us any information on the table mentioned in 4)?
I did not give you any information listed on this table for the 4 (Projected
commission). However, I do have another table that the information is
stored. Are you saying that I couldn't use 2 combined tables in a crosstab
query. I didn't add it to this table because it's not something that I could
easily feed weekly into the database. So, I just entered it into another
table that may be updated less than a dozen times through the year to
minimize manual input.
6) now you are substituting "Money Managers" for client? There was no Client
mentioned in any table and there is no "Money Manager" mentioned in any
table.
Yes...Given the table that I outlined to you. The "Money Manager" or
"Client" is actually the "Account Name". My apologies, I was very general
and vague with you because I thought that you needed the concept not the
actual scenerio. I didn't want you to write the program, I thought you would
give me some advice on how to proceed. I will be more exact going forward.
Perhaps you can look at the Northwind and give us a "story problem" like:
I need to total the Freight cost by SalesPerson in the Orders table for 1996
and 1997 and display the totals for the year as well as the percent increase
or decrease per year. This needs to be displayed in a report that totals by
customer and then by salesperson.
My story would be as follows:
I need the YTD commission for my company's current year as it relates to the
previous year sorted by "Account Name". For each "Account Name", I need to
see the following:
1) Percentage change in commission
2) Dollar change in commission
3) The previous year-ending commission total
4) Current projected commission total
5) Current percentage to the projected total
6) The Numerical Rank as of the previous year
7) The Numerical Rank as of the current year
Hope I was clear enough for you. Please let me know. And thanks for your
response.
Andre Adams
--
Duane Hookom
Microsoft Access MVP
:
My responses are below.
:
I would expect these numbers need to be grouped by some field or fields. You
haven't provide this. You also didn't provide the table name.
1) Percentage change in 2007 vs 2006.
percentage of what?
It's the percentage of the difference between the 2 years. So if I
had a total
$1000.00 in 2007 and $1020.00 in 2006, the % change would be -9%.
4) Projected 2007 Commission
What do you mean by Projected?
This is a column that I created. I manually put this in every year
into a table
that I've classified as "Money Manager". It holds all of the
Manager's contact
information in addition to what commission we expect to generate for
them
in the upcoming year.
6) Year 2006 Ranking of each client as of 2006
There is no "client" in your table
"Ranking" by what? Commission, shares traded, ?
2006 Ranking for each "client" means that we are listing our Money
Managers
in numerical order by commission. We do this for each year on the
report.
So, one of our Money Managers, Alliance Capital may have been
ranked by
commission in 2006 to be #3 but in 2007 he may be ranked #8. We
need to
see them ranked by client, year and numerical order by commission.
7) Year 2007 Ranking of each client as of 2007.
There is no "client" in your table
"Ranking" by what? Commission, shares traded, ?
Just answered above.
Very good questions. I guess I wasn't very clear in the beginning. Please
let me know if you need anything further from me. BTW, I've enclosed the SQL
version of what I've done thus far, just in case you need to take a look at
it.
TRANSFORM Sum(tblTradeListingGroup1.[Commission Amount]) AS [SumOfCommission
Amount]
SELECT tblTradeListingGroup1.[Account Name],
Sum(tblTradeListingGroup1.[Commission Amount]) AS [Total Of Commission Amount]
FROM tblTradeListingGroup1
GROUP BY tblTradeListingGroup1.[Account Name]
PIVOT Format([SettleDate],"yyyy");
Thanks,
Andre Adams
--
Duane Hookom
Microsoft Access MVP
:
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.
:
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
:
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.
:
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
:
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.
