Linking Tables

[Guest]

I need to create an inventory database. For some reason I just can not
understand access. It is so confusing to me. I want to create a report so on
Friday I can see what is left in our warehouse, if I should place new orders
or just to update my customer what is in stock. I downloaded inventory
database but it is not working for me and I would feel so much better if I
could create my own database. I like the form they use and want to do exactly
that. I do not have a lot of part numbers or suppliers we are a small
business. Could someone please tell me where I need to go from here:
3 tables so far.......
Categories
Product
Suppliers
I am stumped on how to link tables. In my products table I have a column
called Category ID isn't this how I link the two tables?
Thanks,

 

[ruralguy via AccessMonster.com]

FWIW, I would recommend continuing to modify the downloaded db until it does
what you want it to do. It is probably one of the best ways to learn Access
along with reading these forums.

 

[Steve]

Your tables should look like:
TblCategory
CategoryID
Category

TblProduct
ProductID
ProductName
CategoryID

TblSupplier
SupplierID
SupplierName

TblSupplierProduct
SupplierProductID
SupplierID
ProductID

Open to the database window and go to tables. Click on the Relationships
button in the menu at the top of the screen. In the Show Table dialog that
appears, add all your tables to the relationships window. Now cretae your
relationshops:
1. Click and Drag CategoryID from TblCategory and drop it on CategoryID in
TblProduct. Check Referential Integrity.
2. Click and Drag SupplierID from TblSupplier and drop it on SupplierID in
TblSupplierProduct. Check Referential Integrity.
3. Click and Drag ProductID from TblProduct and drop it on ProductID in
TblSupplierProduct. Check Referential Integrity.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Thank you for the response. I am getting a message when I try to click and
drag that says,
"Relationship must be on the same number of fields with the same data types."
Please let me know what I am doing wrong!
Thanks,

 

[Steve]

CategoryID in Tbl TblCategory, ProductID in TblProduct, SupplierID in
TblSupplier and SupplierProductID
in TblSupplierProduct should be autonimber data type. CategoryID in Tbl
TblProduct, SupplierID
in TblSupplierProduct and ProductID in TblSupplierProduct should be Number -
Long Integer data type. Check all these. When you click and drag, be sure to
select the precise field in my instructions and drop it on the precise field
in my instructions.

If you still get the same message, post your tables.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

It created a relationship but it will not let me Enforce Referential Integrity.
It says"No unique index found for the referenced field of the primary table.
My tables
TblCategory
CategoryID
Category

TblProduct
ProductID
Product Name
CategoryID

TblSupplier
SupplierID
Supplier Name

TblSupplierProduct
SupplierProductID
SupplierID
ProductID

It created lines from Category to Product
Product to SupplierProduct
Supplier to SupplierProduct

This is so frustrating, and I guess it gets harder from here, huh? I really
want to be able to do this please don't give up on me : )
Thanks,

 

[Arno R]

It created a relationship but it will not let me Enforce Referential Integrity.
It says"No unique index found for the referenced field of the primary table.

We guess that you need to create the needed unique index...

==> Make sure the referenced field is a Primary Key field.
In table design click on the 'key' in the toolbar.

BTW: an inventory database is not at all that easy!
I guess that you are going to need much more help on this, but please beware of this guy Steve !!
Steve is a notorious job hunter in these groups, always trying to sell his services.
http://home.tiscali.nl/arracom/whoissteve.html

HTH
Arno R

 

[ruralguy via AccessMonster.com]

Are your fields set up as the DataTypes I've described here?
TblCategory
CategoryID PrimaryKey (PK) AutoNumber
Category

TblProduct
ProductID (PK) AutoNumber
Product Name
CategoryID ForeignKey (FK) Long Integer

TblSupplier
SupplierID (PK) AutoNumber
Supplier Name

TblSupplierProduct
SupplierProductID (PK) AutoNumber
SupplierID (FK) Long Integer
ProductID (FK) Long Integer

It created a relationship but it will not let me Enforce Referential Integrity.
It says"No unique index found for the referenced field of the primary table.
My tables
TblCategory
CategoryID
Category

TblProduct
ProductID
Product Name
CategoryID

TblSupplier
SupplierID
Supplier Name

TblSupplierProduct
SupplierProductID
SupplierID
ProductID

It created lines from Category to Product
Product to SupplierProduct
Supplier to SupplierProduct

This is so frustrating, and I guess it gets harder from here, huh? I really
want to be able to do this please don't give up on me : )
Thanks,

CategoryID in Tbl TblCategory, ProductID in TblProduct, SupplierID in
TblSupplier and SupplierProductID

[quoted text clipped - 78 lines]