none
Using a Make Table query to make a table with records that have similar names RRS feed

  • Question

  • Hello,

    I am working with a table named TempData and a field named Sample.  The table holds thousands of sample records.  Below is a subset of the Sample field values:

    18I01

    18I01RE1

    18I01RE2

    18I02

    18I03

    18I04

    18I04RE1

    I would like my make table query to create a new table that includes the records that have an "RE" in them as well as the record that only has the base name without the "RE".  In the example above, I would like my newly created table to hold the information for 18I01, 18I01RE1, 18I0RE2, 18I04, and 18I04RE1.  I can easily get the "RE" records into the new table but I am having a hard time relating them to the base record and having it pull in as well.  Does anyone have any suggestions?  Do I need to do this in multiple steps or queries?

    Wednesday, October 10, 2018 6:34 PM

All replies

  • I'm not following your logic.

    > includes the records that have an "RE" in them as well as the record that only has the base name without the "RE". 

    You seem to say: all records WITH "x" and all records WITHOUT "x"
    That is all records (although we could argue about NULL values which don't conform to either :-)).

    You introduce the term "base name" without defining it.

    You then say which records you want to return, without saying why other records should be excluded. I can guess, but I want you to use precise language to say what you want.

    More fundamentally, why should samples be selected by their SampleName, rather than SampleID, or SampleDate, or some other attributes of a sample? It seems you give too much power to SampleName. It seems to be the concatenation of several attributes - rarely a good idea, and never when using in queries.


    -Tom. Microsoft Access MVP


    Thursday, October 11, 2018 2:49 AM