Well, a thing worth doing is worth doing well, including doing some preliminary design work. I'm going to suggest something heretical to you. (At least some of the members might find it so...)
Start with Excel as your design medium. Imagine that you were going to build a spreadsheet for each person, each appointment, each consultant, each test, each other thing that you think would be represented by a separate file folder in this paper method. Think about what columns you would need to keep. In this design, you would think 'flat-file' issues. If you see something that you need to track, odds are that it will either be another column in one (or more) of your worksheets or it will be a whole new worksheet with a bunch of data on its own.
This approach won't help you with the relationships, but it will help you organize your thoughts on what data you need to keep with each spreadsheet. And it is visual enough to let you get started even as a novice. Sometimes you just have to see it on the screen for the ideas to jell.
Now, once you have decided what each sheet in the workbook should contain, you can begin building tables. You should have one table for every sheet you built. HOWEVER, this is where Access's power comes into play.
In any sheet where you have data that also appears on another sheet, you have identified a CANDIDATE for establishing a relationship. Not a certain relationship - but a candidate for one. If you find, for instance, that you have the need to keep case workers with the same client, you might have an assignment sheet that is different than the appointment sheet. If you think you would have kept it on paper, make another sheet for it just to capture the information on requirements.
Now let's look at your appointment sheet. The name and call-back phone number of the client would be there for the convenience of the appointment scheduling person. But you would also have that information in the client's record. Rather than duplicate the effort, you would just put the client's record number in the appointment and let Access call up the name and phone number for you. This is done with a two-table query that involves a JOIN (relationship) in which the element in common would be the client ID number. Instead of duplicating the client data in the appointment table, just point to the client.
You will probably have a consultant for which you need both a name and a phone number so you can verify the appointment as a reminder. Again, if the consultant has a code number in a separate record, you can let Access find that for you. Use another number and just POINT to the required data.
Now, as to being a novice. We all were novices at one time or another. The key with Access is to learn by doing as well as by reading. So prepare yourself by building a few databases. Look at the elements of the Northwind database that is one of the samples. It will show you ways of linking tables, building reports from multi-table queries, etc.
Now, an issue that vexes some folks is choice of data types. As a novice, you really only need to consider four types, or at most five. The rest can be ignored for the moment.
You will need dates and/or times - use the Date data type.
You will need code numbers, record numbers, and counting numbers - use the Long (Integer) data type.
You will need names, addresses, and phone numbers with punctuation in them - use Text data type.
You MIGHT need monetary data - use Currency data type.
You might have check boxes for something being done or not done, for an option being taken or not taken - use Yes/No data type. (If you have more than just yes/no choices, then this is really just a LONG in disguise.)
It is POSSIBLE that you might need to compute or store ratios of some sort. If so, use the DOUBLE (Real) data type for consistency.
Now, be aware that I'm suggesting a course of action that MIGHT waste some space in your records. On the other hand, it might not. And when you get more comfortable with data types, you can go back to change things without too much impact.
You can also find some tutorial books on Access. I strongly advise that as a novice you should make the investment. You will also find all too quickly that you have outgrown the books, which is a GOOD thing. In closing on this sub-topic, I will suggest that before you buy a particular book, thumb through it to see whether the explanations they give make sense to you. My wife teaches Office software and says that some of the books out there are trash, others are gold - but her students often disagree with her on which is which. So what matters is that the books makes sense TO YOU.
