Query that sums data

[accessnovice78]

Hi,

I am a complete novice at Access and have been thrown in at the deep end by my boss.

I have written the most basic queries thanks to my friend google but now I have encountered something a bit more difficult, my boss is demanding information and I can not figure out how to make my query work.

I have the following fields in my query.

Financial Year
Descrpition
Customer Name
Author
Net Sales
Net Value of Sales

I would like to create the query so that it returns back the Total Net Value of Sales by Customer by Description, ideally that top 100 customers. If I do run the query I have written at the moment I get more than the 65000 lines which I can not export in to excel.

Any suggestions on how to overcome this would be much appreciated.

I am a complete and utter novice.



Thanks

[pbaldy]

I changed your thread title to something more meaningful to future users than "Query driving me nuts!".

Try starting a new query with those 3 fields. On the toolbar/ribbon you'll see a Totals icon that looks like a greek "E". Click on that, and you'll see a new row appear in your query (in design view). Change Group By to Sum for the value field and see if that is doing what you want.

[accessnovice78]

Thanks for the hint about the title name and the suggestion.

I have tried using the totals and the query returns back more rows than can be exported in to excel. Is there any way around this?

Cheers

[pbaldy]

But it's correctly totaling by customer and description like you want? You can add the TOP predicate to the query to limit the records returned, or add a criteria to the value field so it only returns those that total more than a given amount. Given the grouping you may want this:

http://support.microsoft.com/kb/153747/en-us