This seems to come up a lot and i am really frustrated.

I have a fairly large table with a primary key of SKU
certain SKU values share multiple producers, categories, ect.
I have a table set up Many2Many to deal with these.

They are attached via the SKU and the PRODUCERID, ect.
I would like to take data like PRODUCERID and integrate into a single query for editing

Here lies the problem: Every time i integrate more than 1 table into a query Access either turns off the "add new item icon" or doesn't show the data at all.

I dont understand this.
I have attached a pic of my relationships and the query in question.
What am i doing wrong.

You can't use a single query that contains more than one 1-to-many relationship (unless the relationship is hierarchical) to produce an updateable recordset. In fact if you look at the data returned it probably won't make any sense since it will contain what looks like duplicate data. The reason is that the SKU table is related to the Producer table and the SKU table is also related to the Categories table but, the Producer table and the Categories table have no relationship to each other. Your scheme looks like:

SKU --> Producer
SKU --> Category

A hierarchial relationship such as:

Customer --> Order --> OrderDetails

would be updateable.

Your query will produce rows that look like:

SKU1, Producer1, Category1
SKU1, Producer1, Category2
SKU2, Producer2, Category1
SKU2, Producer2, Category2

Notice the seeming duplication of producer and category.

Use a main form with a query based on SKU and two subforms. One for each of the two related tables.

Thanks for your input.

The reasoning for my structure is that one SKU can have multiple producers. Thinking about this more, i reallly dont (think that i) need multiple row values for each producer

SKU(s) have ProducerID1, ProducerID2, Producer3.... but not that often. Is it possible, (or a good idea) to "point" ProducerID1 and ProducerID2 at the same set of data values (my Producer table) instead of making 2 seperate rows of information keeping it as one.

I would rather have them listed together in a single row Producer1, Producer2, Producer3 < but pull values from 1 common table.

I am coming from and am used to using Excel but have grown out of it, i am having a hard time understanding "how this could all come together" in a cohesive manor for updating / sorting the information.

Does this Relationship stucture seem appropriate for my needs. If not, what books, resources, techniques would you recommend. I've purchase several books already: "Running Microsoft Access -- Microsoft", and another aimed at spotting and fixing errors (and will probably buy a few more.) Please, if you could point me in the right direction if you know of one.

Thank you for your time.

If an SKU can have multiple producers, you really need the 1-to-many relationship. The three table structure that you proposed is the correct way to handle the two 1-to-many relationships. Putting multiple producer fields in the SKU table would be a step backward since you would be attempting to emulate a spreadsheet in a relational table. Relational tables are designed differently from spreadsheets and you need to change your way of thinking about data to work effectively with them.

Search the MSDN site for "normalization". Microsoft has several good articles on the subject. There is another good article that is geared toward former spreadsheet users at www.fmsinc.com. Be sure to check out their other articles whil you are there.