Can't Link Primary Key to Foreign Key in New Table - HELP!

[fizban33]

I am somewhat new to access and am restructuring a database as par t of a
summer internship. The database is for fundraising and is designed to
provide a list of different foundations' proposal submission deadlines, etc.

I have several tables. The first is the BasicInfo Table. This table
contains the primary key "Foundation ID," as well as basic contact
information for each organization.

I then created a table called "Guidelines" in which I plan to store info
such proposal deadlines, board meeting deadlines, etc. This table contains
the Primary Key "Guidelines ID." This table also contains a field called
"Foundation ID" which I have attempted to link to the "Foundation ID" entry
in the BasicInfo table.

I would like to link these two tables together in a form so that when I
bring up the contact information for an organization, I can also see the
relevant information stored in the Guidelines Table. I have currently linked
the tables using a query (*) so that the field list displays all of the
controls which I would like to display.

However, when I go to enter new Guidelines information in Form View for a
Foundation, the form will not let me enter any Guidelines data. I assume
this means that the tables are not linked properly, or that Access is not
correctly matching the two tables Foundation IDs.

Any help you could provide would be greatly appreciated! I have been
working on this for a long time and am in desperate need of help!

 

[BruceM]

What is the connection between Foundation and Guidelines? Is there a list
of Guidelines for each Foundation?

If so, you need to create a relationship between the two tables. Click
Tools --> Relationships. In the Relationships window, add both tables.
Drag FoundationID from one table on top of FoundationID in the other. Click
"Enforce Referential Integrity". There should be a 0 on one side of the
relationship line and an infinity sign on the other. Save and close.

I will call your Foundation table tblFoundation, and your Guidelines table
tblGuidelines. The easiest thing at this point is to use AutoForm. Select
the tblFoundation in the database window, and click Insert --> Autoform.

For more control, create a form (frmFoundation) based on tblFoundation, and
another form (fsubGuidelines) based on tblGuidelines. On the property sheet
for fsubGuidelines, click the Data tab and choose Continuous as the default
view. With frmFoundation open in design view, drag the icon for
fsubGuidlelines onto it.

This is a very quick description, but it is the basic idea behind creating a
one-to-many relationship between two tables, and a form/subform as your
interface with those tables.

 

[fizban33]

Hi Bruce,

Thanks for your answer. The relationship between Foundations and Guidelines
is that each Foundation will have a set of guidelines. I would like to link
the tables together in a query so that I can place them both in the same
form, or create a guidelines subform which would be placed within another
form containing Basic Foundation information.

The problem that I am having now is that when I place the guidelines subform
on the main Foundation form, I get an error message: This object does not
contain the basic automation object "Foundation ID."

I assume that this is occuring because the Guidelines entry does not yet
exist in the guidelines table, so when I go to create it, Access does now
know which Foundation the guidelines correspond to.

Any thoughts?

Thanks again for your help!

 

[BruceM]

Hi Bruce,

Thanks for your answer. The relationship between Foundations and
Guidelines
is that each Foundation will have a set of guidelines. I would like to
link
the tables together in a query so that I can place them both in the same
form, or create a guidelines subform which would be placed within another
form containing Basic Foundation information.

The problem that I am having now is that when I place the guidelines
subform
on the main Foundation form, I get an error message: This object does not
contain the basic automation object "Foundation ID."

I assume that this is occuring because the Guidelines entry does not yet
exist in the guidelines table, so when I go to create it, Access does now
know which Foundation the guidelines correspond to.

Any thoughts?

Thanks again for your help!

 

[BruceM]

I had stopped monitoring this thread, or I would have replied sooner. Are
you trying to add pre-existing guidelines to a foundation's record, or is
each guideline record unique? In other words, are you selecting from a
pre-existing list of guidelines? It might help if you give a generic
example of a foundation and of a couple of guidelines. Is a guideline a
single field, a description, or what exactly?
Once I know the answers to these few questions I will have a better idea
what to suggest.

 

[fizban33]

Hi,

No problem about the delayed response. I appreaciate any help you can
provide.

To give an example, we have a foundation called OSI. Each Foundation has a
unique set of guidelines. Fields in the Guidelines table include "Amount of
Funding," "Can't Fund," and "Range of Funding." Each field contains either
text or numbers, and each Foundation has unique Guidelines entries in these
fields. But there is no pre-existing list of guidelines.

