Creating a Database in Excel

[Kevin]

I may not be able to do this, but I figured someone on here could tell me. I
am a music minister and I am trying to create a list of all the songs we use
in the services. It will have the title of the song, the temp, key and last
time used and I would like to have a running total of how many times used
that year. Most of this I know how to create, but was not use how to get the
date last used to add up in totol of plays. Also is there a way to be able
to go back and see all the dates we used that song. I know I am asking alot
but I have tried to figure this out and have come up blank. Thanks for any
suggestions.

 

[Pete_UK]

Why not have several columns for Date Used, then each time you use a
particular song you fill in that date in the next available column. The
last date used is obviously the date in the right-most column and the
number of times used is the number of columns minus 3.

Hope this helps.

Pete

 

[Roger Govier]

Hi Kevin

One simple way would be to record each and every time the song was used
along with all of your other data in a long list in 4 or 5 columns. Set
up a header row with titles for each of your headings, Song Name, Date
Played, Key, Temp etc.
Insert a row above your header row.
Mark your header row and choose Data>Filter>Autofilter
This will provide a dropdown on each column so you can select the song
name etc and see a filtered list of when that song was played.

On row 1 above the Song heading in A1 enter
=SUBTOTAL(3,A3:A1000) this will give a count of the filtered range of
Songs

in cell B1
=SUBTOTAL(4,B3:B1000) will give the latest date played

 

[Debra Dalgleish]

You could create a pivot table, based on your list of songs used. There
are instructions and links here:

http://www.contextures.com/xlPivot01.html

Base the pivot table on a dynamic range, as described at the link above.

When you create the pivot table, put song title in the row area, Last
time used in the Data area, as Count of Time Used, and another copy of
time used in the data area, as Max of Time Used (for the latest date).

 

[Kevin]

Thanks Pete, Roger and Debra. I will be honest. I have only done a couple
of things in Excel and really I am not that familar with it, but I will read
up on all of this and try all of your suggestions and see which works best.
Thanks again. I knew there had to be some way to do this. There is software
out there I could by that will do this and more, but so many of them cost
$150-300! I just knew there had to be a way to do this with the software I
already had and almost all of the demos I saw, had all of this laid out in a
spreadsheet format.
Thanks again.
Kevin

 

[CLR]

Hi Kevin.....
It would be a bit of a project, but just to let you know what "could" be
done in Excel and using VBA......you could scroll down your list of songs
putting a "1,2,3 etc" in a column for each you choose for today......then
"push a button" and the program will place a copy of all those songs at the
bottom of another database which will eventually store all the songs you use,
and their "when used" data......then other buttons using Excel's built in
Autofilter, etc, could easily interrogate that database to give you reports
as detailed as "what song do we do FIRST on the second Sunday's of each month
the most", etc etc.........

hth
Vaya con Dios,
Chuck, CABGx3

 

[Kevin]

Wow.... Now you have gone over my head! LOL.... I have no idea how to use
VBA, but I like the suggestion. Sounds like I need to take a course in Excel
before I do this. Thanks for the suggestion tho, it sounds like a cool idea.
Just wish I knew what the heck I was doing in Excel. Or should I do this in
Access? That I am somewhat familar with having done a couple of SIMPLE
databases.

Kevin

 

[CLR]

Sorry, didn't mean to scare you...........just wanting to let yo know that
there is indeed a GRAND light at the end of the tunnel...........take a look
at the Macro Recorder......Tools > Macros > Record new macro............it
will take all of the following keystrokes and "record" them into a macro for
you........really very cool. Not "everything" can be done that way, but a
lot of stuff can......it's a good start.
and of course you can come back to the newsgroups if you run into trouble.

hth
Vaya con Dios,
Chuck, CABGx3

 

[John Calder]

Kevin

Email me your email address and I will send you a simple spreadsheet to do
what I think you are after. It uses a lookup table, data filter and a couple
of data validation lists.


John
(e-mail address removed)

 

[Kevin]

John,
Thank you for your offer. I have sent you my email. I came back today to
copy all of the suggestions and to start trying to build this. I just saw
your post today for the first time, so sorry I didn't get back to you sooner.
If anyone else wants to offer suggestions, I would be more then happy to
give my email and YES I need all the help I can get. LOL.
Thanks again
Kevin