具有多个变量的表的结构 [英] Structure of a table with multiple variables

问题描述

我有 tblFunds 这些字段

I have tblFunds with these fields

FundID PK（自动）

FundNo Text

FundDescr Text

FundID PK (auto)
FundNo Text
FundDescr Text

我需要一张表来记录收到的Grants。每个拨款都分配了一个基金编号。但是，有时赠款可以有多个与之相关的基金。


到目前为止，我概述了 tblGrants ：

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（自动）

GrantNo Text

GrantTitle文件

资金来源文本

CFDA＃文字

奖励日期/时间

BeginDate日期/时间

EndDate日期/时间

GrantAmount货币

GrantID PK (auto)
GrantNo Text
GrantTitle Text
FundingSource Text
CFDA# Text
AwardDate Date/Time
BeginDate Date/Time
EndDate Date/Time
GrantAmount Currency

问题1.拨款：我是否使用 FundID 并与tblFunds建立关系？如何在有多个基金时建立数据。

问题2.拆分比率：拨款可以为联邦和地方资金提供不同类型的拆分比率。它可以是80/20或90/10等。或者它可以兼得。我应该创建一个 tblSplitRatio 来合并这些数据吗？

问题3.拨款类型：拨款可以是资本项目或运营项目。或者它可以两者兼而有之。我该怎么做呢？复选框表示tblGrants中的一个或两个？

问题4.分摊年度：一个资助可以有一个以上的分配年份。每年都有不同的资金分配。我需要一个单独的桌子吗？


请帮忙。谢谢。

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.

推荐答案

当然，有很多方法可以做到这一点。以下是我最初的想法，基于您提供的信息：


1.
tblGrantFunds

FundID号码FK

GrantID号码FK

您可能需要这里的金额吗？


2.

In tblGrants ，

FederalPercent Number（0 - 100）

LocalPercent Number（0 - 100）


3.

在 tblGrants ，

GrantType - 文字（资本/经营/两者）


4.
tblAllocations

GrantID - 数字PK FK

GrantYear - 数字或日期PK

分配 - 货币

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

ChipR：


非常感谢。我将继续发布我的进展。

ChipR:

Many thanks. I will keep posting on my progress.

ChipR：


跟进你的建议。


1.最终用户必须输入所有小信息，可能通过使用表格输入给定的赠款。所以，如果我要创建这个表单，不要我需要一些子表单来合并你建议的表格吗？


2.如果我在tblGrants中添加FederalPercent和LocalPercent，怎么样我要记录多个分割比率。让我解释。赠款的一部分可能具有80％/ 20％的分配比率。补助金的剩余部分可能有90％/ 10％的比例。如果我按照你的建议为FederalPercent设置了一个字段，我不知道如何记录这两个不同的分割比例。你能解释一下吗？


谢谢。

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.

这篇关于具有多个变量的表的结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！