Table Relationships

[tomanddani via AccessMonster.com]

I have three tables: BusinessContacts, IndividualContacts, and Donations.
BusinessContacts has a one to many relationship with Donations (Donations
being the many side). IndividualContacts has a one to many relationship with
Donations (Donations being the many side).
BusinessContacts table includes; BusinessID as the primary key. (autonumber)
IndividualContacts table includes; IndividualContactsID as the primary key.
(autonumber)
Donations table includes; DonationsID as primary key (autonumber), BusinessID
as foreign key (number), and IndividualContactsID as foreign key (number).

I would like to be able to add donation info to business contacts OR
individual contacts. Business contacts and Individual contacts are not
related except that they may both make donations. When I run a query to make
a form to input say a business contact and his donation it will not let me
save the record because I did not input a individualcontactID. How should I
relate these tables?

 

[Jeanette Cunningham]

My suggestion is to have a single table for Contacts. In the Contacts table
have a field that indicates the type of contact - business, or individual.
This will make the queries simpler.

Jeanette Cunningham

 

[Armen Stein]

My suggestion is to have a single table for Contacts. In the Contacts table
have a field that indicates the type of contact - business, or individual.
This will make the queries simpler.

Jeanette Cunningham

I have three tables: BusinessContacts, IndividualContacts, and Donations.
BusinessContacts has a one to many relationship with Donations (Donations
being the many side). IndividualContacts has a one to many relationship
with
Donations (Donations being the many side).
BusinessContacts table includes; BusinessID as the primary key.
(autonumber)
IndividualContacts table includes; IndividualContactsID as the primary
key.
(autonumber)
Donations table includes; DonationsID as primary key (autonumber),
BusinessID
as foreign key (number), and IndividualContactsID as foreign key (number).

I would like to be able to add donation info to business contacts OR
individual contacts. Business contacts and Individual contacts are not
related except that they may both make donations. When I run a query to
make
a form to input say a business contact and his donation it will not let me
save the record because I did not input a individualcontactID. How should
I
relate these tables?

I agree with Jeanette - that would be simpler and better database
design.

But to answer your question: Access defaults numeric fields to zero,
which isn't a valid foreign key for an AutoNumber. That is a common
reason for your problem.

In your table design for Donations, set both foreign key Defaults to
Null. (You can also just delete out the 0 to get a Null, but actually
specifying Null is clearer).

Also, you'll need to make sure that neither foreign key is set to
Required.


Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[tomanddani via AccessMonster.com]

Thanks for your reply. Your right that would solve the problem. But, I also
wanted to track the business information in a table called tblBusiness. That
table is related to the business contacts in a one to many. The business
contacts could be many for one business.

If I were to have one contact table for individuals and businesses how could
I associate a business with that table. I would need to be able to input
individual contact information without adding an associated business. If I
only had business contacts then it would work but the individual contacts
that arn't connected to a business make it difficult.

I have four tables;
tblBusiness - BusinessID(PK), BusinessName
tblBusinessContacts - BusinessContactID(PK), BusinessID(FK), ContactName
tblIndividualContacts - ContactID(PK), ContactName
tblDonations - DonationID(PK), BusinessID(FK), ContactID(FK), DonationType

Table tblBusinessContacts is related to tblBusiness, one to many. Table
tblBusiness is related to tblDonation, one to many. Table
tblIndividualContacts is related to tblDonation, one to many.

Thanks for the help, I know what I have is not right.

Tom

 

[tomanddani via AccessMonster.com]

I tried what you said but I still get a message stating that access cant find
a matching record in table IndividualContacts with key matching fields in
tblDonations.ContactID. I relize what is going on, but i had hoped that there
was some way for tblBusiness and tblIndividualContacts to both store donation
records in tblDonations. The problem is that sometimes the donations are from
businesses and sometimes from individuals.

Is there no other way to relate these tables?

 

[Jeff Boyce]

It seems to me you have "Contacts", with ContactName and Type (Business or
Individual).

One Contacts table...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Armen Stein]

I tried what you said but I still get a message stating that access cant find
a matching record in table IndividualContacts with key matching fields in
tblDonations.ContactID. I relize what is going on, but i had hoped that there
was some way for tblBusiness and tblIndividualContacts to both store donation
records in tblDonations. The problem is that sometimes the donations are from
businesses and sometimes from individuals.

Is there no other way to relate these tables?

What you are trying to do is possible. There is something wrong with
the way your tables and relationships are set up.

In the Donations table, make sure that neither foreign key (BusinessID
or IndividualContactID) is Required or has a Default value.

Make sure that your relationships window shows a relationship line
from BusinessID in the Business table to the _BusinessID_ foreign key
field in tblDonation.

Also make sure that it shows a relationship line from
IndividualContactID in tblIndividualContacts to the
_IndividualContactID_ field in tblDonation.

