Looking for help creating a Band Member Payment Database (1 Viewer)

Peege

New member
Local time
Today, 17:00
Joined
Jul 26, 2017
Messages
2
Hi there.

I'm pretty good with Excel, but I'm a relative newbie when it comes to Access!

I'm in a band and I'm responsible for sorting out the payments to the band members. I've been using an Excel spreadsheet to calculate what's due, but I'm trying to transfer the process to Access.

Where I'm stuck, and it may not even be possible, is this...

I have the main table called Gigs in which all the calculations are done. No problem with creating expressions - pretty much similar to Excel. The relevant field names to my problem are: Date, Venue, Fee, Mileage Member 1, Mileage Member 2 and Mileage Member 3.

I also have a table (with a form to input the data) called Venues, in which I have the field names Venue, Mileage Member 1, Mileage Member 2 and Mileage Member 3.

What I'd like to be able to do is create a form to input data to Gigs where all I have to input is Date, Venue (from a combo box linked to the Venues table) and Fee, but then have the mileages for each member and for each gig automatically input using the data in the Venues table.

I've tried all kinds of things - I assumed that using lookup would solve it - but I've had zero success.

Can this be done or am I wasting my time?
 

Ranman256

Well-known member
Local time
Today, 13:00
Joined
Apr 9, 2015
Messages
4,339
Access IS the answer for this. But it has a learning curve for Excel users. New methods , etc.
youll need:
a tVenue table
VenID (autonum)
VenDate
VenPlace
Venmiles
etc

tMember table (people info)
MemID (autonum)
name,etc

tVenueMembers (all those who attended the venue)
VenID
MemID
Mileage
Fee

The tVenueMembers table is a child of the tVenue.
In database youll need to learn parent/child relationships and a lot of other concepts.
 

isladogs

MVP / VIP
Local time
Today, 17:00
Joined
Jan 14, 2017
Messages
18,186
Can this be done or am I wasting my time?

Yes it can definitely be done in Access - the question is whether its more suited to Excel or Access.

My first bit of advice is to stop thinking in terms of how you would do things in Excel. Spreadsheets are very different to databases

I have the main table called Gigs in which all the calculations are done. No problem with creating expressions - pretty much similar to Excel. The relevant field names to my problem are: Date, Venue, Fee, Mileage Member 1, Mileage Member 2 and Mileage Member 3.

Whilst you can have calculated fields in Access, the standard approach is to calculate values using a query.
Tables are used to store data that will not normally change

As an example, DateOfBirth goes in a table but use a query to calculate Age

I also have a table (with a form to input the data) called Venues, in which I have the field names Venue, Mileage Member 1, Mileage Member 2 and Mileage Member 3.

Also you shouldn't have the same fields in 2 different tables
i.e. Mileage Member 1 etc in both Venues & Gigs
Store data in one table and use a query to retrieve that data as required from linked tables

What I'd like to be able to do is create a form to input data to Gigs where all I have to input is Date, Venue (from a combo box linked to the Venues table) and Fee, but then have the mileages for each member and for each gig automatically input using the data in the Venues table.

I've tried all kinds of things - I assumed that using lookup would solve it - but I've had zero success.

That sounds perfectly feasible in principle
Suggest you consider the points I've raised above, then create tables & relationships between them. Then post the following:
1. Table details - fields & datatypes
2. Relationships window as screenshot

NOTE: as you have less than 10 posts, you'll need to zip any files you upload

EDIT: posted this before seeing Ranman's response. We're basically saying the same thing
 
Last edited:

Peege

New member
Local time
Today, 17:00
Joined
Jul 26, 2017
Messages
2
Thanks Ranman256 and Ridders for your replies.

I think my initial reaction is "Oh dear - this is far more complicated than I expected". :eek:

As you say, there is a bit of learning curve here, and I'm not sure the benefits justify the time needed to figure it out.

I may just have to stick to Excel...
 

isladogs

MVP / VIP
Local time
Today, 17:00
Joined
Jan 14, 2017
Messages
18,186
Oh dear indeed - sorry if we've put you off....

Think of it this way:
There is a learning curve but the rewards are well worth the effort involved
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:00
Joined
Feb 28, 2001
Messages
26,999
Peege -

First, as an old keyboard man who played many gigs in places as socially separated as a Lion's Club New Year's Eve party, clubs on Bourbon Street and Tulane Avenue in New Orleans, and a bar that was a money-laundering front for the mob's Gulf Coast brothels, I understand that you might need to keep track of many things if you get enough business. (P.S. - we got away from the mob bar as soon as we could and got rid of the business manager who got us that booking, but a contract is a contract...)

But my knowledge of Access tells me you need to do a little bit of reading as part of your learning curve. Read up on these topics.

