New Query

[Andre Adams]

If I want to create a query out of the below information, how would I do it?

Money Manager (Our clients)
Account Number (Clients Account)
Gross Commission (Commission Generated - on per trade basis)
Plan Sponsor (State account is sponsored by)

So the table looks like this....

Money Manager Account Number Gross Commission Plan Sponsor

Best Suits, LLC 39875 $352,200.48 State
Of Illinois
Dragonfoot, LLC 39876 $258,300.89 State of
New Jersey
Better Credit, LLC 39877 $233,865.88
Outfit, LLC 39878 $200,000.32
State of Illinois
Shapefoot, LLC 39879 $3,048.80
Beright, LLC 39880 $1,203.11

I need a query that would pull only the accounts that have a "Plan Sponsor".
(Mind you I have a database of over 25,000 individual trades) I need for the
query to give me the Gross Commission and Account number along with the Money
Manager. But, I don't need any trades that do no have a Plan Sponsor.

Can someone please help?

 

[Jerry Whittle]

Hi,

In the query design view, pull down all four fields. In the criteria for
Plan Sponsor, put Is Not Null . That should do it. If not there are a
couple of other things to try.

 

[Andre Adams]

Sorry to rain on you this way, but, I ran into another problem while trying
put in "Is not null). All of my "trade information" is in one table, and all
of my "Account Number and Plan Sponsor information" is in another table.
I've tried creating a relationship between Plan Sponsors on both table, but
it doesn't know to link the "account" to that relationship, which is very
important. I've even tried creating a relationship between the Account
Number on both tables and the Plan Sponsors. But it won't let me create
multiple relationships. It brings up another window with a (1) at the end of
it. It's the same table information, but doesn't really connect the two.
What's the most effective way to do this?

 

[KARL DEWEY]

You should be able to do it in a query without creating a relation.

Post your table structure.

 

[Andre Adams]

My table structure is as follows:

Rep No - Representative handing the account
Money Manager - Our Client
Commission - Commission Generated from Client
Account Number - Account associated with Manager
Plan Sponsor - Senior Manager sponsoring the Money Manager

This Report should be Sorted by the Plan Sponsor. However, under the Plan
Sponsor I need to know each Money Manager and Account (coded with the correct
Plan Sponsor already) that has generated a commission for that Sponsor.
So it would look something like this:

Illinois Plan

Rep No Money Manager Commission Account Number

Assigned Name of MM (Sum of Comm Assigned
for this Account)

Please let me know if you need anything further from me.

Andre Adams

 

[KARL DEWEY]

All of my "trade information" is in one table, and all of my "Account
Number and Plan Sponsor information" is in another table.
Before you said two tables and now you say one table. Which is it? If it
is two then post the structure of both.

 

[Andre Adams]

Sorry, it is two table's.

Trade Information table:
Branch
Account No
Account Name
Symbol
Description 1
Cusip
SettlementDate
Transaction
Shares Contracts
TradePrice
Commission Amount
Blotter
RepID
Clearing Charge
Month Traded

Plan Sponsor Table:

RepID
Account Name
Account Number
Illinois Plan
ShortName

 

[KARL DEWEY]

Your table structure still does not match your other postings --
My table structure is as follows:
Rep No - Representative handing the account
Money Manager - Our Client
Commission - Commission Generated from Client
Plan Sponsor - Senior Manager sponsoring the Money Manager

Is Rep No the same as RepID? Queries do not work if you do not use the
exact names for the fields.

What field is the Money Manager?

Then you have Illinois Plan -- Plan Sponsor -- what is right?

No one can help you if you do not post the actual structure. It would also
help to include the datatype with the field name.

Based on you last post you have RepID, Account Name, and Account Number in
both tables so these common fields might be the fields to join in the query.