By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,967 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,967 IT Pros & Developers. It's quick & easy.

Structure of a table with multiple variables

100+
P: 418
I have tblFunds with these fields
FundID PK (auto)
FundNo Text
FundDescr Text
I need a table to record Grants as they are received. Each grant is assigned a fund number. However, sometimes a grant can have more than one fund associated with it.

So far I sketched tblGrants like this:
GrantID PK (auto)
GrantNo Text
GrantTitle Text
FundingSource Text
CFDA# Text
AwardDate Date/Time
BeginDate Date/Time
EndDate Date/Time
GrantAmount Currency

Question 1. Grant Funds: Do I use FundID and create a relationship with tblFunds? How do I establish data when there is more than one fund.

Question 2. Split Ratio: A grant can have different types of split ratio for federal and local funding. It could be either 80/20 or 90/10 etc. Or it can have both. Shall I create a tblSplitRatio to incorporate this data?

Question 3. Grant Types: A grant can be either for Capital projects or Operating projects. Or it can be for both. How do I do this? Check box to indicate either or both in tblGrants?

Question 4. Apportion Year: A grant can have more than one apportion Year. Each year has different allocation of money. Do I need a separate table for this?

Please help. Thanks.
Apr 29 '09 #1
Share this Question
Share on Google+
18 Replies


Expert 100+
P: 1,287
Of course, there are many ways to do this. Here are my initial thoughts, based on the information you have given:

1.
tblGrantFunds
FundID Number FK
GrantID Number FK
You may need the amounts here?

2.
In tblGrants,
FederalPercent Number (0 - 100)
LocalPercent Number (0 - 100)

3.
In tblGrants,
GrantType - Text (Capital/Operating/Both)

4.
tblAllocations
GrantID - Number PK FK
GrantYear - Number or Date PK
Allocation - Currency
Apr 29 '09 #2

100+
P: 418
ChipR:

Many thanks. I will keep posting on my progress.
Apr 29 '09 #3

100+
P: 418
ChipR:

Following up on your suggestions.

1. An end user will have to enter all the little information, perhaps by using a form, for a given grant. So if I were to create this form, donít I need to some sub forms to incorporate the tables you suggested?

2. If I add FederalPercent and LocalPercent in tblGrants, how am I going to record multiple split ratio. Let me explain. A portion of a grant may have a 80% / 20% split ratio. The remainder of the grant may have a 90% / 10% ratio. If I have one field for FederalPercent as you suggested, I am not sure how do I record this two different split ratio. Can you please explain?

Thanks.
Apr 29 '09 #4

Expert 100+
P: 1,287
M,

1. Yes. Subform or list box or combo box or whatever. Forms are easy if your data is organized properly.

2. Sorry, I didn't see that there would be multiple ratios. How many can one grant have? This may need a separate table.
Apr 29 '09 #5

100+
P: 418
ChipR:

You are very prompt. Thank you.

1. I will attempt and shall ask you for more help on this item, if and when I run into it.

2. There are more than 2 possibilities as far as I know: 80/20, 90/10, 95/5

3. Following up on your previous suggestion. tblAllocation: Looks like this is where I will need to show the amount/allocation. If the apportion year is single there will be one entry, if there is more than one apportion year then there will be multiple entry. Does this sound right?

4. So the frmGrant will have to have a subform that will be based on tblAllocations and tblGrantFunds. Do I understand you correctly?

Thanks again.
Apr 29 '09 #6

Expert 100+
P: 1,287
I'm bored waiting on compiling and stuff :)

On the ratios, I'm not quite sure, but it does sound like you need a table.

tblSplitRatio
GrantID Number FK
Amount - Currency
Percent1 - Number
Percent2 - Number

3. Right.

4. Yes, you could have one subform listing the Funds associated with this grant. Another subform could list the yearly Allocations. Seems feasible to me.
Apr 29 '09 #7

100+
P: 418
ChipR

I understand the structures of these various tables, but can you explain how do I go about determining the relationships between them? Which one should be 1 or 2 or 3 (I am refering to join type). I always get flustered with this step. Thanks.
Apr 29 '09 #8

Expert 100+
P: 1,287
To tell you the truth, I don't have relationships set up between my tables. But to do so, you would create relationships between fields that are exactly the same in both tables and use the join type 1.

Expand|Select|Wrap|Line Numbers
  1. tblSplitRatio.GrantID  -> tblGrants.GrantID
  2. tblAllocations.GrantID -> tblGrants.GrantID
  3. tblGrantFunds.GrantID  -> tblGrants.GrantID
  4. tblGrantFunds.FundID   -> tblFunds.FundID
Apr 29 '09 #9

