Jeo, the tone of your question tells me you are relatively new to Access. I can help you with a mind-set issue that you need to know when designing ANYTHING SUBSTANTIAL in Access (any version of same).
{Put on ol' perfessor hat with the square, flat top and less-than-new gold tassel}
When you are designing something for a business, you need to start from the detailed business work-flow model. Newman's advice is therefore spot-on regarding a starting point. I always tell people to use a white-board and sticky notes. Or a smooth wall that isn't cloth-covered.
What you do is identify business entities. Some things already named that qualify for a set of notes are your projects & your people. Let's take the case where you have many projects and many people, but there are never enough to go around, so some folks work more than one project, and some projects are big enough to need more than one person assigned. Here is how you do this on paper.
You write down (on the sticky note) all the data you have about a person that does not involve any other business entity. Ditto for projects. Arrange them on the wall in columns. OK, two tables, each distinct note is a record in one of the tables.
Now you find that you need to identify the project that a person works on. But my rule said to not write that on either the person note or project note because that would be mixing the purpose of the note. So what you do is put some smaller sticky notes in another column between the two original columns, with the project name and the person name for each assignment.
Voila - you have just "discovered" the Assignment table. And it is an example of what we often call an intersection - the set of projects intersects the set of people through this intermediate table. Now, in techie terms, what it does is allow you to implement a many-to-many relationship indirectly, which you need to do in this case 'cause Access only supports one-to-one or one-to-many. And the bit about "keeping a table pure" is actually just "normalization." You can look up that term in your help files to get a more formal definition.
Let's take a look at something else that might happen. Since you are doing projects with multiple people and you probably have some sort of charge system to account for the costs, you need to know how much time each person charged to a project. The Assignment table can help you identify whether the charge was legal, but here is where another "purity" issue might arise. (Another type of normalization.)
If you have to write anything on a sticky AFTER YOU HAVE PLACED IT, you might be adulterating the purity of the table. TIME is something that should not appear in a person record or a project record (other than perhaps "start of employment" for a person or "contract awarded" for a project.) Remember, even though you don't always think of it this way, TIME is a separate resource, 'cause you make your customers pay for it.
If you have a time-based detail for someone or something, you need ANOTHER table that depends on those things plus TIME. So to track time spent by a person on a project you might consider a table that lists the person, the project, the time of the charge (you can pick start or end, it is up to you), and the number of hours - and this would be SEPARATE from the simple assignment table 'cause that doesn't involve time at all. (You could use the assignment table to validate a charge, but not to track it.)
Now, go through and identify other business entities, resources, etc. Keep them pure. Where an intersection occurs, consider another intersection table. The assignment was two-way, but there is no reason you couldn't have three-way, four-way, or n-way (other than a limit of I think 10 indexes for sorting purposes.) In general, when you have an intersection table that has data from other tables, you have a many (intersections) to one (resource) type relationship. So this is how you often define relationships.
If you have a relationship identified in this way, Access works better when you define the "one" side of the relationship to have a primary (i.e. unique) key to identify it. So for a person, you might have an employee ID number, but for a project, you would have a project/contract number as appropriate to your business. This is how you determine what to use as keys. Any time you have an intersection and it includes data from another table, there is a good chance that the data from the other table should be in a key.
OK, here is where things get trickier. Now define actions to be performed on those items you have identified. Don't be surprised if you find that to perform the action and to still keep the tables pure, you have to define another table somewhere. It happens, and when it does, it is a GOOD thing. Because it means you have identified another process within your business dataflow model.
These actions will become queries, reports, forms, or - sometimes - VBA modules required to implement the Access equivalent function.
This is just an overview of the project analysis phase of an Access implementation. It is one man's view on how to approach this sort of thing in a systematic way. I hope it helps.
{taking off ol' perfessor hat}
Good luck!
I’m trying to figure out the best way to implement a database.
, so thank you!