This should work. Open the Donation table directly and try to create
a new record. You should see that you can put a valid ID into either
foreign key field, and leave the other one blank (null).

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Jamie Collins]

Access defaults numeric fields to zero,
which isn't a valid foreign key for an AutoNumber.

Why do you say that? Do you know of anything in the random AutoNumber
algorithm which guaranteed the auto-generated value will not be zero?
What if an incremental AutoNumber was seeded to begin at a value less
than (or equal to) zero? For both flavours of algorithm, an explicit
value of zero can be inserted into the AutoNumber column.

Here's some Jet SQL code (ANSI-92 Query Mode syntax) which shows a
foreign key value of zero which was auto-generated:

CREATE TABLE Test1 (
Test1_ID INTEGER IDENTITY(0, 1) NOT NULL UNIQUE,
Test1_data_col INTEGER NOT NULL)
;
CREATE TABLE Test2 (
Test1_ID INTEGER REFERENCES Test1 (Test1_ID),
Test2_ID INTEGER IDENTITY(0, 1) NOT NULL UNIQUE,
Test2_data_col INTEGER NOT NULL)
;
CREATE VIEW Test1Test2
AS
SELECT T1.Test1_ID, T1.Test1_data_col,
T2.Test1_ID, T2.Test2_ID, T2.Test2_data_col
FROM Test1 AS T1
INNER JOIN Test2 AS T2
ON T1.Test1_ID = T2.Test1_ID
;
INSERT INTO Test1Test2 (Test1_data_col, Test2_data_col)
VALUES (55, 99)
;
SELECT T1.Test1_ID, Test1_data_col, T2.Test1_ID, Test2_ID,
Test2_data_col
FROM Test1Test2
;

As a courtesy, here's some VBA to create an mdb and demo the above:

Sub ZeroValueAutoNumberFK()
' Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String
Sql = _
"CREATE TABLE Test1 (" & vbCr & "Test1_ID" & _
" INTEGER IDENTITY(0, 1) NOT NULL" & _
" UNIQUE, " & vbCr & "Test1_data_col INTEGER" & _
" NOT NULL)"
..Execute Sql
Sql = _
"CREATE TABLE Test2 (" & vbCr & "Test1_ID" & _
" INTEGER REFERENCES Test1 (Test1_ID)," & _
" " & vbCr & "Test2_ID INTEGER IDENTITY(0," & _
" 1) NOT NULL UNIQUE, " & vbCr & "Test2_data_col" & _
" INTEGER NOT NULL)"
..Execute Sql
Sql = _
"CREATE VIEW Test1Test2 " & vbCr & "AS " & vbCr & "SELECT" & _
" T1.Test1_ID, T1.Test1_data_col," & _
" " & vbCr & "T2.Test1_ID, T2.Test2_ID, T2.Test2_data_col" & _
" " & vbCr & "FROM Test1 AS T1 " & vbCr & "INNER JOIN" & _
" Test2 AS T2 " & vbCr & "ON T1.Test1_ID =" & _
" T2.Test1_ID"
..Execute Sql
Sql = _
"INSERT INTO Test1Test2 (Test1_data_col," & _
" Test2_data_col) " & vbCr & "VALUES (55," & _
" 99)"
..Execute Sql
Sql = _
"SELECT T1.Test1_ID, Test1_data_col," & _
" T2.Test1_ID, Test2_ID, Test2_data_col" & _
" " & vbCr & "FROM Test1Test2"
Dim rs
Set rs = .Execute(Sql)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Tony Toews [MVP]]

But to answer your question: Access defaults numeric fields to zero,
which isn't a valid foreign key for an AutoNumber. That is a common
reason for your problem.

Actually in A2007 I think the standard default is null.

In your table design for Donations, set both foreign key Defaults to
Null. (You can also just delete out the 0 to get a Null, but actually
specifying Null is clearer).

Minor nitpick but I've been leaving that field blank for about 12 or
14 years now.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[John W. Vinson]

Actually in A2007 I think the standard default is null.

FINALLY, to the great relief of Access developers everywhere, some of whom
have been ranting about this previous mistake for years.

Minor nitpick but I've been leaving that field blank for about 12 or
14 years now.

Same here. Don't see that it would make too much difference, but NULL would
certainly work just as well and might be more self-documenting.

John W. Vinson [MVP]

 

[Armen Stein]

FINALLY, to the great relief of Access developers everywhere, some of whom
have been ranting about this previous mistake for years.

Yes, that's great. But we'll still need to watch for this problem for
the next few years on older versions.

Same here. Don't see that it would make too much difference, but NULL would
certainly work just as well and might be more self-documenting.

That's why I specify Null - it's more clear that leaving it blank. No
difference otherwise.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com