Query Totals

[Andre Adams]

I'm having a problem creating a query.

I have a Sales Query that I'm trying to run that is based off of the
following information.

1) Region (State in Which the Sales occured)
2) Banker (Person that Manages the money for the Client)
3) Master Client (Client that has an account through the firm with the Banker)
4) Budget (The annual amount of money we expect to recieve from the client)

The problem is, there is more than one Banker that manages money for the
same client. Now, what I did was, created a table just for Master Client.
Within that table I added a column that shows the budget for that client. So
within the query I connect that table to my main table and pull the budget
from it. Now, when I run the query it will show me all the information that
I asked for, but, if there is 2 different bankers with the same Master
Client, it shows me the Budget twice. How would I run the query to show the
overall Budget to be subtracted cumulatively no matter how many bankers are
assigned to the Master Client?

Below is the SQL to my current query. Thank you so much in advance, this is
a very rushed project so any help would be appreciated.

SELECT tblMasterClient.[Master Client], tblregion.Region,
Sum(tblAccounting.[Total Revenues]) AS [SumOfTotal Revenues],
tblBanker.Banker, tblMasterClient.[Year 2008 Budget]
FROM tblMasterClient RIGHT JOIN (tblBanker RIGHT JOIN (tblregion RIGHT JOIN
tblAccounting ON tblregion.ID = tblAccounting.Region) ON tblBanker.ID =
tblAccounting.Banker) ON tblMasterClient.ID = tblAccounting.[Master Client]
WHERE (((tblAccounting.[Sale Date]) Between [What Sale Date should I begin?]
And [What Sale Date should I end?]))
GROUP BY tblMasterClient.[Master Client], tblregion.Region,
tblBanker.Banker, tblMasterClient.[Year 2008 Budget];

 

[Fred]

I was following you regarding what you're trying to do and what the problem
is until you jumped to another universe regarding mission and new undefined
terminology when you said "Client, it shows me the Budget twice. How would I
run the query to show the overall Budget to be subtracted cumulatively"

Assuming that all of this means that you just want ot get rid of the
duplication......

"one line only" means that you can't list the banker anyway. Make a new
query without the banker table.

If you have more complex needs, reports can do a lot nicer job of listing,
grouping and summarizing than queries.

 

[Andre Adams]

Hey Fred,

Thanks for the reply. I do want to get rid of the duplications, but, I need
the report to show the Bankers. The report would be broken down as such...

Budget Actual Variance
% of Actual to Budget
Region
Banker
Master Client 600000 50000 550000 .084%

So for each banker, it should show the Master Client accounts listed. How
would I have the Master Client budget cumulatively decrease no matter how
many times it shows up on this report?

I was following you regarding what you're trying to do and what the problem
is until you jumped to another universe regarding mission and new undefined
terminology when you said "Client, it shows me the Budget twice. How would I
run the query to show the overall Budget to be subtracted cumulatively"

Assuming that all of this means that you just want ot get rid of the
duplication......

"one line only" means that you can't list the banker anyway. Make a new
query without the banker table.

If you have more complex needs, reports can do a lot nicer job of listing,
grouping and summarizing than queries.

I'm having a problem creating a query.

I have a Sales Query that I'm trying to run that is based off of the
following information.

1) Region (State in Which the Sales occured)
2) Banker (Person that Manages the money for the Client)
3) Master Client (Client that has an account through the firm with the Banker)
4) Budget (The annual amount of money we expect to recieve from the client)

The problem is, there is more than one Banker that manages money for the
same client. Now, what I did was, created a table just for Master Client.
Within that table I added a column that shows the budget for that client. So
within the query I connect that table to my main table and pull the budget
from it. Now, when I run the query it will show me all the information that
I asked for, but, if there is 2 different bankers with the same Master
Client, it shows me the Budget twice. How would I run the query to show the
overall Budget to be subtracted cumulatively no matter how many bankers are
assigned to the Master Client?

Below is the SQL to my current query. Thank you so much in advance, this is
a very rushed project so any help would be appreciated.

SELECT tblMasterClient.[Master Client], tblregion.Region,
Sum(tblAccounting.[Total Revenues]) AS [SumOfTotal Revenues],
tblBanker.Banker, tblMasterClient.[Year 2008 Budget]
FROM tblMasterClient RIGHT JOIN (tblBanker RIGHT JOIN (tblregion RIGHT JOIN
tblAccounting ON tblregion.ID = tblAccounting.Region) ON tblBanker.ID =
tblAccounting.Banker) ON tblMasterClient.ID = tblAccounting.[Master Client]
WHERE (((tblAccounting.[Sale Date]) Between [What Sale Date should I begin?]
And [What Sale Date should I end?]))
GROUP BY tblMasterClient.[Master Client], tblregion.Region,
tblBanker.Banker, tblMasterClient.[Year 2008 Budget];

 

[Margaret Bartley]

You've got more information in your SQL statement than just the four fields
in your explanation, so I'll give you a general sense of what I think you
need to do, but you'll have to work on the details yourself.

Assuming
* multiple records per banker per client (otherwise, why would you be
summing Total Revenues?)
*One Region per client
*One Budget per client

you will want to create a Totals Query by grouping by Banker and by Client,
using Sum for the Revenue, and using First for the Region and the Budget.

This will give all the bankers for the same client the same Budget and
Region, and totaling the Revenue by banker.

I'm not sure where the date field is in this. It will probably be a WHERE
expression. It could be paramaterized.