Creating a grant database (1 Viewer)

[Maria83]

Hi Everyone,

I could really use some advice. I'm interning for a non-profit and am creating a database to hold all their grant competition data. This is the kind of info i'm dealing with:

5 different grants (GrantName) (there's repetition in this column)
Several investigators (ApplicantName) (there's repetition in this column)
Several Institutes (InstitutionName) (there's repetition in this column)
The Province,City where the institution is located (there's repetition in this column)
Grant numbers (these are unique, but only given to successful applicants)
I have a column indicating successful/unsuccessful applicants (s/u)
I also have financial data linked to the successful applications (funded amounts)

I just don't know how to build the database and what relationships I should establish, which relationships should I maintain referential integrity.

Its difficult for me because the all the applicants don't have unique identifiers (primary keys).

I've tried having a sheet for all applicants (both successful and unsuccessful) that collects all the data above and then a second sheet with only successful applicants, but then I have to replicate the same data twice in two separate sheets.

I wanted to create the second successful applicant sheet because then the GrantNumber column could be used as the primary key, and I could then create a relationship with that sheet and the financial information sheet (the two sheets will have the same number of rows). But I don't know how to link the successful applicant sheet to the all applicant sheet (what relationship should I use?)

If anyone has any suggestions as to how I should build this database, please let me know, I'd really appreciate it.

Thanks for the help,
Maria

 

[burrina]

I would suggest you take a look at this site, it may help.

http://www.databaseanswers.org/data_models/

 

[plog]

My advice is to read up on normalization: http://en.wikipedia.org/wiki/Database_normalization

Then I'd start small and build your tables. I'd start with the definition tables (investigators, institutes, etc.) then I'd start on the more complex tables (grants).

Give it a shot, then post back here a screenshot of relationship screen.

 

[RainLover]

If you used AutoNumber as the Primary Key for each and every table then this would solve the first part of your design problem.

Also have a read of the links that have been recommended to you.

Now this will keep you busy for a day or two. Databases can take a lot of effort and time to write to get to the point of satisfying your requirements.

But don't worry we are here to help.

 

[Steve R.]

Its difficult for me because the all the applicants don't have unique identifiers (primary keys).

The primary key is used to maintain the relationship of the data between various tables. As such, do not use the primary key in the applicant table as an "account number" for each applicant. You will need a separate field that contains a unique value as the identifier for each applicant.

Grant numbers (these are unique, but only given to successful applicants)

This depends on how compulsive one is concerning the data. My viewpoint is that what you consider to be a "grant number" is actually an "action number" that should be recorded. This will help with all correspondence and other activities associated with processing that grant. You have identified a logical field that will specify whether the grant was actually approved.

I would suggest going on Amazon.com and finding an Access book.

 

[Maria83]

Thank-you all for your help and suggestions. I'm going to go through the links, and work through it slowly as suggested. I'll post a screen shot of the relationships once I get there.

Thanks again!

 

[Maria83]

Hi Guys,

So I attached a screen shot of the relationships I've established so far. I'm having difficulty figuring out how to have certain fields auto-populate. I want the applicants table to be the first place information is entered, and I want the InstitutionName to auto-populate (for instance, if someone starts to enter The University of Toro..., it should complete Toronto. Or have a drop-down option).

Right now users have to enter the institution information in the Institution table first and then move on to the applicant table. And if an application has an associated grant number, they have to enter that in the Funding table before they can add the information in the applicant table. Like I said previously, I'd like users to fill the applicant table first.

Also, the applicant table currently accepts if I incorrectly spell a grantname (which makes me think I may need a separate table for grant names to reduce that error), and is not case-sensitive on the institution name (which I would like to fix).

Also,just throwing this out there, is there anyway users can populate the database by simply 'copying & pasting' from an excel spreadsheet?

Thanks again guys

 

[RainLover]

You have an "Institution Name" as the Primary Key.

Would not a Number be much easier to handle. I would strongly suggest that all those tables would be better off by using Autonumber.

What you want to do can be done with just a little bit of a tidy up on two of your tables with a lot of work on the Funding Table.

You could start by calling it tblFunding and something similar for all your tables.

Most would agree that tables should be prefixed with tbl, Queries with qry, Forms with frm etc.

 

[plog]

I agree with RainLover about the Institution Name being the primary key in Institutions and the foreign key in Applicants, you should use an autonumber primary key for this. Suppose "Boring Organization, Limited" changes their name to "Idiots Incorporated"? Then you have to update 2 tables and multiple rows. With an autonumber primary key, you can let the MBA's "re-invent" to their hearts' content and all you have to do is change one record in one table.

Also, I'm pretty sure Funding needs to be restructured. You shouldn't numerate field names (FirstFunding, SecondFunding, etc). When you feel the need to do this, its time for a new table. All those numerated fields become a new table like so:

FundingYears
GrantNumber, FiscalYear, Funding


It may be a little different depening on the data in there. Can you provide some sample data for all 3 tables? I have some suspicions about other fields/relationships, but need some data to see how they work together.

 

[RainLover]

Also, I'm pretty sure Funding needs to be restructured. You shouldn't numerate field names (FirstFunding, SecondFunding, etc). When you feel the need to do this, its time for a new table. All those numerated fields become a new table like so: Exactly. According to the rules a new Table and just One would be required. Plog I was waiting for the OP before going on. Can you take over if you get a response. It is bed time for me.

 

[Steve R.]

I suggest that you follow the advice of RainLover and plog.

Moreover, I don't know how complex you wish to be, but there appear to be several issues that you may wish to consider.

1. Multiple funding sources (grants) and multiple recipients. That means allocating a total among various recipients. See plog's post.
2. Tracking the remaining balance. That gets into accounting issues. Is it your intent to monitor the available funds remaining for each recipient, or just to identify how much grant money they were given?

 

[Maria83]

Hi Guys,

Thanks for all the feedback, I really do appreciate it. I used the InstitutionsName as the primary key in the Institutions table because I thought by doing that it would reduce redundancies and not allow users to enter erroneous institution names. In the applicants table each row is one application for one kind of grant. so Bill Smith from The Comic book Institute may have applied for a SuperStar grant with a project called "Superhero's: are they just really popular regular people?" and he may have got funding for that project. If he got funding he would be assigned a unique grantNumber and if not, then he would not have a unique grant number. But Paul Harding from the Comic Book Institute may have applied for a NewSuperhero's Grant, and that would be in the next row, so InstituteName's in the applicant table repeat, so I can't use them as unique identifiers there. Thats why I used autoNumber as the primary key for the applicants table, because there aren't any unique identifiers in that table. I created an Institutions table to control for all the various kinds of institutions that exist in the applicants table. I thought by having an institutions table, i can have each name input as a unique identifier, and that way if I need to change the name of Comic Book Institute to The School of Comics, I could do that in the institute table once and it would carry those changes throughout the applicant table.

I created the funding table to track funding for the approved grants. GrantNumber is a unique number given only to successful grants (so those are never duplicated) which is why I made them a primary key, that way they could link to the applicants table (where the project title and applicant name and institution info, etc. etc are all located).

When it comes to funding, a grant can be awarded a total of say $40,000, that's the approved funding amount. But that amount is provided to the applicant over different fiscal years. So a fiscal year could be 2000-2001, or 2002-2003. The total could be split over two fiscal years, three fiscal years, or the whole thing may be given in the first fiscal year. I need to indicate how the lump sum was given to each applicant over all the possible grants over the last 20 years and for current grants who will get funding in future fiscal years.

Lastly, I don't need to track remaining balances or do any accounting within this database, it is simply being used to store grant data and to pull reports, for instance to see how much money was provided to X institution. Or to see How much money was given to X province(s) over fiscal years 2005/06 - 2009/10.

My Questions:

If I use an autonumber as the primary key for the institutions table, how do I link it to the applicants table? because right now the two tables don't have the same number of rows.

I think I get what you're saying, but am not sure. So I should create one table that lists all possible fiscal years, another table that lists all the grantnumbers, another table that lists start and end dates for all the approved grants, and another table for funding? That seems a bit odd in my mind, I'm not sure how I would fill those tables and how I would relate one table to the next. I also don't know how I would structure the funding table, theres an awarded amount and then the breakdown by fiscal year amounts, how would I link that data to the correct fiscal year in the FiscalYearTable?

I've attached an excel file of sample data, there are three sheets, each with sample data for each of the tables.

Thanks again guys

 

[GinaWhipp]

Hmm, perhaps these links will help...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html

Help with Normalization
http://www.access-programmers.co.uk/forums/showpost.php?p=1146957&postcount=2

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Naming Conventions…
http://www.access-diva.com/d1.html

 

[plog]

If I use an autonumber as the primary key for the institutions table, how do I link it to the applicants table?

Using your sample data, on the Instituions Table let's say 1 gets assigned to Credit View and 2 gets assigned to North General as primary keys. On the Applicants table cell C2 becomes 1, C3 becomes 2 and C4 becomes 1. Those two tables are now linked. Them not having the same number of rows is exactly how a relational database is suppose to work (thus the term "one-to-many relationship").

However, I don't know about that relationship. Who gets the grant? Is it the applicant or the Institution? Is the ApplicantName really just a conact name for that grant?

As for fiscal years, you don't create a lookup table, you simply assign the value when you need to. Tweaking the structure I initially laid out for that table, is what your data would look like in it:


Grants
GrantNumber, FirstFiscalYearStart, ApprovedFunding
300-201, 1/1/2001, 100000
ND-4009, 1/1/2001, 100000


FundingAmounts
GrantNumber, FiscalYear, Amount
300-201, 1, 12500
300-201, 2, 50000
300-201, 3, 37500

 

[Maria83]

Using your sample data, on the Instituions Table let's say 1 gets assigned to Credit View and 2 gets assigned to North General as primary keys. On the Applicants table cell C2 becomes 1, C3 becomes 2 and C4 becomes 1. Those two tables are now linked. Them not having the same number of rows is exactly how a relational database is suppose to work (thus the term "one-to-many relationship").

Okay, so right now I do have a one-to-many relationship between the institutions table and the applicants table, so what you are saying is that I just need to add an autonumber to the institutions table and have it as the primary key.

However, I don't know about that relationship. Who gets the grant? Is it the applicant or the Institution? Is the ApplicantName really just a conact name for that grant?

So the grant is assigned to the applicant, the money is awarded to the applicant but is held in an account at the applicants institution. They want to track which institutions have held funds for our awardees. So the applicant name is a contact name but may also be used for other purposes, to differentiate between new applicants and old applicants for instance - to see if word is spreading about our competitions and to show that we are building capacity across provinces etc etc..

As for fiscal years, you don't create a lookup table, you simply assign the value when you need to. Tweaking the structure I initially laid out for that table, is what your data would look like in it:

Grants
GrantNumber, FirstFiscalYearStart, ApprovedFunding
300-201, 1/1/2001, 100000
ND-4009, 1/1/2001, 100000


FundingAmounts
GrantNumber, FiscalYear, Amount
300-201, 1, 12500
300-201, 2, 50000
300-201, 3, 37500

So the thing is that the grant start/end date needs to be indicated somewhere and that date can be different from the grant-funding payout dates that are tracked by fiscal year. So grant 300-201 could start November 1999, but the first fiscal payment could be January 2000/01. So they want to track both dates.

So just so that I'm clear, I think what you're suggesting is that I have two tables. In the Grants table I indicate GrantNumber, start/end date and approved funding. In the FundedAmounts table, I indicate GrantNumber, Fiscal Year, and Amount. Fiscal year in the second table has to link to a table though with all the fiscal years though, doesn't it? I know you said I don't have to create a look-up table, but what do the numbers (1,2,3) refer to then?

I'm sorry if I have too many questions, I really appreciate your help and I'm going to implement your suggestions and hope it all works out.

Thanks again PLOG

 

[RainLover]

Maria83

Can we stop for a moment and look at what these Keys are all about. There have been a lot of incorrect statements made in this thread. IMHO.

A Primary Key is what identifies a particular record within a table.

It must be unique within that table. This is about the only rule you need to know. The rest are suggestions not rules. One of these suggestions is that a user must never see or knowingly use a Primary Key.

A primary key could be AutoNumber and run consecutively. 1,2,3,4,5,6
It could be AutoNumber and run as a random number. e.g. -986, 34, 1232, -76986
It could be a known value. Such as a Person's Name, A Color, for a car it could be the Registration number and so on.

The first two are simple to understand. Random or Consecutive is simply set in the Table properties.

The latter is complex because we know that a Person's Name is going to clash eventually.
There is a limited number of Colors.
Car Registration numbers can change.

This limitation can be overcome by combining two fields together. If required it could be a combination of 3 or 4 fields until you find a unique combination.

The first group are called Surrogate and the others are called Natural.

I use and recommend Autonumber because I never need to use more than one field. It is always Unique.

If I had 2 Tables I would have a Primary Key in each table and that would be of type Autonumber.
The second Table would also have a Foreign Key who's value would match ONE of the Primary Key's Values.
Each Record in the First table has one Primary key for each record.
The same applies to the second table however the second table has the foreign key with a value equal to the primary key of the first.
With this table it may have several records that equal the primary of the first which would be known as a One to Many relationship.

I hope you can understand this. It is a simple concept but when putting digit to keyboard it is not so easy.

 

[plog]

Yes you would have 2 tables for your grant data--one for the overriding data (name, number, etc.) then another for the funding.

Fiscal year in the second table has to link to a table though with all the fiscal years though, doesn't it?

Nope. It refers to what fiscal year you are in for that grant (1st, 2nd, 3rd, etc.). Now if you want to find out when a specific fiscal year starts you would create a query based on Grants and FundingAmounts and then calculate the specific fiscal year's start date by using the DateAdd function (http://www.techonthenet.com/access/functions/date/dateadd.php)

FiscalYearStart: DateAdd("yyyy", (FiscalYear - 1), FirstFiscalYearStart)

Basically, you take the grant's start data and add years to it based on whatever fiscal year you are in.

 

[plog]

Rain, can you explain in terms of her data where we are going wrong? You've explained primary keys well, but for her to learn you really should put it in terms of her data.

I don't really know what your ultimate objective is because I can't see the connection between her data and your abstract concepts so I'm certain she doesn't either.

 

[RainLover]

Plog

I was a bit worried that I would not be completely understood. In fact one of your statements led me to write something.

I agree with RainLover about the Institution Name being the primary key in Institutions and the foreign key in Applicants, you should use an autonumber primary key for this. Suppose "Boring Organization, Limited" changes their name to "Idiots Incorporated"? Then you have to update 2 tables and multiple rows. With an autonumber primary key, you can let the MBA's "re-invent" to their hearts' content and all you have to do is change one record in one table.

I did not catch onto where you were going with your multiple changes.

I simply used the Rule to be sure to be sure. I was trying also to avoid using someone's name in a critical manner. I am trying to be a good boy tonight. Talking of which it is 2:00 AM. I am off for now.

 

[Maria83]

Hey Guys,

Thanks so much for all of the feedback, I've made a few changes and have a couple of questions.

Rain - Thanks so much for the explanation of primary keys it was really clear and helped me re-think my design and putting it together with the suggestions Plog gave me really helped (although Plog was right I didn't really understand how it related to my data at first, I think I made the appropriate changes based on your combined advice). I attached a screen shot of the relationships I've created with my new tables (re-formatted tables) and also an excel file of sample data to get a picture of what my database looks like.

So I ran a few queries and reports to make sure things were working, and everything seems to be working well. I created a form for users to be able to populate the database, but I'm having some difficulty with it - and could really use some help. For some reason I can run through all my records on the form but can't seem to generate an empty form that users can then fill to populate the database. I'd also like certain fields to pre-populate (like Institution, city, province, name) with the option to input data if it doesn't already exist in the pre-populated field. I'm going to try to look into this tonight and hopefully figure it out, but any suggestions you guys can make would really help me out. And/or if you see any alarming issues with the relationships I've created or in the tables I created - I'd really appreciate your feedback.
Thanks so much guys,
Maria