I'm having a go at building my 'first' database, I've done really basic single table (pretty much flat record) ones before years ago in FileMaker but never anything using multiple tables, forms and reports and have never used Access before. So in at the deep end and all that!
The idea is to replace individual spreadsheets (job sheets) we have that record the history and progress of a production through our recording studios. The end goal is to have a database where the user can look up a job via title or job number and then add the relevant data to various sections. I am however a little unsure on one aspect of how to structure one of the tables where we capture the daily progress of the recording.
In one area of the spreadsheet we have 8 columns where each row records a jobs progress, so for example:
Column B = Session Number
Column C = Staff Name
Column D = Date of Session
Column E = Time of Session (AM/PM/Evening)
Column F = Recorded Up To Page #
Column G = Time on Timeline
Column H = Minutes Recorded in Session
Column I = Number of Pages Recorded
What isn't always known is how many sessions will be needed and therefore how many rows. In Excel that's not really a problem as you just add another row but I'm not sure of the best way to handle this in Access.
Can you have a function on a form to add new fields to a table? For example the form has enough fields for say 5 sessions but if more are needed you click on a 'Add More' button and it automatically adds all the required fields for a 6th session and adds these to the table.
Or would it be better to have a session table with the above 8 fields and each row is then a different session? I can see how that works for a single record but how would that work for multiple records, how would you identify that say ID's 1-10 are for x production and ID's 11-15 are for y production etc?
Definitely bitten off more than I can chew but I like a challenge