Restricting Data Entry

[MNNovice]

I have 2 tables

tblFunds
FundID (auto / PK)
FundNo (Text)
FundDescr (Text)


tblGrants
GrantID (auto / PK)
GrantNo (Text)
GrantDescr (Text)

Question 1: I have a 3rd table which includes fields of its own plus fields from other tables. Now I want to include FundNo and GrantNo in this 3rd table. A Grant Number can have more than one Fund numbers. I would like to restrict data entry into the 3rd table as such that it won't allow incorrect data. For example, Fund 872 belongs to Grant Number A1. If someone picks Grant A74 for fund 872 the DB will result in an error message. How do I do this?

Question 2: Do I need to add GrantID as an additional field to the tblFunds?

Question 3: Do I create another table called tblFundGrants with these fields:

tblFundGrants
RecordID (PK / auto)
FundID (FK)
GrantID (FK)

Question 4: Here is the tricky part. One fund can have more than one Grant Number.

Any help any one? Thanks.

[Denburt]

Have you read up on table normalization?

[MNNovice]

Yes, I have. Actually quite a few times. However, I am one of those who learn mostly from listening. In other words, for me effective learning does not come from reading alone. It helps tremendously if I can see/hear examples.

If you don't have time to explain a little by taking the examples of tables I posted, I will understand.

Thanks.

[Denburt]

I would be more than happy to try and help I just thought that may assist. It sounds as though you have a many to many type of issue.

Fund1 = Gant1
Fund1 = Gant2

Gant1 = Fund1
Gant1 = Fund2

Am I mistaken?

Quoting from Mary's article.

If any tables have a many to many relationship this must be broken out using a JOIN table. For example, Customers can have many Suppliers and Suppliers can supply to many Customers. This is known as a many to many relationship. You would need to create a JOIN table that would have a primary key made up of a foreign key reference to the Customers table and a foreign key reference to the suppliers table. Therefore the SuppliersPerCus tomer table would be {SupplierID, CustomerID}. Now the Suppliers table will have a 1 to many relationship with the SuppliersPerCus tomer table and the Customers table will also have a 1 to many relationship with the SuppliersPerCus tomer table.

There are many ways this can be broken out 1 would be to create a table that would hold the various relationships between the tables.

GrantFundTbl
GFID (PK / auto)
GFrelation

FundID 1 = Gant1
FundID 1 = Gant2
FundID 2 = Gant2

Is this what you are looking for?

[OldBirdman]

Question 1 states that a Grant Number can have more than one Fund number. Question 4 states that one Fund number can have more than one Grant number. Therefore, the two tables, tblFunds and tblGrants have a many-to-many relationship. Setting this up, using tblFundGrants should be the first step.

tblFundGrants
RecordID (PK / auto)  not really necessary.  Primary key could be FundID and GrantID, which is unique.
FundID (FK)
GrantID (FK)
Field1 (some data type)
Field2 (some data type)
...

If a user is on a record, say a Grant record, there would be a command to add a new Fund. Clicking this must generate a new tblFundGrants record, with proper keys entered by code, not operator, and then present user with a dialog to complete the additional data referred to in Question 1 ("...include s fields of its own ...").
The same logic applies to user on a Fund record.
Now the conflict. If this is a many-to-many relationship, the error described in Question 1 is not an error. There is no way to know that Fund 872, already belonging to Grant A1 cannot also be for Grant A74, because

Here is the tricky part. One fund can have more than one Grant Number.

[MNNovice]

Denburt:

Thank you for your time. I appreciate.

Following your suggestions I created three tables (tblGrants, tblFunds and tblGrantFunds).

Let me give you examples and then ask my questions.

Fund 702 belongs to Grant No. 164 and 819

Grant 215 has Funds 834, 844 and 845

Question 1: Now do I add a field GrantID to tblFunds and another field called FundID to tblGrants? If not, how do I record this data?

I defined 1 to many relationship between tblFunds & tblGrants and both the tables are connected to tblGrantFunds.

Question2: How will the data get updated in tblGrantFunds?

Now you know why I am a Novice. Thanks.

[Denburt]

I defined 1 to many relationship between tblFunds & tblGrants

Delete that relationship

both the tables are connected to tblGrantFunds.

using tblGrantFunds ID stored in the third table you mentioned and thus would be the link between tblGrant and the 3rd table which includes fields of its own plus fields from other tables as well. This would also be the link to the tblFunds.

Question2: How will the data get updated in tblGrantFunds?

Set up a form so you can go in and update that information or just enter it manually straight into the table.

[MNNovice]

Denburt:

I understand what you are saying. I shall keep you posted how it goes. Even though I am not sure how do I update FundDescr and GrantDescr. Seems like some data (FundNo., GrantNo) will be repeated in three different tables. Am I missing something here?

Thanks.

[OldBirdman]

Each will be repeated in 2 tables, not 3. These are the primary keys in their own table and foreign keys in the other table. So yes, they will be repeated, but they really are not data, more like infrastructure.

[MNNovice]

So if I add / edit Fund infomation, I will have to do it in tblFunds and tblGrants? And tblGrantFund will be updated on its own?

[FishVal]

So if I add / edit Fund infomation, I will have to do it in tblFunds and tblGrants? And tblGrantFund will be updated on its own?

[tblFunds] holds a list of funds
[tblGrants] holds a list of grants
[tblGrantFund] holds valid grant/fund combinations you've mentioned in Q1 of original post, thus linking [tblFunds] and [tblGrants] via many-to-many relationship;

PK of [tblGrantFund] (either Autonumber field or composite key produced from both FKs) should be linked to FK in the 3rd table you've mentioned in the original post thus ensuring only valid grant/fund combinations in this 3rd table.
If you've decided to have Autonumber PK in [tblGrantField], then a unique multifield index should be set on the rest two fields to prevent grant/fund combination duplication.

I assume grant/fund combination is not supposed to be unique throughout the 3rd table mentioned in original post, otherwise this third table appears to play the role of bridge table in many-to-many relationship thus making [tblGrantFund] redundant.

Regards,
Fish

[MNNovice]

Fish:
Thanks for your elaborate answer. I appreciate.

The 3rd table referred to in my original posting is called tblGrantAPExp (to record AP expenses for grants). The fields in this table are as follows:

APID                          (auto / PK)
VendorID                    Number
InvoiceNo                   Text
InvoiceDate                 Text
AccountID                  Number
FundID                       Number
SubClassID                Number
ProjectID                    Number
GrantID                      Number

Each Grant has a string of account number which is comprised of various combination of Account No, Fund no, SubClass No, Project No etc. In my original posting the first question refers to validation of data. Suppose one selects a fund while entering an invoice, I wanted to make sure he/she picks the correct Grant number that goes with the selected fund number. I was not sure how to do this because a grant can have multiple fund numbers, likewise, a fund can be associated with multiple grants.

1. I don’t quite understand what you meant when you said, “

If you've decided to have Autonumber PK in [tblGrantFund], then a unique multifield index should be set on the rest two fields to prevent grant/fund combination duplication.”

Can you explain with an example?

2. Now that I have 3 tables, tblFunds, tblGrants and tblGrantFunds which one I need to use to add/edit data for a fund or a grant? Suppose fund 702 originally had 2 grants and now I need to edit this fund data because one new grant will be associated with fund 702. Which table(s) do I use to update this data? All three, just tblFunds and tblGrants?

[Denburt]

Very well put Fish, thanks for helping to clarify things. MNNovice when you want to add a Fund you would add it to the tblFunds then you would go into tblGrantFunds and specify which Grants are associated with it. The same would apply for any new grants added.

For a MultiField Index you would open the table (tblFundGrants) in design view.
On the Table design toolbar click the Indexes button. In this window you can choose a name for the inde,x then select the first field such as the GrantID You can then set it to unique, then in the next row you would leave the Index Name blank and choose the field FundID. This will prevent any combination of those fields from being repeated.

Your table tblGrantAPExp no longer needs a FundID and a GrantID you can now use the id from tblFundGrants in your query you would tie the fund table to tblFundGrants and the Grant table to tblFundGrants and tblFundGrants ties into tblGrantAPExp.
I think that should do it... Is this a little more clear now?

[Denburt]

Oh and to make sure you understand when you design your form you can use the tblFundGrants in a list or combo box showing the two fields Funds and Grants they then have no choice but to choose the options you have provided in that table.

[OldBirdman]

I would NEVER let a user have direct access to tblFundGrants. Any error here will be difficult to detect, and could remain hidden.
User should be able to enter a new Grant into tblGrants, via a form. To correct entry errors, the user needs to be able to edit a Grant in tblGrants.
User should be able to enter a new Fund into tblFunds, via a form. To correct entry errors, the user needs to be able to edit a Fund in tblFunds.
To create a relationship between a Grant and a Fund, I would have my user select a Grant from one combobox, and a Fund from a combobox. Now user presses a cmdbutton captioned "Create Relationship", and VBA code does the rest. If the link already exists, the cmdbutton would be captioned "Delete Relationship"
This way there can be no duplicates, and no record in tblFundGrants with a missing Fund or Grant.
Once this structure is set up, the rest is details. Additional fields, or tables, can be added as necessary.