newbie help

[Paul De Lagrave]

I am new to access and am trying to develop a returned check database for my
business. I have three table that I am trying to link together so that I am
also able to view the corresponding information from whatever table I am
looking at. The three tables I have are Contact, Checks and Reference and
they all have the prefix tbl in the name.

The contents of tblContact:
ID (autonumber-Primary Key)
First Name
Last Name
Address
Apartment
City
State
Zip Code
Driver's License

Content for tblChecks:
ID (autonumber Primary Key)
Check #
Check Amount
Date
Bank Reference
Sent to Collections
Check Paid

Content of Reference:
Categories (Primary Key)

I then have ID from Contacts linked to ID from checks and if the customer
has more than one bad check, the only way for me to add that woudl be turn
off the primary key in tblChecks. I also have Bank Reference linked to
Categories from tblChecks to tblReference and that works fine. I assume that
the way I have tblContact and tblChecks is wrong since I have autonumber for
each of them and each is individual that I can't have more than one check
for each entry. How would I go about to resolve this or how should I link
the tables to get the relationships to work?

I have been told to remove the primary key from one of the tables or to
change the autonumber to number, someone else told to make a foreign key... I
am not on how to do this, could someone explain in detail how I could make
this datebase into a functioning database.

Thank you

 

[Lynn Trapp]

Paul,
I'm assuming that it's possible for each contact to have more than one
returned check. If that's the case then your setup should be something like
this, with the tables related on ContactID.

The contents of tblContact:
ContactID (autonumber-Primary Key)
First Name
Last Name
Address
Apartment
City
State
Zip Code
Driver's License

Content for tblChecks:
CheckID (autonumber Primary Key)
ContactID (Foreign Key)
Check #
Check Amount
Date
Bank Reference
Sent to Collections
Check Paid


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Paul De Lagrave]

How do I make a foreign key?

Paul,
I'm assuming that it's possible for each contact to have more than one
returned check. If that's the case then your setup should be something like
this, with the tables related on ContactID.

The contents of tblContact:
ContactID (autonumber-Primary Key)
First Name
Last Name
Address
Apartment
City
State
Zip Code
Driver's License

Content for tblChecks:
CheckID (autonumber Primary Key)
ContactID (Foreign Key)
Check #
Check Amount
Date
Bank Reference
Sent to Collections
Check Paid


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Lynn Trapp]

You simply add the field to the child table and then, in the Relationships
window, relate the 2 tables together.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html