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 SuppliersPerCustomer table would be {SupplierID, CustomerID}. Now the Suppliers table will have a 1 to many relationship with the SuppliersPerCustomer table and the Customers table will also have a 1 to many relationship with the SuppliersPerCustomer 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 ("...includes 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]

@MNNovice
[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.

[MNNovice]

Denburt:

Yes things are much clearer now. Following your instructions I was able to create the tblGrantAPExp. However, I must have done something incorrect. As I try to select a Fund number on this form (frmGrantAPExp), I can see the Fund number but the GrantID is displayed as ID and NOT a grant number. Therefore, it's not easy to select which fund I want. Because eg, Fund 702 has more than one Grant number associated with it. If I can only see the GrantID how do I pick the correct Fund No 702?

2. In the form frmGrantAPExp, I need to have both the grant number and fund number displayed. Is there a way to do this? Because I no longer have GrantID or FundID available on this form rather GrantFundID.

Thanks.

[MNNovice]

Birdman:

I understand your cautionary notes. Thank you.

However, I don't think I understand what you meant when you said,

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"

Can you elaborate on this? I am not clear on the location of the combo boxes and command button you are referring to. Also, I don't know VBA codes. Thanks.

[OldBirdman]

Without VBA coding I'm lost so I'll let someone else pick up that question.

To elaborate on my comments -
I believe that users should work in forms, not in the tables themselves. This way the programmer has control over what is allowed, by whom, and when. This is my concept of the division between programmer/developer and the end user.

So, as I see it, you will need 2 forms, one for updating tblFunds and the other for updating tblGrants. Update here is to add records or modify (edit) records. You seem to have that OK, based on your last post.

Now I would have a 3rd form for creating the links between Grants and Funds. For the moment, I'll have 3 controls on this form. I'll call them cboFunds, cboGrants, and cmdAction.

cboFunds will have a query for Control Source. This query, as a minimum, would have tblFunds.FundID and tblFunds.FundNo, with an 'ORDER BY' appropriate to this use. ORDER BY could be a date field, descending, the FundNo ascending, or whatever. The first column, containing the FundID, would be hidden, but cboFunds would be bound to column 1.

cboGrants would be set up the same as cboFunds.

Between these 2 controls would be cmdAction, a command button. It would have a caption of "Create Association". When pressed, it would -
1) Check that both comboboxes had a selected item
2) Create a new record for tblFundGrants
3) Set both comboboxes to initial state
4) Notify user of success or failure
a) One or both comboboxes not selected
b) Duplicate Association - correct selected
c) New Association created
d)

Denbert or FishVal- can you help with a non-VBA solution here.

[MNNovice]

Birdman:

Thank you for your detailed elaboration. It makes sense. But without knowing VBA it's of no use for me. I do appreciate your time and effort.

I will wait for Denburt's response.

Thanks again.

[Denburt]

As I try to select a Fund number on this form (frmGrantAPExp), I can see the Fund number but the GrantID is displayed as ID and NOT a grant number.

