Help a beginner with table relationships?

[Xixor]

Hi, all. I'm new to the forums and not more than 10 hours into my very first experience with Access. I'm designing a database for my team at work and am hoping to keep it simple. I have run into some confusion on setting up the table relationships and am hoping you may be able to guide me in the right direction.

Table 1 - University faculty.
Table 2 - Faculty grant submissions. One faculty member can have many grant submissions and most will not become funded/active.
Table 3 - Active grants. All active grants will be precipitated by exactly one submission.



The faculty table I have I think entirely sorted out, with basic info on each faculty member.

The submissions table has a bunch of info on the submission, like what funding entity the submission went to, what the due date was, etc. Each submission also needs to have a "principal investigator", who is basically one of the faculty members from the faculty table. I think I have that setup correctly with a one to many relationship between the primary key (employee ID) of the faculty table and the principal investigator field in the submissions table.

Where I have run out of steam is setting up the Active grants table. Each active grant is related to only one submission, and should contain some info found in the submissions table (like the funding entity). It also should contain the principal investigator's name. Is there a straightforward way to link the submissions table to the active table and have it automatically pull the relevant info into the active table or is that a gross oversimplification of the process? Is it even necessary to re-create fields in the active table that already exist in the submissions table, given that I am guessing there is a way to link two records together and then use a query to display data from each table?

One additional wrinkle/caveat - 99% of the time each active grant will have a principal investigator who is the same person who submitted the grant, but sometimes down the line the investigator on the grant will change, so in that sense I don't know if it would be a good idea to make principal investigator field in the active table linked to the investigator field in the corresponding entry on the submissions table. Maybe linking it to the faculty table would be a better idea instead?

There is so much more that I don't know that I don't know, so to speak, but this is hopefully a start at describing my project. Thanks!

[ssanfu]

Why is faculty member linked to investigator?
I would expect it to look like



If the investigator will change and you need to track investigator changes, I would use something like


One faculty member can have many submissions
one submission can have many investigators.

[Xixor]

Thanks for your reply ssanfu! Two follow-up questions:

1) I had completely not thought of the solution I think you are describing with the "grant_active" field. Are you suggesting that this field be a click box (or yes/no dropdown), that when clicked would then indicate that submission turned into an active grant? (and then would unlock some other fields to be filled in by the user in the form to flesh-out the details of the active grant) Specifically, the user would need to update the active grant info with "date awarded", "amount awarded" etc... Is this a more streamlined way of approaching this entire project than having a completely separate table for active grants?

2) "why is faculty member linked to investigator?" Because the investigator of a grant submission needs to be one of the faculty members from the faculty table. Originally I had the faculty employee ID field duplicated in the submissions table like your diagram and had them linked primary-foreign key, but I ran into difficulty because I need the users on a form (when entering info into the submissions table) to select the faculty member's name for a submission, and when I created a combo box to do this, it just displayed employee ID numbers and not the faculty names. The way I worked around it was to setup a combo box via the "wizard", and link to the faculty name in the faculty table, and it seems to have worked...

I feel so lost with all of this, but it is super interesting to me and I feel like I am learning as I go along!

[orange]

Xixor,

Steve has given you some suggestions. But I'm going to ask you to step back and describe the "business process" in simple, plain English. Try to remove the reference to grant table and/or submissions table - I'm not saying it is wrong, I'm attempting to say you may be prejudging the facts.
You have some attributes in your tables that don't seem to be identified in your description and some things in description that aren't referenced in your proposed tables. Things like Funding entity, prime Sponsor, prime Investigator, faculty member and faculty employee are mentioned by there is a certain lack of clarity as to their definition and role in your "business process(es)".

I recommend you work through this tutorial from RogersAccessLibrary and experience the process of setting up tables/entities and relationships. What you learn can be used with any database, but you have to spend 30-45 minutes working through the tutorial.
There are several articles in various formats in the Database Planning and Design link in my signature.
Good luck with your project.

[Xixor]

Thanks, orange. I will definitely work through that tutorial as soon as I can. I have also been watching as many videos online as I can.

In simple barebones english, my team is responsible for the administrative and financial matters in a university department with externally-funded research dollars. Faculty submit grant applications to external funders, some of those applications are ultimately funded. We need a comprehensive way of keeping track of the details of these submissions and the grant money that comes in as a result.

The end goal is simply to create reports quickly and efficiently when someone asks a question like "how much money did we receive from the National Institutes of Health this year?", or "How many grant submissions did [faculty name] put in this year", or "what submissions do we have due coming up in [month]". It would be a bonus if we could also see more granular/esoteric things like "what is [faculty name] success rate in applying to [funder name] during the month of [month]?", or "how many active grants does [department] have from [funder]?"

Right now, my team has a few messy excel spreadsheets that everyone haphazardly types data into when they are working on an outgoing submission or on an active grant. When the dean comes to us and asks one of the above questions, it normally takes one or more people several hours to extract/manipulate the data from the excel spreadsheets into an accurate answer. I feel like getting a simple access database up and running would enable us to answer these types of questions instantaneously as opposed to the manual human analysis that is required with our system right now.

Any guidance on the overarching framework I should be aiming for with the tables and such would be immensely helpful!

[orange]

Here are the business facts for a sample data model Tracking Grants for Research from Barry Williams' site.
And here is the associated data model.
The model and facts are "typical" and show the entities and relationships often found in "this business". The scope and detail may not apply to your situation. So add, remove and/or modify to fit your needs. The facts and model ate meant to help you with a starting point.
I still recommend that you work through the tutorial suggested earlier. Then with this new model and facts, work through the process with your own data.
Good luck.

[ssanfu]

More reading??

After working through the tutorials (or at the same time), here is a 5 part blog on Normalization

What is Normalization? (5 parts)
=======================
What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html


Entity-Relationship Diagramming (4 parts)
==============================
Entity-Relationship Diagramming: Part I http://rogersaccessblog.blogspot.com...ng-part-i.html


The Normal Forms (5 parts)
=========================
The Normal Forms: Introduction http://rogersaccessblog.blogspot.com...roduction.html




"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.


So design your tables using paper, whiteboard, a window, etc BEFORE jumping into Access. It will save you many headaches....


A comment on naming:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.


I have more if you want it....

[Xixor]

Thanks, ssanfu and orange. I am trying to work through the relationship tutorials now with the entities and attributes of my particular situation...

[orange]

Relationships are how business facts/business rules constrain "things" in your database.
Good luck with your project.