Database normalization - for general web searchs, but if you use this forum's Search function, you only need normalization. This is the topic that will also lead you to parent/child table structures. You will also need to learn about Junction Tables because you MIGHT need them depending on what exactly you want to do. Look up stuff for Queries as a means of computation.

As a matter of advice, if you really wanted to do this job in Access (and it WILL be able to handle the job), you need to know a couple of "Old Programmer's Rules" - which I am eminently qualified to give you, since I'm an old programmer.

Rule #1: If you can't do it on paper, you can't do it in Access. That means you have to PLAN OUT your goals and design the steps you need to follow in order to achieve the desired results.

Rule #2: Access won't tell you anything you didn't tell it first. That means that if you know what you want to see coming out of your database, you need to assure that Access has a way to either get that information from an input form OR it needs a way to compute that information from data taken through an input form. This CAN sometimes require you to work backwards from desired output to verify that there is an input source for that output.

One of the toughest mental leaps you will need is the ability to separate out things to cluster them in tables where they belong on the "like goes with like" concept. Here's a simplified way to look at it.

OK, you've got members. Each one plays one or more instruments. Do you CARE which instruments they play? If not, you can just make ONE field that lets you list what they play and be done with it. What else do you know? Well, their names, addresses, and contact phone numbers for some things. But ... you mentioned mileage to the gigs.

If you guys were like the group I was with some 50 years ago, you have an "assembly point" where you get together at a particular member's house, piled your stuff in the back of the station wagon or mini-van or small trailer, and then rode to the venue. So, how do you want to compute the mileage?

Well, we were small enough as a group to ignore it, but... If you have a fixed assembly point, then the distance from each person's residence to the assembly point is a fixed attribute of the members and this each member who made it to the assembly point in their own vehicle gets an incremental fee. That fee might be an attribute of each member independent of the gig. (Or you might CHOOSE to say that the home-to-assembly point fee is not reimbursed. Depends on how much it will turn out to be.)

But after that, the distance from the assembly point to the venue is the same for everyone who was driving - but not for anyone who was merely riding. So you need a table to help you remember who DROVE for that gig, and it is an attribute of the gig, not the member.

Then you have to decide who PLAYED at the gig whether they drove or rode, so you might need a second table OR a marker in one Gig table to note who played and thus would be eligible for a cut of the booking fee.

With that in mind, I might choose:

tblMember
-MemberID - AN ARBITRARY BUT UNIQUE NUMBER used for query linking purposes
-name
-address
-primary contact phone
-instrument(s)
-miles to assembly point (this IS optional if you decide to not bother)

tblGig
-GigID - AN ARBITRARY BUT UNIQUE ID number used for query linking
-name of venue
-address of venue
-venue point of contact
-venue contact phone
-mileage from assembly point
-dollar value of gig
-start date/time of gig
-end date/time of gig (ALTERNATIVE: Length of gig)

tblGigMember
-MemberID - of member who played this gig
-GigID - of gig that at least some members played
-MemberDrove - Yes/No field, YES if member drove from assembly point to gig
-MemberOwed - used to accumulate fees for this member for this gig

Then you can use queries that will JOIN the member and gig information into a single set of records that you could use to compute distribution of fees. The trick will be that if you want to reimburse for mileage, you do that first for those members who get mileage and THEN you distribute what is left over to those who played the gig.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 17:00
Joined
Sep 12, 2006
Messages
15,613
Doc Man?

Was that like the Double Deuce in Road House - one of my favorite movies. "I thought you'd be bigger."


With regard to databases/spreadsheets

I store historic details of some investments in a spreadsheet. I track the value over time by adding extra rows showing the total value. I only hold the current price of each investment

I know what I need to do is put it all in a database, but it does take time to convert a sloppy spreadsheet into a streamlined database. A few days, and I will get it round to it ... sometime.

One of the main differences is that with a database you hardly ever - probably never -see all your data. You just enter the date or date range you want, and you see the appropriate results. What you don't do is see and manipulate all the data as a whole. Therefore you have to be absolutely sure that your "business rules" are working correctly, and that the filtered views you see do include everything you want to see.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:00
Joined
Feb 28, 2001
Messages
26,999
Dave:

The "laundry" bar was a tacky place in downtown New Orleans with a downstairs piano bar and an upstairs stage. We had the stage. It was all in satin-finish cloth-like gold wallpaper with red felt-like ornate designs. We always wondered how the joint stayed in business when hardly anyone would be there most nights. But of course, it's purpose wasn't to MAKE money. They used it to LAUNDER the money from places for which they could not declare an income.

Peege - Dave (Gemma) is absolutely right. My rule #1 is the first step in HOW you got the point of "absolutely sure that your "business rules" are working correctly."
 

Users who are viewing this thread

Top Bottom