Share via

change from one to one to one to many

Anonymous
2023-09-02T11:05:31+00:00

I am a newbie to Access. Have been using Alpha Software for 30 years but the software gets buggy after some years and I always end up having to upgrade. Tired of paying their high prices, so here I sit in front of Access LOL!

I have created five tables, customer, patient, linitm and two separate price lists for different customers. No records have been entered. The only field rules so far are for "patient" which are "required" and "unique." I am trying to join the main table called "patients" to "linitm" with the connecting field in both tables called "caseno." "Linitm" will contain multiple products for the same order. On the relationships screen I have successfully joined the two tables, but it will only create a one to one relationship. I have tried changing the selections in the join type pop up but it has no effect. Not sure what they mean anyhow. So how do I change it to a one to many? This will be simple invoicing program with monthly statements for a dental lab.

Second option would be for some generous soul to point me to such program I can modify to suit my needs. Creating a new program for my business at 72 years of age is going to be a nightmare for me. Everything I can find on the net requires a cloud subscription with a monthly fee. SHEESH!

Microsoft 365 and Office | Access | For home | 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. ScottGem 68,810 Reputation points Volunteer Moderator
    2023-09-02T12:09:36+00:00

    First, check whether the field you re joining on is set to unique on the many side of your relationship. That is usually why Access would force it to be one to one. The case number field should be a non unique foreign key since "Linitm will contain multiple products for the same order.". I'm assuming here, that a case is equivalent to an order.

    As for a program, to do this, you would have to explain first, what you want the app to do. If the main basis is order entry, you might want to take a look at the new Northwinds template for ideas.

    2 people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2023-09-03T20:37:33+00:00

    I am wondering if the tables need to have data in them to pickup the proper relationship?

    The only thing which can define a relationship type as one-to-one is that the primary and foreign keys of the tables are each a candidate key of their respective table, i.e. in each table the column in question must contain distinct values.  This results form the column being indexed uniquely, which might be by virtue of being defined as the primary key, or might be by being indexed independently to disallow duplicates.  These are properties of the table definition, and whether or not the table contains data is irrelevant.

    I'm suspicious about your attempt to create a relationship between Patients and LineItems.  The usual model in this sort of situation would be like this:

    Customers---<Orders----<LineItems>---Products

    LineItems resolves the binary many-to-many relationship type between Customers and Products into two one-to-many relationship types.

    You'll find many examples of models like this in sample databases, including Northwind and my own DatabaseBasics.zip, which you can find in my public databases folder at:

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

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-09-02T12:27:17+00:00

    I want to print invoices and end of the month statements. The linitm table caseno is not set to unique. I am wondering if the tables need to have data in them to pickup the proper relationship? In the mean time I'll check out the Northwinds template. Thank you...

    0 comments