Database for Band Management

[Raiderduck]

Hey guys. Lots of general questions here - mods please move/delete if not allowed.


My background - consider myself to be able to use excel at an advanced level and have used it for things such as sports modelling and prediction, record keeping, band admin (until now) and a few other things. Only recently have become a bit frustrated on the band side of things and that coincides with pushing myself to learn MS access. I've watched a few youtube tutorials and courses etc and understand most of the basic (at least) concepts.
I'll describe below what I do, and what I want to be able to do in access (if its possible!).
I'm in, and manage a band. Its my full time job, I do all the consulting with clients and venues, and manage all the musicians. We play weddings, functions, public gigs etc all around my hometown and beyond. When I say "Band", we are not the same 4 guys that rock up to each gig. Its more a collective of 12-16 or so "regulars" who all play different instruments etc. So, when I chat to a bride who wants a Sax player, I say no problem and I pencil in one of our sax players to join the lineup. Having this arrangement is flexible, it allows us to play more than once on the same night - in the busy season we could be doing 3-4 weddings on the same night - all different lineups but playing under the same name. As you can imagine, negotiating with clients, lining up the correct musicians can be a headache in the busy season. I've used excel up until now, but want to make an interactive database in Access. So, here are my thoughts on what I want to do.
Have a table with every detail for each gig (Say a wedding because that's what we mostly play) on there. So the columns in the table will be something like ...
Date Event (wedding/function/public gig etc) Client/brides name Location Venue Total Fee to band Package ordered by bride (size of band) Musician 1 name Musician 1 Fee $ Musician 2 name Musician 2 Fee $ Musician 3 name Musician 3 Fee $ (and so on depending on the package ordered) Remainder Fee $ (money left over after paying musicians) Ceremony details - all requested songs, timings etc All reception details - songs, timings, do any guests want to sing etc Number of guests Dresscode Booking status Payment status
So - thats the main table more or less. For each new gig that comes in, I'd make a new entry.
I would like 2 forms for this. 1 - to be filled out by me, which contains the "admin" fields - All musicians and their separate fees (singer/guitarist might get paid more than sax player for example), total fee charged client, booking status, payment status etc.
2 - To be filled out by client. This is where they enter what song they want for bridal dance, what they want the band to wear, how many members they want in the band etc.


I would like the clients form to be interactive and them be able to change all their preferences etc, and it show up in my table.
Questions so far - I obviously need to make it web based, and look pretty for the clients. How do I do this? Do I need to give each client a login? Or would I just send an individual link? Very hazy on this side of things.
From here, I also want to give each musician a page/login so they can see certain details. Lets say I've got 10 bookings coming up, and I've got my second guitarist, lets call him Paul, playing 6 out of those 10 gigs. I want paul to be able to open up a page, and only see details that he needs to. So, 6 gigs will appear in his gig list, and for each of those gigs he can see what he needs to wear, where it is, what songs he needs to play, who he is playing with, how much he is getting paid etc. What he doesnt need to see is the gigs i've NOT assigned him to, how much the other musicians are getting paid, and you get the idea.
I'd like to have an individual page for each of my musicians where they can see the details they need to see, and an individual page/form for each of my clients for them to input their own details.
And that's pretty much it. If anyone's got any advice, thoughts or could help out with this it would be awesome, I've got a basic table/forms set up but definitely need assistance from here, especially in creating the web side of it.
Other things I'd like to incorporate in the future... - Once a new gig comes in, I can pencil in my "ideal" lineup, and those selected musicians would get an email/text alert where its got the basic details and they can either accept or decline the gig. - After a gig is completed, have an auto-invoice sent to me from each musician for the agreed amount.
Thanks in advance!

[JamesDeckert]

Your musician names and fee should be in its own table. Then each event can refer to the musicians in a one to many relationship. This will allow you to have any number of musicians and not make a musician 1, musician 2, etc field, which will be a nightmare when querying.

Having two forms which look at different fields in the table is not a problem.

Access 2013 can build web forms, but I haven't done this so hopefully someone else will respond to this question. Maybe you could give each person a unique number and that is their id code entered into the table. Their form opens in add mode so they won't see others data but can only enter new data.

Each musician opening a form/report with only their own details is doable, just need to query/filter their info before opening the form/report.

Do you already have a website hosted? And is it running Windows?

[CJ_London]

duplicate post here

http://www.access-programmers.co.uk/...=1#post1446501

[rpeare]

There are a couple of ways you could do this:

1. You can create a 'web database' with (I think) 2010 or higher, but it also requires you to have a sharepoint site, this is extremely limiting if you plan to do any coding though, web databases will only allow macros and not any vba code.
2. Create forms (Access 2007 or higher) that you mail out (I've never done this myself) but they are specifically designed for things like event management (see https://support.office.com/en-za/art...rs=en-ZA&ad=ZA). If you go this route DO NOT be constrained by trying to fit everything on a single form. It will end up causing you trouble in your table design. You should be able to harvest the data from the forms without having to do data entry.

The way you have described your structure (or what you think it should be) is not a great design. Read some articles on data normalization, what you likely want is something like:

tblInstruments
Inst_ID  Inst_Name
1        Guitar
2        Piano
3        Saxophone

tblBandRoster
BR_ID  Band_FN  Band_LN  Band_Rate  Inst_ID
1      Mickey   Mouse    100        1
2      Donald   Duck     200        3
3      Minnie   Mouse    150        1
4      Tinker   Belle    400        2

tblEventType
ET_ID  ET_Desc
1      Wedding
2      Bar Mitzvah
3      Graduation
4      Reception

tblEvent  (note your 'attire type' would probably be in this table, if you have payment plans for your events your payment information should be stored on a different table, if it's 'all or nothing' this table would be fine)
EV_ID  ET_ID  ET_Date  ET_Address    ET_City       ET_State  ET_Zip  ET_StartTime  ET_EndTime  ET_Notes ---> other event specific data points
1      1      1/1/2015 1313 1st St.  Mobile        AL        44444   13:00         18:00       ABBA themed wedding
2      1      1/1/2015 48 Water St.  Tuscaloosa    AL        44445   16:00         20:00       Crimson Tide themed wedding

tblEventBand  (this table would contain information specific to the band member for a specific event)
EB_ID  EV_ID  BR_ID -----> other event and band member specific data points 
1      1      4
2      1      2
3      2      3 
4      2      1

tblSongList
SL_ID  SL_Name ---> other song specific information
1      Dancing Queen
2      Fernando
3      Does Mother Know that you're out
4      Sweet Home Alabama 

tblEventSongs  (this table would contain information specific to requested songs for requested events
ES_ID  EV_ID  SL_ID  Req_Time  Song_Order
1      1      1      13:00     1
2      1      3      13:05     2
3      2      4      16:00     1
4      2      4      20:00

You get the idea, think of your database being modular, breaking down the information into chunks that make sense and store the least amount of data possible with the flexibility to answer all your questions.

[JamesDeckert]

My suggestion of a one to many relationship should have been a many to many. Each event can have many musicians and each musician can be booked for several events. Thus you'll need a junction table between the event table and the musician table which contains the primary key from both tables along with any other fields which need to be added.

[rpeare]

That's exactly what tblEventSongs and tblEventBand are doing, they are junction tables.....