Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    154

    Question Query Question Regarding Two Tables

    Let's say I have two tables, an Inventory Table and a Showroom table. Sometimes Inventory is used as a showroom display model and is transferred to a new Inventory batch with a dash and the showroom code after it. The showroom code is unfortunately a string and does not have a standard length. Is there a way to ask in a query to only show me Inventory that looks LIKE a showroom code (any showroom code). I have tried using LIKE and IN() to no success, and it seem like trying to make a criteria string in a function (Like *this* OR like *that* OR ...) never seems to work in the criteria box in the query builder(I'm sure that this has to do with LIKE being a reserved word and not text, so the criteria ends up looking for the word 'like'). I am down to doing my old standby of making a table by running through all Inventory with dashes and comparing that to all showrooms (my old friend Instr) and then creating a table but I wondered if there is some trick that I haven't run across to do this quicker and with greater elegance. I am using Access 2003. Thank you for your time and your replies.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,177
    Like requires quotes, so if your showroom codes all contain "SR," you would say, for example,
    Code:
    WHERE IC Like "*SR*"
    This would include all inventory that includes a showroom code, and exclude all those that don't.

    Sam

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,569
    just as an aside its a leetle odd to my way of thinking to have a showroom table and an inventory table, that idenfication can be achieved by having a single location column which identifies where an item is (it could be showroom, it coulkd be a warehouse, it could be on the back of a truck....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,177
    just as an aside its a leetle odd to my way of thinking to have a showroom table and an inventory table, that idenfication can be achieved by having a single location column which identifies where an item is (it could be showroom, it coulkd be a warehouse, it could be on the back of a truck....
    ... and if you only have two or three showrooms (as opposed to 10 or more), you don't even need a separate table for them. Just make a combobox, make the row source type "table/query", and type all the values right there in the row source. You can use the wizard for this. Put the selected location in a separate column in the table, not as a part of the inventory code.

    Sam
    Last edited by Sam Landy; 11-08-12 at 12:29. Reason: I'm continuing healdem's thought

Posting Permissions

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