Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    69

    Arrow Unanswered: Search and Filter function

    Hi ,

    I need some help with this search and filter function i am working on now.
    I have a database with products. (product code and description) The product code is encoded as following XX XXX XX X (all the X's beeing numbers) The first two X's stand for product type second for brand and so on .....
    So if i want to search all products from brand 100 it should run a query with the variable ??100???
    To typ all the ?'s is not so user friendly so i thaught to work with windows wheren you can fill in the information you want to filter on and maybe a refresh button so you can display the filtered information (image A)

    The result should be visible as in example B.

    Anyone any idea ?
    Attached Thumbnails Attached Thumbnails srcfction.JPG  

  2. #2
    Join Date
    May 2004
    Posts
    65
    If you create a new field in your query eg.
    ProductGroup:Mid$([Product Code],3,3)

    The new ProductGroup field will only contain the 3 characters starting from position 3.

    In the criteria match this to your form search of the three characters set by the user.

  3. #3
    Join Date
    Sep 2003
    Posts
    69
    Thanks for your help but this is not my problem. The real problem is i don't know how to put the filtering criterion in a field of a form and then run a query.
    I can easyly make a query eg Pcode [Insert product code:] and when i run the query the user would be asked to insert the variable but i want to avoid that by making 3 filtering fields , each for a part of the product code and after update automatically run the query and if possible in the form (not in a separate window)

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you can certainly do it with what you've got (constructing a string "???100????" (actually "###100###" for numerics) and using LIKE in the query

    gavinfm's idea will also fly.

    BUT, you may be better-off reconsidering your table design.

    you show the way in your own post: "product type", "brand", "and so on" are NOT the same species so they don't belong in the same table, let alone in the same field.

    then you reconstruct your product number from a bunch of foreign keys.

    ...and then you can select by brand, type etc without messing around with substrings or LIKE comparisons (they are not the fastest things to do).

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you want to see how to use forms as query critera, the currently adjacent post has an example from Bud and a text howto from me.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2003
    Posts
    69
    izy,

    i solved the problem but i am not sure what you mean when advising me to reconsider the way my tables are conceived. Now every product has a code and the most logical this is to make this code 'logic'

    by example: suppose i sell drinks and i want to assign a code to coca cola light bottles of 1.5l , my code would be 01 for group drinks, 001 for the brand (coca cola) , 01 for the type (cola), and 1 for the subtype (light) . So the full code would be 01001011 (fictive code)

    It make is easy and structurated to learn the codes by hart. I think this is a very common way to assign codes to products. Am i wrong ?

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it all depends on your application and what you are trying to achieve.

    maybe it doesn't work for you, but i was thinking of something like this:

    tblGroup
    IDgroup, auto, PK
    strType, text
    entries like: 1, Drinks; 2, Pizza; 3, Newspapers

    tblSupplier:
    IDsupplier, auto, PK
    strSupplier, text
    ...and other supplier stuff like address etc
    entries like: 1, Coke; 2, Pepsi; 3, Conde-Nast; 4, Pizza Hut

    tblType:
    IDtype, auto, PK
    strType
    entries like: 1, Cola; 2, Orange; 3, Washington Post

    tblSubType:
    IDsubT, auto, PK
    strSubT, text
    entries like: 1, Normal; 2, Light

    tblProduct:
    IDproduct, auto, PK 'or maybe use a combination of the FKs
    IDgroup, FK
    IDsupplier, FK
    IDtype, FK
    IDsubT, FK
    ...and whatever else you want to say about the product

    now there is nothing to memorise.
    combos could build a PN while the user sees old-fashioned text.
    JOINs will happily find all "Light" or all "Drinks"

    it would be nice to be smarter. e.g. you don't want to see "Washington Post" as a type of "Drinks" so maybe you need some tables between each layer to define which sub-categories are possible.

    tblMMgroup:
    IDmmGroup, auto, PK
    IDgroup
    IDsupplier
    >>> entries like: 1, 1, 1; 2, 1, 2; 3, 2, 1; 4, 2, 2 <<< PLAIN WRONG!
    entries like: 1, 1, 1; 2, 1, 2
    ...so after selecting "Drinks" your supplier options are "Pepsi" & "Coke"

    i have to admit that the whole thing becomes much more complicated!!!!!

    izy
    Last edited by izyrider; 01-10-05 at 13:33. Reason: error
    currently using SS 2008R2

Posting Permissions

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