Need help creating relationships between tables.

[rebfein]

Hi there,



I'm a Tech writer, who last used Access XP a VERY long time ago. I've been tasked with creating an Access DB project involving inventory for my boss.

I've spend two months creating in Excel (per the boss) the main table. I've imported the information into Access and created five very pretty tables (company p/n, p/n description, categories, vendor p/n, vendor components, company p/n descriptions).

I'm trying to establish relationships between the tables, each has a primary key field (Index set to "Yes, no duplicates"). I can establish a one-to-one relationship with the drag and drop method, but when I try to establish a one to many relationship from one table to the next, I get a one to one relationship. How do I get a one to many relationship?


Steps taken:

1. Drag ID from one table to the next.
2. In the Edit relationships box comes up, I click the 'Enforce...' check box.
3. Click the Join Properties button and select option 3 and click OK.
4. At the Edit Relationships screen, and press the Create button.

[June7]

I see 6, not 5, table names.

What are the PK/FK fields in each table?


Advise no spaces and special characters/punctuation (underscore is exception) in naming convention.

[orange]

Further to June's comment, spreadsheet and database are different animals. Each tables is about 1 subject; they are related based on Business rules (too many people think they can make up relationships at will).

Consider (remove space and special characters from names)
companyPartNo, PartNoDescription, Categories, VendorPartNo, VendorComponents, CompanyPartNoDescriptions

--readers (and you) need a brief description of what these things represent-sufficient to know something is in 1 table and not possibly in another, or 2 or 3
--it would be helpful to readers, and critical to you, to know in simple business terms where this things fit. A description of a typical day at the office will do wonders to communicate your ebnvironment to others.


I see CompanyPartNo - but I have to ask what is Company and how does PartNo fit? Or is it Part and PartNo is an attribute of Part.
A company builds 1 or many PartNos (Parts)
1 Part may be built by 1 or more Companies
so it appears to be a potential Many to many relationship. This situation is resolved by a Junction table.

It seems PartNoDescription could be PartDescription
Categories is extremely generic, but will have meaning in your context once that is established
VendorPartNo -- again I see Vendor and Part perhaps masking another entity/table??
Vendor (Seller of your Parts??, or Supplier to your company??)

VendorComponents --- seems like various assemblies of Parts, each of which may be sold as a unit
So it's like Bicycle with wheels, tires, spokes, hubs...?

Have to go at the moment , but food for thought.

Good luck.