NEW to Access - Help Please!!!

[sunshine]

I am trying to build a small database to help me understand how tables work together in Access. I am using an example of a library database with three tables: Borrower, Loan and Book tables.

When I am creating the tables in design view I am unsure which data type to use. I have tried many different combinations and i keep getting a type JOIN mismatch error when I try to build forms.

I have the borrower set up as the main table with the borrowerID being the primary key set up as autonumber property.

When it comes to setting up my next 2 tables I am not sure if I need to use an autonumber property for the primary keys for these tables as well or if I just use the number property and let access assign the numbers for those tables.

If my question seems a little jumbled I'm sorry as I said I am a new user and I am trying to stumble by on my own!

Thanks for any help!
Sunshine

 

[Corticus]

Hi Sunshine (w/musical accompaniment)!

Regarding joins first,
If you want to join on a field, the fields must be of the same data type. If your Borrower table uses an autonumber as your key, then if you have a related table joined to this table on the ID field, then the related table's field must be a number field.

Here's how I imagine your tables,

Borrower:
BorrowerID/Name
(autonumber/text)
1/Bob
2/Joe etc...

Loan:
LoadID/BorrowerID/BookID/date
(autonumber/number/number/date)
1/1/1/01-01-03

Book:
BookID/Name
(autonumber/text)
1/Hamlet
2/The Tempest

From Tools|Relationships, drag the autonumber field from the borrower table to the Number field for borrowerID on the loan table, and drag the autonumber field from the book table to the bookID field on the load table.

Check 'enforce referential integrity' if you want to insure that every record in you related tables (such as the bookID and borrowerID in the loan table) has a record in the original table. In other words, you couldn't have a loan in out for bookID 3, but not have a bookID 3 in the book table.

Check 'cascade update related records' if you want changes in your main table to be reflected in the related tables.

Check 'cascade delete related records' if you want a deletion of a record from its main table to result in that record being deleted from any related tables.

HTH,