SumOfTotals

[Sandy]

I have a query which is roughly as follows

Rep1 Rep2 Job Cost(£)
AT 50
CW AT 70
PM CW 100
CW 80
AT PM 90

Where the Rep2 field is null the Job Cost is allocated to Rep1. Where the
Rep2 field is not null the Job Cost is allocated at 50% toRep1 and 50% to
Rep2.

I can do group by sum for each case, that is not the problem. What I would
like to do is simply have a form (or whatever) that shows the absolute
totals for each Rep.

E.g. using the above :-
AT = £130
CW = £165
PM = £95

Any thoughts?
Sandy

 

[Dirk Goldgar]

I have a query which is roughly as follows

Rep1 Rep2 Job Cost(£)
AT 50
CW AT 70
PM CW 100
CW 80
AT PM 90

Where the Rep2 field is null the Job Cost is allocated to Rep1. Where the
Rep2 field is not null the Job Cost is allocated at 50% toRep1 and 50% to
Rep2.

I can do group by sum for each case, that is not the problem. What I would
like to do is simply have a form (or whatever) that shows the absolute
totals for each Rep.

E.g. using the above :-
AT = £130
CW = £165
PM = £95

Any thoughts?

I don't suppose the underlying tables for that query are more normalized?
Assuming they aren't, the first thing you have to do is normalize the data
from the first query. Suppose that original query is named "SourceQuery".
Then we need to do this to normalize:

SELECT
Rep1 AS Rep,
IIf(Rep2 Is Null, [Job Cost], [Job Cost] / 2) As JobCost
FROM SourceQuery
UNION ALL
SELECT
Rep2 AS Rep,
([Job Cost] / 2) As JobCost
FROM SourceQuery
WHERE Rep2 Is Not Null

Save that query as NormalizedJobCosts. Using that as a base, you can now
easily get totals by Rep:

SELECT
Rep,
Sum(JobCost) As TotalJobCost
FROM NormalizedJobCosts
GROUP BY Rep

 

[Sandy]

Hi Dirk,

My two 'SumOfTotal' queries are as follows :-

qry_Repairer_Totals
Rep1
Rep2 - Criteria "Is Null"
Total - Group By 'Sum'

qry_Repairer_Shared_Totals
Rep2 -Criteria "Is Not Null"
Total - Group By 'Sum'
Repairer Share: Sum([Total])/2 - which is a Calculated field.

Where should I place the :-
"SELECT
Rep1 AS Rep,
IIf(Rep2 Is Null, [Job Cost], [Job Cost] / 2) As JobCost
FROM SourceQuery
UNION ALL
SELECT
Rep2 AS Rep,
([Job Cost] / 2) As JobCost
FROM SourceQuery
WHERE Rep2 Is Not Null"

Thanks
Sandy

I have a query which is roughly as follows

Rep1 Rep2 Job Cost(£)
AT 50
CW AT 70
PM CW 100
CW 80
AT PM 90

Where the Rep2 field is null the Job Cost is allocated to Rep1. Where the
Rep2 field is not null the Job Cost is allocated at 50% toRep1 and 50% to
Rep2.

I can do group by sum for each case, that is not the problem. What I
would like to do is simply have a form (or whatever) that shows the
absolute totals for each Rep.

E.g. using the above :-
AT = £130
CW = £165
PM = £95

Any thoughts?

I don't suppose the underlying tables for that query are more normalized?
Assuming they aren't, the first thing you have to do is normalize the data
from the first query. Suppose that original query is named "SourceQuery".
Then we need to do this to normalize:

SELECT
Rep1 AS Rep,
IIf(Rep2 Is Null, [Job Cost], [Job Cost] / 2) As JobCost
FROM SourceQuery
UNION ALL
SELECT
Rep2 AS Rep,
([Job Cost] / 2) As JobCost
FROM SourceQuery
WHERE Rep2 Is Not Null

Save that query as NormalizedJobCosts. Using that as a base, you can now
easily get totals by Rep:

SELECT
Rep,
Sum(JobCost) As TotalJobCost
FROM NormalizedJobCosts
GROUP BY Rep


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk,

My two 'SumOfTotal' queries are as follows :-

qry_Repairer_Totals
Rep1
Rep2 - Criteria "Is Null"
Total - Group By 'Sum'

qry_Repairer_Shared_Totals
Rep2 -Criteria "Is Not Null"
Total - Group By 'Sum'
Repairer Share: Sum([Total])/2 - which is a Calculated field.