It also seems that a problem occurs when I try and link three tables
together in a query. For example, I tried to link the Basic Foundation
info., Guidelines Info, and a third table together using the Foundation ID
(which serves as the primary key in the Basic info table and the Foreign Key
in the others).

When I link two tables, everything works fine. But three tables seems to
complicate things.

Thanks again for your help.

 

[BruceM]

Starting from the beginning, it sounds as if you have a Foundations table
(tblFoundation) and a Guidelines table (tblGuidelines).

tblFoundation
FoundationID (PK)
Address
Phone
Other contact information

tblGuidelines
GuidelineID (PK)
FoundationID (FK)
FundingAmount (currency)
CanFund (Yes/No)
FundingRange (should probably be two currency fields for upper and lower
limits)
Other guideline information

From one of my previous postings in this thread:

Create a relationship between the two tables. Click Tools -->
Relationships. In the Relationships window, add both tables. Drag
FoundationID from one table on top of FoundationID in the other. Click
"Enforce Referential Integrity". There should be a 0 on one side of the
relationship line and an infinity sign on the other. Save and close.

Create a form (frmFoundation) based on tblFoundation, and another form
(fsubGuidelines) based on tblGuidelines. On the property sheet for
fsubGuidelines, click the Data tab and choose Continuous as the default
view. With frmFoundation open in design view, drag the icon for
fsubGuidlelines onto it. Switch to Form view, and try adding some data.

There is no need to combine the tables into a query for purposes of the
form/subform, although if you want to sort or otherwise manipulate the data
you can use a query based on a table rather than the table itself as a
form's record source.

As you have described the situation I don't see a need for a third table.

 

[jahoobob via AccessMonster.com]

Sounds to me as though you are trying to link these table on a one to one
basis, ie OSI has one record of info in the Foundatuon table and one record
of info in the Guidelines table. If this is the case combine the data in the
guidelines table with the foundation table. You should not split up data
just to split it up.
If there is more than one record of guidelines for each record of foundation,
then what Bruce says.

Hi,

No problem about the delayed response. I appreaciate any help you can
provide.

To give an example, we have a foundation called OSI. Each Foundation has a
unique set of guidelines. Fields in the Guidelines table include "Amount of
Funding," "Can't Fund," and "Range of Funding." Each field contains either
text or numbers, and each Foundation has unique Guidelines entries in these
fields. But there is no pre-existing list of guidelines.

It also seems that a problem occurs when I try and link three tables
together in a query. For example, I tried to link the Basic Foundation
info., Guidelines Info, and a third table together using the Foundation ID
(which serves as the primary key in the Basic info table and the Foreign Key
in the others).

When I link two tables, everything works fine. But three tables seems to
complicate things.

Thanks again for your help.

I had stopped monitoring this thread, or I would have replied sooner. Are
you trying to add pre-existing guidelines to a foundation's record, or is

[quoted text clipped - 97 lines]

 

[BruceM]

I agree. I took "set of guidelines" to mean several guidelines for each
Foundation. If there is just one, that's a different story.

Sounds to me as though you are trying to link these table on a one to one
basis, ie OSI has one record of info in the Foundatuon table and one
record
of info in the Guidelines table. If this is the case combine the data in
the
guidelines table with the foundation table. You should not split up data
just to split it up.
If there is more than one record of guidelines for each record of
foundation,
then what Bruce says.

Hi,

No problem about the delayed response. I appreaciate any help you can
provide.

To give an example, we have a foundation called OSI. Each Foundation has
a
unique set of guidelines. Fields in the Guidelines table include "Amount
of
Funding," "Can't Fund," and "Range of Funding." Each field contains
either
text or numbers, and each Foundation has unique Guidelines entries in
these
fields. But there is no pre-existing list of guidelines.

It also seems that a problem occurs when I try and link three tables
together in a query. For example, I tried to link the Basic Foundation
info., Guidelines Info, and a third table together using the Foundation
ID
(which serves as the primary key in the Basic info table and the Foreign
Key
in the others).

When I link two tables, everything works fine. But three tables seems to
complicate things.

Thanks again for your help.

I had stopped monitoring this thread, or I would have replied sooner.
Are
you trying to add pre-existing guidelines to a foundation's record, or
is

[quoted text clipped - 97 lines]

Any help you could provide would be greatly appreciated! I have
been
working on this for a long time and am in desperate need of help!