can someone help me getting started with reports?

[Craig Armitage]

Well ive got some books and ive tried to read and understand them but its
just not making any sense to me...

Im sure its very basic but i would like to do the following...

Ive made a database that i want to use to track a few things...namely,
customers, jobs and referals (meaning where a customer got our number,
website, magazine etc)...

I have a referrer table, thats link to customers and a jobs table thats
linked back to the customer. in the job table is the cost of a job.

I would like to make a report that firstly tells me how many customers i
have gained from a specific referrer. and also, how much money i have made
from that referrer via all the customers * jobs.

Can anyone give me step by step info on how to do this... or possibly a link
to a very basic tutorial on reports.

Thanks guys

 

[tina]

suggest you read up on *queries*, to learn how to pull the data you want
from your tables; for your stated needs, focus on Totals queries. once you
have a query that returns the data you want, build a report based on the
query.

hth

 

[Guest]

As Tina says you can use an aggregating (Totals) query, but it might not be
quite so simple if you have undertaken more than one job for the same
customer. Standard SQL supports a COUNT DISTINCT operation but Access queries
do not. You can use a subquery however to count the rows in a customers
table per referrer, while aggregating the total amount earned in the outer
query, like so:

SELECT Referals.ReferrerID, Referrer, SUM(Cost) AS TotalEarned,
(SELECT COUNT(*)
FROM Customers
WHERE Customers.ReferrerID = Referals.ReferrerID)
AS CustomersReferred
FROM Referals, Customers, Jobs
WHERE Referals.ReferrerID = Customers.ReferrerID
AND Customers.CustomerID = Jobs.CustomerID
GROUP BY Referals.ReferrerID, Referrer;

Its then a very simple matter to base a report on the query. It could also
all be done in a report based on a query which simply joins the three tables,
but a query is probably easier.

Ken Sheridan
Stafford, England