Grouping & Filter Data With Queries

C

Curtis Stevens

I have affiliates I pay residual payments to. I have a query that pulls the
residual data from a table and a report that uses this query to print out the
reporting information. I do not want the information to come up in the
reports for any affiliate who's residual is less than $25 total.

A screen shot of my query:

http://www.gotmerchant.com/db.gif

Sql data:

SELECT ResidualInfo.AffiliatePaid, Affiliates.Company,
Affiliates.ContactName, Customers.Company, ResidualInfo.ProcessingMonth,
ResidualInfo.ProcessingVolume, ResidualInfo.DiscountTransactions,
ResidualInfo.NetProfit, Affiliates.[Residual Percentage]
FROM (Customers INNER JOIN Affiliates ON Customers.ReferredAffiliateID =
Affiliates.AffiliateID) INNER JOIN ResidualInfo ON Customers.CustomerID =
ResidualInfo.CustomerID
WHERE (((ResidualInfo.AffiliatePaid)=Yes));

I have it setup on the report so each affiliate is broken out into it's own
pages. At the bottom for each affiliate, I have a text box with this source
code to add up all the monies due to them:

Nz(Sum([NetProfit]))*[ResidualPercentage]

MY QUESTION:

What is the best way or approach to do this? Do something on the query or
report side?

Thanks
Curtis
 
C

Curtis Stevens

Any help on the kind of code I need to use, little lost on what to do...

Depends on what your goal of best is, but I'd choose query.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

Curtis Stevens said:
I have affiliates I pay residual payments to. I have a query that pulls the
residual data from a table and a report that uses this query to print out the
reporting information. I do not want the information to come up in the
reports for any affiliate who's residual is less than $25 total.

A screen shot of my query:

http://www.gotmerchant.com/db.gif

Sql data:

SELECT ResidualInfo.AffiliatePaid, Affiliates.Company,
Affiliates.ContactName, Customers.Company, ResidualInfo.ProcessingMonth,
ResidualInfo.ProcessingVolume, ResidualInfo.DiscountTransactions,
ResidualInfo.NetProfit, Affiliates.[Residual Percentage]
FROM (Customers INNER JOIN Affiliates ON Customers.ReferredAffiliateID =
Affiliates.AffiliateID) INNER JOIN ResidualInfo ON Customers.CustomerID =
ResidualInfo.CustomerID
WHERE (((ResidualInfo.AffiliatePaid)=Yes));

I have it setup on the report so each affiliate is broken out into it's own
pages. At the bottom for each affiliate, I have a text box with this source
code to add up all the monies due to them:

Nz(Sum([NetProfit]))*[ResidualPercentage]

MY QUESTION:

What is the best way or approach to do this? Do something on the query or
report side?

Thanks
Curtis
Click to expand...
Click to expand...
 
C

Curtis Stevens

Do you have any code I can use, lost of what to do.

Depends on what your goal of best is, but I'd choose query.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

Curtis Stevens said:
I have affiliates I pay residual payments to. I have a query that pulls the
residual data from a table and a report that uses this query to print out the
reporting information. I do not want the information to come up in the
reports for any affiliate who's residual is less than $25 total.

A screen shot of my query:

http://www.gotmerchant.com/db.gif

Sql data:

SELECT ResidualInfo.AffiliatePaid, Affiliates.Company,
Affiliates.ContactName, Customers.Company, ResidualInfo.ProcessingMonth,
ResidualInfo.ProcessingVolume, ResidualInfo.DiscountTransactions,
ResidualInfo.NetProfit, Affiliates.[Residual Percentage]
FROM (Customers INNER JOIN Affiliates ON Customers.ReferredAffiliateID =
Affiliates.AffiliateID) INNER JOIN ResidualInfo ON Customers.CustomerID =
ResidualInfo.CustomerID
WHERE (((ResidualInfo.AffiliatePaid)=Yes));

I have it setup on the report so each affiliate is broken out into it's own
pages. At the bottom for each affiliate, I have a text box with this source
code to add up all the monies due to them:

Nz(Sum([NetProfit]))*[ResidualPercentage]

MY QUESTION:

What is the best way or approach to do this? Do something on the query or
report side?

Thanks
Curtis
Click to expand...
Click to expand...
 
J

John Spencer

You need to calculate the value - Sum([NetProfit]))*[ResidualPercentage]) -
for each affiliate.

You should be able to use something like the following. I've guessed that
you are grouping on Affiliates.Company.

SELECT ResidualInfo.AffiliatePaid, Affiliates.Company,
Affiliates.ContactName, Customers.Company, ResidualInfo.ProcessingMonth,
ResidualInfo.ProcessingVolume, ResidualInfo.DiscountTransactions,
ResidualInfo.NetProfit, Affiliates.[Residual Percentage]
FROM (Customers INNER JOIN Affiliates ON Customers.ReferredAffiliateID =
Affiliates.AffiliateID) INNER JOIN ResidualInfo ON Customers.CustomerID =
ResidualInfo.CustomerID
WHERE (((ResidualInfo.AffiliatePaid)=Yes)) AND
Affiliates.Company IN
(SELECT Affiliates.Company
FROM (Customers INNER JOIN Affiliates
ON Customers.ReferredAffiliateID = Affiliates.AffiliateID)
INNER JOIN ResidualInfo
ON Customers.CustomerID = ResidualInfo.CustomerID
WHERE ResidualInfo.AffiliatePaid=True
GROUP BY Affiliates.Company
HAVING Sum([NetProfit] * [ResidualPercentage]) >= 25)

What you need to do is use the primary key field of the affiliates table to
limit the records to be returned.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Top