Ok in the field that you are using to select the Fund/Grant your rowsource should be a query you can add all three tables to this query and use the fields needed to display the Grant number or description etc. making sure that the ID used for tracking purposes is from the tblGrantFund (usually the first field. the field should be bound to that column and you can set that column size to 0 so it isn't seen.

2. In the form frmGrantAPExp, I need to have both the grant number and fund number displayed. Is there a way to do this? Because I no longer have GrantID or FundID available on this form rather GrantFundID.

On the form add a field for the Grant number and reference the Fund Field Column 2 or whatever column it is in. The controlsource for the field Grant would be. =cboFund.column(2) and it would not be editable. Note:the columns start with 0 as a reference

I usually use a light shade of yellow for all of my non editable fields and make a note somewhere on the form.

[Denburt]

Now I would have a 3rd form for creating the links between Grants and Funds. For the moment, I'll have 3 controls on this form. I'll call them cboFunds, cboGrants, and cmdAction.

Leave out the button you just need the two fields, the forms recordsource would be tblFundGrants and each combo box would have a controlsource for the item in question. I would probably use Continuous forms or set it to datasheet so I can have a better view of what is already associated with what. Make sure your first column is using the ID in question column size 0 etc. and you should be all set

[MNNovice]

Denburt:

Thanks again.

Ok in the field that you are using to select the Fund/Grant your rowsource should be a query you can add all three tables to this query and use the fields needed to display the Grant number or description etc. making sure that the ID used for tracking purposes is from the tblGrantFund (usually the first field. the field should be bound to that column and you can set that column size to 0 so it isn't seen.

Aha! now I know I need to do a query first and then use it in the control source (instead of using tblGrantFund) of the fromGrantAPExp.

I shall try now and keep you posted. Many many many thanks.

[Denburt]

;) My pleasure! (did I hit 20 char yet?) lol

[Denburt]

Oh and thanks again for the help Fish and Birdman I know that my communication skills aren't always as good as they could be.

[MNNovice]

Denburt:

I succeeded but partially.

Ok in the field that you are using to select the Fund/Grant your rowsource should be a query you can add all three tables to this query and use the fields needed to display the Grant number or description etc. making sure that the ID used for tracking purposes is from the tblGrantFund (usually the first field. the field should be bound to that column and you can set that column size to 0 so it isn't seen.

I got this part working. On the form frmGrantAPExp cboFund is linked to qryGrantFund. I can see the fund list along with the associated Grant numbers. Really cooooooool.

But when I added cboFund.Column(2) in the control source of cboGrant on the same form. I get no data. I expected to get cboGrant automatically populated as soon as data selected for cboFund.

qryGrantFund has these fields
GrantFundID
GrantNo (this is a text field)
FundNo (text)

Column count 3 and column widths are 0";1";1";
What am I doing wrong?

You are a good teacher. I find your communication rather easy to understand. Thanks again.

ps: I have the form in datasheet view.

[OldBirdman]

Thanks Denburt for taking over. When I first started with Access, I had real problems with a mix of VBA and Macros, and elected to go with VBA. Now I can't do anything without it.

[Denburt]

Yeah BirdMan I do the same thing I used to be real bad but now I try to take a step back and see if I can simplify things before I start my VBA typing, in the long run it helps save me some time (usually). I once worked with a guy and he said (jokingly) that if I needed to add 2+2 I would sit down and write a routine for it. Your quite welcome BTW.

Ok MNNovice

But when I added cboFund.Column(2) in the control source of cboGrant on the same form. I get no data. I expected to get cboGrant automatically populated as soon as data selected for cboFund.

qryGrantFund has these fields
GrantFundID
GrantNo (this is a text field)
FundNo (text)

well if grant is the second column then a slight change is in order however you should have something or did you leave off the = sign?

The ID field is column(0)
The next column (grant I think) would be:
=cboFund.Column(1)

Also thank you for the nice comments.

[MNNovice]

No I didn't forget the equal sign and it's still not working. Thanks.

[Denburt]

Well according to everything I posted it should be working. I just verified it on this end. Check the field names and look everything over carefully and if it still looks o.K. to you then (if it's not to big or sensitive in nature) you could upload it and I can look it over for you.

[MNNovice]

Denburt:

Many thanks. Attached is the DB. Please remove it from this site once you find out where I goofed up. I would like to learn from my mistakes. If you keep a copy for yourself, that will be okay. It perhaps will help me with future questions as I build it.

Thanks.

[Denburt]

Ah bingo! I suppose I should have stated that you should use a text box for the Grant field. If you go to design view right click on the Grant combobox click on "Change To" then change it to a text box all should be fine.

[MNNovice]

Denburt:

Thanks. I shall try and shall keep you posted.

[MNNovice]

Denburt:

Thank you so much. It worked like charm. One question. Since my form is in datasheet format, how can I make this text box shaded in a different color so that the end users will know they cannot change the data for Grant No?

I didn't know the short cut to change a combo box to a text box. Learn something new. Thanks again.

[Denburt]

@MNNovice
Datasheets can be pretty restrictive in a lot of ways, about the only thing I could suggest for that would be to change the Grant field so that is is disabled (Enabled=no in properties) this would grey out the text in the box and not allow them to tab into it.

Glad you learned a new shortcut and I am pleased that I could be of help. Feel free to keep the questions comming.

[MNNovice]

Denburt:

Thanks for your offer of help.

A grant can have a several kinds of split ratio for funding sources from Fed and Local. It could be 80/20, 83/17 or 95/5.

Because Fed may choose to change ratios I created a table called tblSplitRatio to record any changes in future. This table has these fields
SplitRatioID - PK (auto)
FedRatio - Number
LocalRatio - Number

My question is how do I connect it to the tblGrants? If I add a GrantID as a FK that's not going to update the record in tblSplitRatio. I will prefer to enter all info relating to a grant in one table and go from there. But what I prefer and what I get to do to accomplish my goals are not the same. (LOL).

This split ratio also plays a vital role when we draw money against a grant from the Fed. For example, let's say we have an invoice from a vendor for $100 we can only draw $80 or $83 or $95 from Fed. So in my tblAPDetails, I would like this amount automatically calculated once I enter the invoiced amount of $100.

In tblAPDetails, as you know, the GrantID is tied to the FundID.

I am not sure how to go about calculating the net amount of $80 (80% of $100).

Can you give me some direction?

[Denburt]

Well if your only working with a 2 part ratio 80/20 per say and it always totals 100% then you could set a field in the tblGrants and use that for the Feds percentage your local percentage would always be the remainder. Then in the form you could use a calculated control to determine the amount that the feds would pay etc.

If you do want to use a second table for some reason then I don't see any reason why you couldn't select the Grant and store the ID in the split table then add the ratios

tblSplitRatio
SplitRatioID - PK (auto)
tblGrantID - FK
FedRatio - Number
LocalRatio - Number

[MNNovice]

Believe it or not, I was thinking the same thing. I will try the first method, ie, creating a FedRatio field in tblGrants and then have the LocalRatio automatically populated (100 - FedRatio). That will be a lot easier for me. Thanks.

I think I am getting close to be done with the Grant part. Now I have the next which is to record expenses for this grant. That's a little complicated but I need to worry one step at a time.

Thanks again. Until my next question.

[Denburt]

I did make some of the changes we talked about recently, I hope they meet your standards.

[MNNovice]

Denburt:

I calculated both fields separately and then re-do the Total field and it's working now. Nothing was wrong nowhere. For whatever reason the old total box wouldn't work. But thanks for the back up copy.

Thanks.