NeoPa
Expert Mod 15k+
P: 31,709
A-1.
Assuming [tblFunds] is a finite list of fund details, like a lookup table, then
[tblGrantFunds] would contain a [GrantID], a [FundID], and any other details that may be required. [GrantID] and [FundID] would, together, make a unique index. This can be used as the PK, or another, AutoNumber, field can be created for this purpose.
Apr 29 '09 #10

NeoPa
Expert Mod 15k+
P: 31,709
Wow!! You guys have been busy. I was expecting that to be post #3 :D
Apr 29 '09 #11

100+
P: 418
NeoPa / ChipR:

Many thanks. I know these are petty/simple and perhaps boring for you. I appreciate your help.
Apr 29 '09 #12

NeoPa
Expert Mod 15k+
P: 31,709
@MNNovice
If every item on the Many side of a One-to-Many link, has a matching entry in the One side, then it is appropriate to set up a Type 1 Join.
If it is possible for an item on the Many side of a One-to-Many link, to have no matching data on the One side, then a Type 2 or Type 3 join should be used.

Consider the scenario of monitoring humans. [tblPerson] may have a join set up (to itself) to represent a life-partner (spouse). This join could not properly be set up as a Type 1 as it's perfectly normal for a person not to have a partner. A Parent join, on the other hand, would be a Type 1 as there is no conceivable situation where a person would have had no parents.
Apr 29 '09 #13

100+
P: 418
NeoPa / ChipR

I am afraid I will have to make some more changes with regard to the table structures. I just found out that the apportion fiscal year is not critical for our purpose. Therefore, I decided not to create tblAllocations. However, I have a new situation with Grant Budget.

The budget looks like this:
Item Description
Account No
Fund No
Org No.
Program
Sub Class
Project
Budget Amount
Change (+ / -) Amount
Total Amount
I am thinking about creating a new table called tblGrantBudget with GrantID as a FK. Other fields will be
AccountID (from tblAccounts)
FundID (from tblFunds)
OrgID (from tblOrgs)
ProgramID (from Programs)
SubClassID (from tblSubClass)
ProjectID (from tblProjects)
What about the amount? I can add the total amount as a calculated text box on the form instead of having it on the table. But what about the split ratio? Because based on the project number the split ratio can vary. This is where I am not sure what to do.

May I have some guidance? Am I on the right track of thoughts?

NeoPa: I was really impressed with your explanation of relationship. It is very easy to remember. Hopefully I can sort it out when it comes to apply my understanding. Thanks.
Apr 29 '09 #14

Expert 100+
P: 1,287
My advice is to stop designing immediately, until you have all of the information you need. If you find out later that you have to make fundamental changes to your data structure, any implementation work you've done is not only a waste of time, but will probably waste you more time as you try to make do with it, or fix it. Instead, use the time wisely now, and get very detailed descriptions of what data you need to produce, what reports you want to produce, what functions you want your application to have, and finally what data you will have access to.
Apr 30 '09 #15

100+
P: 418
ChipR:

I completely understand what you are saying but my experience tells me getting ALL the information for a bunch of end users can only happen in a perfect world. So I learn to be content with what I have now and proceed with caution. Develop the DB in phases with 2 - 3 sample data and play with what it can and cannot do.

But I have some good understanding of what is required of this task, which I put together in the attached word document.

Thanks.
Apr 30 '09 #16

NeoPa
Expert Mod 15k+
P: 31,709
@ChipR
I wholeheartedly support the last comment from Chip. If I could convince any of our members of the wisdom of this approach I would consider myself to have done them a great service indeed.

Attention to detail at this stage and focusing clearly on the precise requirements are so critical to a project, as is the concept of proceeding step-by-step, only progressing to the next step when all dependant steps are completed.

In real life, outside influences (often those that request the project) are prone to change the requirement. This is a situation outside of your control, but where possible Follow the guidelines laid out above and by Chip in the earlier post, and you will at least reduce your subsequent problems.
Apr 30 '09 #17

NeoPa
Expert Mod 15k+
P: 31,709
@MNNovice
Good answer.

Furthermore, it's also necessary sometimes to go through a type of trial and error phase. Especially when you are building up your experience.

It never hurts to stress the importance of the design phase though, nor how important it is to get this part right.
Apr 30 '09 #18

Expert Mod 2.5K+
P: 2,545
Interesting debate, which in software development circles in general mirrors the for and against arguments for prototyping (quick'n'dirty) methods of implementation.

The trouble with trying to define all aspects of software in advance is analysis paralysis - users can't think of all things they want, so they're unwilling to sign off requirements as complete. Equally, as Chip has pointed to, you need to know enough to have some stability in the design fundamentals - as changes to table structures, particularly those that may involve changing existing relations, can be very difficult to make at a later date.

Access is in my opinion a good prototyping environment - and can be used to generate test applications very quickly. Be prepared to throw them away and start again if requirements change drastically, however...

-Stewart
Apr 30 '09 #19

Post your reply

Sign in to post your reply or Sign up for a free account.