+ Reply to Thread
Results 1 to 6 of 6

Multiple relationships in the same table

  1. #1
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    185

    Multiple relationships in the same table

    Hello,

    I just want to check if there is a better way to do this

    i have 1 table called Categories, and 1 table called parts. I want to have another table called partcat that will list all the parts and what categories they are in.

    I want to make the parts available in multiple categories.

    This means i would have multiple field names in the partcat table with the same name with increment of 1 for example partcat1 and partcat2 and partcat3.

    As i would be creating a relationship for each instance, as the part could be in many categories.

    This way seems messy and could get confusing

    Is there a better way to do this in access?

    Any help would be much appreciated

  2. #2
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Kaysville, UT
    MS-Off Ver
    Office 365
    Posts
    118

    Re: Multiple relationships in the same table

    Use the power query functions in excel to build connections to your two tables. Then you can build relationships at will and combine and display using any pivot process you want. Upload an example of the data and I will provide with an example.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,260

    Re: Multiple relationships in the same table

    In a query, join the two tables in a outer join on the common field. You can then, if you wish, convert the Select Query to a Make Table Query.

  4. #4
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    185

    Re: Multiple relationships in the same table

    Hello Yeah I have attached a sample file.

    I thought this morning that making 3 fields the primary key I could than add the different categories into the same field than separate fields.

    Is that a better way, as it would make the form easier as the drop down would be simpler.

    Many thanks for your help

    I didn't see that the file is invalid
    Attached Images Attached Images
    Last edited by UltimateNeo; 05-12-2020 at 08:01 AM.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,260

    Re: Multiple relationships in the same table

    There is no file attached.

  6. #6
    Forum Contributor
    Join Date
    01-06-2019
    Location
    Australia
    MS-Off Ver
    2019
    Posts
    185

    Re: Multiple relationships in the same table

    Sorry I have attached the file now, did not realise it was a invalid format.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. relationships between table is needed
    By paxonltd in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-13-2019, 11:47 PM
  2. [SOLVED] Relationships not creating correct results in pivot-table.
    By saudi_red_neck in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-07-2018, 06:55 AM
  3. Pivot table Relationships
    By danieljephcott in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-16-2017, 10:52 AM
  4. Pivot table relationships between source table data
    By LizzyJ in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 04-13-2015, 09:56 AM
  5. PowerPivot Multiple Relationships
    By amotto11 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-23-2011, 12:24 PM
  6. Calculating relationships in a table
    By daddylonglegs- in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2010, 09:27 AM
  7. Table relationships in Excel?
    By saturnius in forum Excel General
    Replies: 3
    Last Post: 01-26-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1