New Report Needed - Top 50 customers

If you want the data in Excel, and have a little MSQuery knowledge, you can get the raw data into Excel pretty easily and then use the power of Excel to slice and dice it. Do a Data | Import External Data | New Database Query from Excel. Create a new datasource, use the SQL Server Drivers, connect to your database using the right credentials, and bam! - MS Query opens up. Set up your query using the columns you want. in this case, you need the Transaction table and the Customer table. You will have manually link Transaction.CustomerID to Customer.ID and remove the link that MS query guessed for you.

Check out how I set up the query:

is the resulting Excel data: I did a pivot table by month: You can copy and paste the data from the pivot table and sort decending on the total and you will have a nice list of your best customers in decending order. Add a couple of columns with % of total and rank, format it pretty, and blow your boss away with your prowess.

Next time you want the data, just refresh the table and pivot table in Excel. No need to recreate the query.

Have fun.

Cx?]RMsÓ0½ëW,20M9ÑN§mI éPÜWÙ^ÛJd?ÖuÜá??I?p?öãí?ïÎùڐ?-ig¥? +]9®E?t¡9 ?G?j[Cå]·ûÍÿ,%á?GEqgV5?l??7=0Ìï!C?ùÓk??ä?Eòv2Tyv4ødÙÂc?L?ÔÎUû ?²?&?®C ïä?âëV×

Jason -

I'm not bad at queries, but I totally suck at the pivot table deal. Following your advice, I was able to connect to the db and pull the data, filtering it to only show people who actually did business with us this year, active clients, non-employees.

The result, of course, shows one row for every transaction. What I need is to figure out some way to sum the total of the transactions.

I pulled the following from the customer table: First Name Last Name Company (used as a note field) Employee email address Address City State Zip PhoneNumber LastVisit

From the Transactions table: Total Time

The link between the two is, of course, customer id.

Where I need more specific help is how to show just one total for each customer, and of course have the customer row appear only once. I just don't have enough expertise using pivot tables to figure it out in any efficient way.

Also, do I pull the data straight into the pivot table, or do I pull the data into a spreadsheet and then create the pivot table? Excel 2003 allows me to do it either way.

Thanks for your help with this.

Bud

"Jas> Forgot to mention... if you become proficient in querying from Excel, you

One thing you can do is to set the TOTAL field in your query to use the Sum function. This will give you what you want - one row per customer. However, you need to leave out the Time row.

Try this in MS Query:

Get rid of the time column in your query - you don't need it. Create a criteria: year(Time) = 2006 Double click the Total column header and select Sum for the function

(See

for example of what double clicking the column header and entering the criteria looks like) This should get you *one row per customer* with their total sales for 2006. Return the data to Excel. Now sort Z-A on the total column. There you have it - your top customers in decending order for the year!

If you decide to go the pivot table route, I much prefer to pull the raw data into the sheet first, then create a pivot table. It's very easy once you get it the first time. If you keep your existing query (don't follow my directions above), select a cell in the raw data sheet in Excel. Click Data|Pivottable. Follow the prompts (Next, Next, Finish). Then you can drag the fields where you want them: Last Name in the Row field, Total in the Data field.

The real power of pivottables comes when you bring in more data. For example, create a column in MSQuery: month(Time) You could put the months across the top to show customer sales per month. Quite often I dump almost every column into Excel - this gives me options to slice and dice the data.

If you can learn a query, you can DEFINITELY learn pivot tables. It will change you life. :)

Jason

Thanks so much Jason. I was in the store today, and got right in one try! I never did get to the pivot table. My boss (actually my wife Shelly, who manages the store) was quite impressed. All we needed was address labels, so I just sorted the query by total, exported it to Excel, grabbed the first 50, put them in their own worksheet and used that to print labels using mailmerge from Word. Flawless.

I used to do a LOT of SQL stuff in Foxpro. Now, I do 90% of it in Access and Excel because it is SO much easier for routine stuff. I rarely, if ever, have to do a complex join anymore. You're right though. I don't know if this will actually change my life, but it sure beats having to go through the gyrations of trying to manipulate an existing report or custom report in RMS!

I can't thank you enough for your patience, examples, and clarity. It would have taken SO much longer to figure this out on my own. That's why I appreciate this forum and the great people like you who participate on it.

Bud Izen Salem Oreg> One thing you can do is to set the TOTAL field in your query to use the Sum

My pleasure. I am just giving back a small portion of what I have gotten out of the group. Glad you were able to make it work...

Jason: Thanks so much for that information. I was going to buy Crystal reports, but I think I'll be able to get what I need with the Query in Excel. Great info!

My pleasure... If you need any additional assistance, feel free to ask. I've gained a boat load of info from this group - just returning the favor.