I've read through the Access Basic book that Crystal recommended, thanks again for that. ... Many thanks, and sorry for the long text.
you're welcome,
Neil, happy it helped you. Your post wasn't so long ...
I would assume that musicians may play more than one instrument, and you might want to prioritize their parts too
> "For each new engagement, should I enter the new engagement into a new table each time?"
no. I see many tables. Better to have more tables and less fields in each. At first glance, here is some of what I see:
Events (what
Gasman called Engagements -- but Events is shorter)
EventID, AutoNumber, PK
VenueID, Long Integer, Default Value=Null, FK to Venues
EventName, Short Text, Size=50? (maybe longer?), name of event
DateEv, date/time, Date of event with possibly starting time too
Dur, Long Integer, Duration in Minutes
EventPlayers
EvPlayID, AutoNumber, PK
EventID, Long Integer, Default Value=Null, FK to Events
PlayerID, Long Integer, Default Value=Null, FK to Players
InstrID, Long Integer, Default Value=Null, FK to Instruments
PartNbr, Integer, DefaultValue=Null, 1=First chair, 2=Second chair, etc
to make it quicker to set up records for EventPlayers, you might want to have a tables for Sets and SetPlayers so you can define, for instance, string quartet set1, string quartet set2, full orchestra set, and so on, with the players and roles for each
actually though, to make this even more generic, you might have a table called:
EventRoles
with a RoleID foreign key to a Roles table
that would cover the conductor and other supporting roles as well
Venues
VenueID, AutoNumber, PK
VenueName, ShortText
Players
PlayerID, AutoNumber, PK
InstrID, Long Integer, Default Value=Null, FK to Instruments -- main instrument
PlayerCode, Short Text, Size=20? -- this will have a Unique Index
FirstNameP, short Text, Size=50, first name of player (which might include a middle name too, if they go by it)
LastNameP, short Text, Size=50, last name of player
PlayerInstruments
PlayInstrID, autonumber, PK
PlayerID, Long Integer, Default Value=Null, FK to Players
InstrID, Long Integer, Default Value=Null, FK to Instruments
PartNbr, Integer, DefaultValue=Null, Usual Part Number, 1=First chair, 2=Second chair, etc
Ordr, integer, (order is a reserved word) - order this instrument is played. Maybe main=0
Sectns (both Section and Sections are reserved words)
SectID, autoNumber, PK
SectName, Short Text, Size=20, Section Name (string, wind, percussion) - Unique Index
SectID_, Long Integer, Default Value=Null, FK to Sectns.SectID, for rolling up
This table is hierarchical. Example records
1, String
2, Wind
3, Persussion
4, Woodwind, 2
5, Brass, 2
Instruments
InstrID, AutoNumber, PK
SectID, Long Integer, Default Value=Null, FK to Sectns
InstrName, Short Text
Banks
BankID, AutoNumber, PK
BankName, Short Text, Size=50
ABAnum, Short Text, Size=15 (in US, length=9), ABA routing transit number
CityB, Short Text, Size=30, Bank City
StB, Short Text, Size=2, Bank State abbreviation
ZipB, Short Text, Size=5, Bank Zip Code (maybe longer if you have international addresses)
Zip2B, Short Text, Size=4, Bank Zip Code extension (maybe longer if you have international addresses)
BankBranches
BBranchID, AutoNumber, PK
BankID, Long Integer, Default Value=Null, FK to Banks
CityBB, Short Text, Size=30, Bank Branch City
StBB, Short Text, Size=2, Bank Branch State abbreviation
ZipBB, Short Text, Size=5, Bank Branch Zip Code (maybe longer if you have international addresses)
Zip2BB, Short Text, Size=4, Bank Branch Zip Code extension (maybe longer if you have international addresses)
States
St, Short Text, Size=2, PK, State abbreviation (State is a reserved word)
StName, Short Text, Size=30, State Name
Accounts
AccountID, AutoNumber, PK
PlayerID, Long Integer, Default Value=Null, FK to Players
BankID, Long Integer, Default Value=Null, FK to Banks
BBranchID, Long Integer, Default Value=Null, FK to BankBranches - may not be filled
AcctTypeID, Long Integer, Default Value=Null, FK to AcctTypes
AcctNum, Short Text, Size=20? (usually not >12), Account Number
AcctName, Short Text, Size=50, Name on Account
AcctTypes
AcctTypeID, AutoNumber, PK
AcctType, Short Text,
PK = Primary Key
FK = foreign Key
Be sure not to use any reserved words for names. Always start names with a letter, not a number or special character. Don't use any special characters in names except underscore (_) and don't use spaces!
Problem names and reserved words in Access, by Allen Browne
http://allenbrowne.com/AppIssueBadWord.html
I could go on and on -- the more I read your message, the more tables I see. If I looked at your data, I would see even more! Design will take up to 30% of the time for your project.
Data structure (tables, fields, relationships) is the most important thing to get right. If you do this well, the rest is downhill
Coming from Excel, you are really going to need to change your mindset, and perspective, to design a good structure.
As for people -- names and contact information ... I typically store all contacts (humans, companies, organizations, etc) in one place, not have names scattered all over. One person may play several roles -- they may be a musician as well as a conductor upon ocassion, and fill in with other roles. So the contacts table is just generic information about people that is linked to tables with related information such as phone numbers, email addresses, and addresses. CID is the primary key in the Contacts table.
In a Players table, I would also store CID as a FK -- so that eliminates the need to put any fields there that the generic contact structures already store.
Here is a "simple" contact management database application in Access that you can download and play around with. Everything is open so you can also look at its design, and see how things were done. There's lots of sample data in there. I also made an easy way to delete the sample data, although the web page may not mention that ~ there's a lot it doesn't mention! Best is to just go download it and try it for yourself. Click on everything and see what it does. Since the data in there is made up, you can't hurt anything
MyContacts
https://www.msaccessgurus.com/tool/MyContacts.htm