Share via

Is there any way to do 0-to-1 or 0-to-many relationships in access?

Anonymous
2023-10-03T03:15:14+00:00

Edit:

I have added in a picture below of what I want to create a relationship for in access incase my question is not clear.

I want to create a relationship between products & product categories as each category can have 0 to multiple products under the category, so I would need to do a 0-to-many relationship.

Microsoft 365 and Office | Access | For education | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2023-10-03T12:32:36+00:00

    This is just a normal one-to-many relationship type.  The requirement of an enforced relationship type is that there cannot be a row in the referencing table without a matching row in the referenced table, i.e., in your case, there cannot be a row in Products without a matching row in ProductCategories.  However the reverse is not the case, so it is legitimate to have a row in ProductCategories without any matches in Products.

    There might be situation, however, where a row in Products would not appropriately have a matching row in ProductCategories.  The solution in this case would be to have a row such as the following in ProductCategories

    ProductCategoryID     ProductCategory

    0                                N/A

    You might be wondering how this is possible if the primary key ProductCategoryID column is an autonumber.  It is a common misconception that the value in an autonumber column cannot be set manually.  In this case it would be done by executing the following 'append' query:

    INSERT INTO ProductCategories(ProductCategoryID, ProductCategory)

    VALUES(0, "N/A");

    For products where a category is inappropriate the ProductCategoryID foreign key column would be give a value of zero.

    Of course, having a value of zero for the N/A row in ProductCategories is not actually necessary.  You could allow the autonumber mechanism to give it an arbitrary value and then use that value in Products where a category is inappropriate.  Using zero is a common convention, however.

    2 people found this answer helpful.
    0 comments

Answer accepted by question author

  1. George Hepworth 22,765 Reputation points Volunteer Moderator
    2023-10-03T12:31:40+00:00

    I agree with Hans; there is no "0 to many" relationship.

    You want categories which may not have any products in them. However, other categories can have 1 or more products in them.

    That's a 1 to many relationship. "many" is understood to mean "0 or more than 0". You're fine without having to come up with something new to describe it.

    2 people found this answer helpful.
    0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-10-03T13:31:51+00:00

    It sounds to me like you might have a type hierarchy here.  This is where an entity (an employee in your case) can be of more than one entity type (salespersons and cashiers in your case, each of these being a sub-type of type employees). For this salespersons and cashiers would be the referencing tables in one-to-one relationship types with employees.  A sub-type is characterised by sharing all of the attributes of its (super) type but not those of other sub-types, so employees would have columns representing attributes which are common to all employees, while salespersons and cashiers would each have columns representing attributes specific to the sub-type.

    You might like to take a look at TypeHierarchy.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file uses a simplified model for a hypothetical academic institution in which a person might be an employee or student, or both.  The model is as below. Note that there are two instances of the AdministrativePersonnel table in the model, not two separate tables. This is because an admin employee can be the line manager of another admin employee:

     ![Image](https://learn-attachment.microsoft.com/api/attachments/18742be6-d05a-4409-be81-a0bda037c830?platform=QnA

  2. George Hepworth 22,765 Reputation points Volunteer Moderator
    2023-10-03T13:03:26+00:00

    No, this is a many-to-many relationship. One person can fill many roles. One role can be filled by many people.

    This requires three tables.

    Person (employee)

    Role

    PersonRole

    The third table, "PersonRole" has one record for each role held by a person.

    In your scenario, the Primary Key for "PersonOne" will appear in two records, one will have the Primary Key for "Cashier" and one will have the Primary Key for "Salesperson". You can add additional fields, such as "DateStartedRole" to indicate when the person was given the Cashier duties.

    In this third table, often called a Junction table, the Primary Keys for the two other related records are referred to as "Foreign Keys".

    Ken is in the thread, and I know he's good at providing such details, so I hope he'll add to this discussion as well.

    1 person found this answer helpful.
    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-10-03T07:12:04+00:00

    What would a 0-to-1 or 0-to-many relationship be? In 28 years of using Microsoft Access I've never heard of those...

    0 comments