Trying to extract stats from lots of information in access

M

mrmag2000

Hello, I am in need of some help on how to create a report that gathers the
information needed below.


I currently have an Access file that has over 100,000 records with about 30
different columns of information.

One of the columns lists a "class" of customer. One, Two and Three (class).

Another column has a total amount they have paid over the lifetime of their
membership (all years totaled).

And another column has the year in which they became a customer.

Basically with these columns of information, I would like to get a report (or
anything that works the best) the first sorts the customers by Class. Then I
would like it to sort the customers by they year they became a customer. And
next I would like to get a grand total of the dollar amount spent by the
customers (for all customers in a given inception year). For example: For
customers that became a member in 1980, I would like to have the total amount
of money they spent over their entire life. Then another subtotal for
customers created in 1981...etc. Finally, I would like to have a count of
each year that's under each class. So since I'm just interested in class
"One", I would like to get the count of all 1980 customers that are in class
One. Then all 1981 customers that are in class One.

I do not want to see the detail of each customer. My main goal is to be
able to put this into an excel file to run a chart


I would like it to look something like this....

Class One
Year Amount Number of Customers
1980 $550.00 85
1981 $751.00 36
1982 $968.00 91

Class Two
 
D

Duane Hookom

Have you considered opening Excel and creating a pivot table based on your
Access data?
 
M

mrmag2000

Duane said:
Have you considered opening Excel and creating a pivot table based on your
Access data?
Hello, I am in need of some help on how to create a report that gathers the
information needed below.
[quoted text clipped - 33 lines]
Class Two


I guess I didnt know that was possible. I thought excel had a maximum of
65K or so rows.
 
D

Duane Hookom

A pivot table can connect to many more records since only aggregates are
displayed. When you group by a value, you naturally return many fewer rows.

--
Duane Hookom
Microsoft Access MVP


mrmag2000 said:
Duane said:
Have you considered opening Excel and creating a pivot table based on your
Access data?
Hello, I am in need of some help on how to create a report that gathers the
information needed below.
[quoted text clipped - 33 lines]
Class Two


I guess I didnt know that was possible. I thought excel had a maximum of
65K or so rows.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top