Multiple relationships in the same table

[UltimateNeo]

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

[DavidBowman]

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.

[alansidman]

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.

[UltimateNeo]

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

[alansidman]

There is no file attached.

[UltimateNeo]

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