Is this even possible??

[Alex19nz]

OK so here we go..

I've got a huge excel sheet which has the Top 40 music charts for every week since 1999. But heres the deal. I need to create a way to sum certain fields and create some sort of ouput... heres what I mean: (A Smaller version of my chart book)

I want to be able to find out how many times "Cher - Believe" was in the charts and to sum up her weeks in the chart. Essentially I would have something that says

Cher - Believe = Entered 2 Times... Score of 11 (Adding up 5+6)

See what I mean?

I guess basically I want to be able to sum records that have the same artist and produce a report.

lets see how we go eh?

Cheers guys.

 

[alastair69]

it is possible i think but am not sure you could use the Dcount function, you will have to look it up in the help files, to find the correct way to format the Dcount funtion.

Alastair

 

[Alex19nz]

Thanks Alastair69...

That does kinda work but the problem is ive got thousands of entries and it could take some time to do it manually.

But thanks

 

[alastair69]

Thanks Alastair69...

That does kinda work but the problem is ive got thousands of entries and it could take some time to do it manually.

But thanks

You can do this funtion though a query to help speed things on, or group by

 

[Alex19nz]

A better explanation:

I want to be able to

> combine all "STEPS" entries
> sum up their weeks in the chart (9 entries)
> give them a score by adding up the weeks (ie: 1+2+3+4+5+6+7+8+9 giving them a score of 45)

and then displaying this data along with other artists somehow so it could possibly say

STEPS - 5,6,7,8 [9 ENTRIES] [SCORE: 45]
ROBBIE WILLIAMS - STRONG [5 ENTRIES ] [SCORE: 15]
SPICE GIRLS - STOP [2 ENTRIES ] [SCORE: 3]
RONAN KEATING - ROLLERCOASTER [7 ENTRIES ] [SCORE: 28]

etc
etc..

Cheers Guys!

 

[jalverson]

Do you have to use Access or can you leave the data in Excel? If you can leave the data in Excel, have you tried creating a pivot table? The rows that have the weeks will cause a problem but you should be able to create simple pivot table which is grouped by Artist, and has two columns that counts the number of time the Artist's name is in the table and sums your weeks column.

If you are not familiar with pivot tables, e-mail back and I can walk you through the steps.

Jeff