Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    9

    Unanswered: Many-to-many lookup

    In Access 2007 I have a Many-To-Many relation set up between Products and Categories (with a Products_Categories_Spec junctiontable between), so one product can have many categories and one category can belong to many products.

    I'd like to make a Form where I can easily set the categories for each product without having to deal with the ID numbers, which is the case when just using the "make Form Wizard".

    How is this possible without using the "evil" lookup function?

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    not sure I understand your question

    if your Categories table is just a single field/column table of text records - then there are no evil ID numbers

    and you don't really need a junction table; just have a static Categories table to avoid retyping the category name each time. Am not a big fan of look up fields in tables but this is one time it is particularly helpful....

    Product Category
    Watch jewelry
    Watch athletic gear
    Watch mens wear
    Ring jewelry


    Make the category field to be a look up field sourced on the Category table...

    now if you filter the Product field on 'Watch' you will get all the categories....if you filter the Category field on 'jewelry' then you will get both Watch and Ring...

  3. #3
    Join Date
    Dec 2009
    Posts
    9
    And I dont understand your answer. Du you mean that I should use the new Access 2007 function that stores many values in the Category field in the Product table? I tryed to make a static table called Categories with a Product and Category field. Then I made a Category field in the Product table. Then I used the lookup guide at the Category field in the Product table and choosed the Categories table as the source of values. in available fields I picked Category wich I also sorted at. then I got the option to choose if I want to store many values. Should I mark that option? I heard that it uses an invinsible junction table, and then I could as well use a visible one. Well if I don't mark that option it doesnt work at all.

    Have I missed something here?

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    my answer was not 07 specific.

    I wouldn't bother to use the 07 feature of multi value fields.

    just have a static Categories table

    then in your main table (Products) make the category field to be a look up field sourced on the Category table...

    it doesn't need to be any more complicated than this....

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    categories don't belong to products
    products may have one or more category... woudl they have more than one category, really?

    or ar you using something similar to key words

    one thing you could consider his a category hierarchy, where your categories self reference

    eg
    categories
    Code:
    jewllery
    +-rings
    +-necklaces
    +-watches
      +-wrist
      |  +Mens
      |   |  +Analogue
      |   |  + Digital
      |  +Womens
      |  +Androgenous
      +-chain
      +-pendant
    to model that you need the category, a description and an optional pointer to that categories parent.

    however you may find it smarter to use key words selected from another table
    so you define you key words for a watch as 'sports' 'mens' 'wrist' 'bling'
    the reason for defining the key words in a table is that it keeps some discipline in how the key words are formulated
    it allows your user to have ultimate flexibility. in the traditional hierarchy category model you have repeating elements ie you have wristwatches which often fork into 2 or more groups

    eg "analogue or digital", "male, female or Androgrenous",
    bear in mind you may need to apply a NOT filter as well eg if a user only wants Male you should exclude results for female & androgenous watches..

    BTW Access has historically had some wizards and tools which can seem very attractive.. classically this was styled the lookup wizard... what you are talking about sounds sort of similar. if its embedding a list of options inside a table, or column in a table, then I'd strongly recommend you don't use it as its an absolute pain in the medium to long term.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Dec 2009
    Posts
    9
    Quote Originally Posted by NTC View Post
    my answer was not 07 specific.

    I wouldn't bother to use the 07 feature of multi value fields.

    just have a static Categories table

    then in your main table (Products) make the category field to be a look up field sourced on the Category table...

    it doesn't need to be any more complicated than this....
    I still don't see how this sollution can make it possible for one product to have more then one category? They really need to be in diffrent categories at the same time because thats how it should be in the webshop.

Posting Permissions

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