I don't think those two queries are enough in themselves (if I'm
interpreting what you posted correctly). They give you the totals for Rep1
when there is no Rep2, and the totals for Rep2, but they don't give you the
totals for Rep1 when there *is* a Rep2.

Where should I place the :-
"SELECT
Rep1 AS Rep,
IIf(Rep2 Is Null, [Job Cost], [Job Cost] / 2) As JobCost
FROM SourceQuery
UNION ALL
SELECT
Rep2 AS Rep,
([Job Cost] / 2) As JobCost
FROM SourceQuery
WHERE Rep2 Is Not Null"

It's hard to describe queries properly in with design view. It's easier to
switch the queries to SQL view, and copy that for posting to the newsgroup.
I could be more specific with my instructions if I knew the table/query
those two queries of yours were selecting from. Please post the SQL view of
qry_Repairer_Totals.

 

[Sandy]

Query_Repairer_Totals SQL as requested.

"SELECT qryJob_Details.RepName, qryJob_Details.RepName2,
Sum(qryJob_Details.Total) AS SumOfTotal
FROM qryJob_Details
GROUP BY qryJob_Details.RepName, qryJob_Details.RepName2
HAVING (((qryJob_Details.RepName2) Is Null));

And Query_Repairer_Shared_Totals (not requested but....)

"SELECT qryJob_Details.RepName2, Sum(qryJob_Details.Total) AS
SumOfTotal, Sum([Total]/2) AS [Repairer Share]
FROM qryJob_Details
GROUP BY qryJob_Details.RepName2
HAVING (((qryJob_Details.RepName2) Is Not Null));

Thanks
Sandy

Hi Dirk,

My two 'SumOfTotal' queries are as follows :-

qry_Repairer_Totals
Rep1
Rep2 - Criteria "Is Null"
Total - Group By 'Sum'

qry_Repairer_Shared_Totals
Rep2 -Criteria "Is Not Null"
Total - Group By 'Sum'
Repairer Share: Sum([Total])/2 - which is a Calculated field.

I don't think those two queries are enough in themselves (if I'm
interpreting what you posted correctly). They give you the totals for
Rep1 when there is no Rep2, and the totals for Rep2, but they don't give
you the totals for Rep1 when there *is* a Rep2.

Where should I place the :-
"SELECT
Rep1 AS Rep,
IIf(Rep2 Is Null, [Job Cost], [Job Cost] / 2) As JobCost
FROM SourceQuery
UNION ALL
SELECT
Rep2 AS Rep,
([Job Cost] / 2) As JobCost
FROM SourceQuery
WHERE Rep2 Is Not Null"

It's hard to describe queries properly in with design view. It's easier
to switch the queries to SQL view, and copy that for posting to the
newsgroup. I could be more specific with my instructions if I knew the
table/query those two queries of yours were selecting from. Please post
the SQL view of qry_Repairer_Totals.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Query_Repairer_Totals SQL as requested.

"SELECT qryJob_Details.RepName, qryJob_Details.RepName2,
Sum(qryJob_Details.Total) AS SumOfTotal
FROM qryJob_Details
GROUP BY qryJob_Details.RepName, qryJob_Details.RepName2
HAVING (((qryJob_Details.RepName2) Is Null));

And Query_Repairer_Shared_Totals (not requested but....)

"SELECT qryJob_Details.RepName2, Sum(qryJob_Details.Total) AS
SumOfTotal, Sum([Total]/2) AS [Repairer Share]
FROM qryJob_Details
GROUP BY qryJob_Details.RepName2
HAVING (((qryJob_Details.RepName2) Is Not Null));

Okay. Now I know the names of the source query and the relevant fields it
returns. Here's what I recommend:

1. Create a new query, then switch to SQL view.

2. Delete whatever partial SQL may be In the SQL view window, and paste in
the SQL that you see between the
"start of SQL" and "end of SQL" lines below (not including those boundary
lines):

----- start of SQL -----
SELECT
RepName,
IIf(RepName2 Is Null, [Total], [Total] / 2) As RepTotal
FROM qryJob_Details
UNION ALL
SELECT
RepName2 AS RepName,
([Total] / 2) As RepTotal
FROM qryJob_Details
WHERE RepName2 Is Not Null;

----- end of SQL -----

3. Save that query as "qryRepairerTotalsNormalized", and close it. You may
want to try opening it in datasheet view now, to ensure that I haven't made
a mistake in the SQL.

4. Create another new query. Switch that query to SQL view.

