Is possible to create a continuios form that will display...
Assortments, under each assorment show the items that make up that assortment, and then under the item would be the parts that made up that item, (Ive tried a left join, but if an item doesnt have an assortment it still needs to be displayed )(also if i try to put a subform in the form, only one can be continuious

ok here are the tables (only the fields pertaning to the querry) in question

ASSORTMENTS
[ASST_ID](PK)
[ASST_NUM]
[ASST_DISC]


ITEMS
[ITEM_ID](PK)
[ITEM_NUM]
[ITEM_DISC]
[ASST_ID](PK)

ITEM PARTS
[PART_ID](PK)
[PART_DISC]
[ITEM_ID](FK)

The problem I see is your statement "an item doesnt have an assortment it still needs to be displayed" which effectively kills the JOIN I would have suggested.

You might make this easier by running a query from time to time to identify items that have no associated assortment and make a single assortment out of them.

However, unless there is something I don't see right away, it looks like you could have a problem if the same item could ever appear in more than one assortment.

If it does not, you could create a zeroth assortment (a sort of default case), then do a JOIN on ASST, ITEM, PART and do the required GROUP BY operation in a very straight-forward manner.

Other than that, I don't see how to approach the problem because of the no-assortment item display requirement. It sort of runs in the opposite direction of the normal grouping.

WOW!!

Thank you first for identifying the problem!

The PDM (Product Data Manager) we use is Metaphase (an EDS product that sits on Oracle). If you are not familiar with it, it is an Incredible drag and drop Product Data Manager. That allows you to have a bunch of Item Parts, and when you create an item you can go to your list of parts and drag&drop them on the Item they relate to. Same applies to an assortment.


My problem is that it is programmed (from what I can see) to handle relationships at a cellular level rather than by field, I could be wrong.

I think I might have to do it all in one table and have a field that specifies what type of entry it is, and another field to relate it to its Item/Assortment.

Then I still have the problem of the item that is in multiple assortments, and the part that is in different Items

Thanks for your time, and help. If anyone knoow anything about METAPHASE please share

~Eric


[This message has been edited by kawter (edited 11-19-2001).]

Your follow-up description is worse than I thought. You have a potential many-to-many relationship from the looks of it.

If I were to program this myself, it would take at least 5 tables.

ASSORTMENTS: ASST_ID (PK), ASST_NUM, ASST_DISC

ITEMS: ITEM_ID (PK), ITEM_NUM, ITEM_DISC

PARTS: PART_ID (PK), PART_DISC (what, no PART_NUM ?)

ASST_ITEMS: ASST_ID (FK), ITEM_ID (FK)

ITEM_PARTS: ITEM_ID (FK), PART_ID (FK)

Then, you have separate descriptions of each assortment, item, or part. You also have a list of combinations that show all items that are part of an assortment and another list of parts that belong with an item.

You can then join the ASST_ITEMS table and ITEM_PARTS table through ITEM_ID to get the groupings you need and can join with the other tables to get the descriptive information.

But you still need an assortment that is all items not in any other assortment. And you need an item that lists any parts not in any other item, so you have a couple of queries to consider there.

Which implies a query to delete all ASST_ITEMS that are the "default" assortment and all ITEM_PARTS that are the "default" item, then queries to identify parts not in other items and to identify items not in other assortments. So that you can move all "dangling" items or parts to the "catch-all" assortment or item.

Hey Mr DOC

Sorry if you read this before my updates, it was a lil messy

Here is how i have written the Access/SQL querry to show me the Items. Think it will work?


SELECT NZ([ASSORTMENT_ITEMS].[ASST_ID],10) AS ASST_ID,
NZ([ASSORTMENT_ITEMS].[TOY_ID],12) AS TOY_ID,
NZ([ASSORTMENTS].[ASST_NUM]," ") AS ASST_NUM,
NZ([ASSORTMENTS].[ASST_DISC]," ") AS ASST_DISC,
NZ([TOYS_IN_PACKAGE].[TOY_NUM]," ") AS TOY_NUM,
NZ([TOYS_IN_PACKAGE].[TOY_DISC]," ") AS TOY_DISC

FROM TOYS_IN_PACKAGE INNER JOIN (ASSORTMENTS INNER JOIN ASSORTMENT_ITEMS ON [ASSORTMENTS].[ASST_ID]=NZ([ASSORTMENT_ITEMS].[ASST_ID],10)) ON [TOYS_IN_PACKAGE].[TOY_ID]=NZ([ASSORTMENT_ITEMS].[TOY_ID],12);

Again i would use the same logic on the Item/Parts relationship


thoughts?

Thanks for all of your time with this


[This message has been edited by kawter (edited 11-20-2001).]

[This message has been edited by kawter (edited 11-20-2001).]