5. Delete whatever partial SQL may be In the SQL view window, and paste in
the SQL that you see between the
"start of SQL" and "end of SQL" lines below (not including those boundary
lines):

----- start of SQL -----
SELECT
RepName,
Sum(qryRepairerTotalsNormalized.RepTotal) As RepTotal
FROM qryRepairerTotalsNormalized
GROUP BY RepName;

----- end of SQL -----

6. Having pasted in the SQL, you can switch this query back to design view
if you like. Save the query as whatever name you like, and test it in
datasheet view to see if it works.

One thing worth noting: you may some some slight oddities due to rounding
errors. We are dividing at the detail level, then summing. Depending on
your needs, it may be necessary to sum, then divide, or else to divide, then
round, then sum. We'll see.

 

[Sandy]

Hi Dirk,

Your suggestion works perfectly. Thank you.

Can I ask.... having never seen :-
"IIf(RepName2 Is Null, [Total], [Total] / 2) As RepTotal"
What exactly does it do and why the two I's in IIf?

Thank you once again
Sandy

Query_Repairer_Totals SQL as requested.

"SELECT qryJob_Details.RepName, qryJob_Details.RepName2,
Sum(qryJob_Details.Total) AS SumOfTotal
FROM qryJob_Details
GROUP BY qryJob_Details.RepName, qryJob_Details.RepName2
HAVING (((qryJob_Details.RepName2) Is Null));

And Query_Repairer_Shared_Totals (not requested but....)

"SELECT qryJob_Details.RepName2, Sum(qryJob_Details.Total) AS
SumOfTotal, Sum([Total]/2) AS [Repairer Share]
FROM qryJob_Details
GROUP BY qryJob_Details.RepName2
HAVING (((qryJob_Details.RepName2) Is Not Null));

Okay. Now I know the names of the source query and the relevant fields it
returns. Here's what I recommend:

1. Create a new query, then switch to SQL view.

2. Delete whatever partial SQL may be In the SQL view window, and paste in
the SQL that you see between the
"start of SQL" and "end of SQL" lines below (not including those boundary
lines):

----- start of SQL -----
SELECT
RepName,
IIf(RepName2 Is Null, [Total], [Total] / 2) As RepTotal
FROM qryJob_Details
UNION ALL
SELECT
RepName2 AS RepName,
([Total] / 2) As RepTotal
FROM qryJob_Details
WHERE RepName2 Is Not Null;

----- end of SQL -----

3. Save that query as "qryRepairerTotalsNormalized", and close it. You
may want to try opening it in datasheet view now, to ensure that I haven't
made a mistake in the SQL.

4. Create another new query. Switch that query to SQL view.

5. Delete whatever partial SQL may be In the SQL view window, and paste in
the SQL that you see between the
"start of SQL" and "end of SQL" lines below (not including those boundary
lines):

----- start of SQL -----
SELECT
RepName,
Sum(qryRepairerTotalsNormalized.RepTotal) As RepTotal
FROM qryRepairerTotalsNormalized
GROUP BY RepName;

----- end of SQL -----

6. Having pasted in the SQL, you can switch this query back to design view
if you like. Save the query as whatever name you like, and test it in
datasheet view to see if it works.

One thing worth noting: you may some some slight oddities due to rounding
errors. We are dividing at the detail level, then summing. Depending on
your needs, it may be necessary to sum, then divide, or else to divide,
then round, then sum. We'll see.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Your suggestion works perfectly. Thank you.

Great! You're welcome.

Can I ask.... having never seen :-
"IIf(RepName2 Is Null, [Total], [Total] / 2) As RepTotal"
What exactly does it do and why the two I's in IIf?

"IIf" is the name of a built-in function that exists in both Jet (Access)
SQL and in VBA. I think it stands for "Immediate If". It takes three
arguments: <condition>, <value if true>, and <value if false>. The
function evalutes the truth or falsehood of the <condition> argument and
returns <value if true> if <condition> is true. If <condition> is not
true, the function returns <value if false>.

I should point out that the third argument, which I'm calling <value if
false>, will also be returned if the condition is neither true nor false,
but Null. So really it has the effect of an Else clause in an
"If-Then-Else" structure.

The expression

IIf(RepName2 Is Null, [Total], [Total] / 2)

tests RepName2 to see if it's Null. If it is, the function returns [Total].
If it isn't, it returns [Total] / 2.

So in the context it was used, if there's no second repairer, then repairer
1 gets all of the total; if there are two repairers, repairer 1 gets only
half of the total.