Results 1 to 4 of 4
  1. #1
    IMTheNachoman is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    2

    Question calculated column - does value exist as substring in another table

    I'm having a hard time figuring out how to create a query with a sepcific calculated column. Hard to explain so I will give an example.

    Let's say I have these two tables:



    Table A


    Server Name OS
    row1 AIX
    ROW2 Linux
    RoW3 AIX
    row4 Solaris
    rOw5 AIX

    Table B


    Server Name
    row1
    hi_row2
    row_this
    row_that
    row3
    row5

    I want to create a query that will show all rows and columns from "Table A" with a calculated column that says "Yes" or "No" if that row's "Server Name" is in "Table B". The tricky part is that the "Server Name" from "Table A" may be a sub-string of "Server Name" of "Table B".

    Desired Output Table


    Server Name OS In Table B
    row1 AIX yes
    ROW2 Linux yes
    RoW3 AIX yes
    row4 Solaris no
    rOw5 AIX yes

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    SELECT TableA.ServerName, TableA.OS, IIf(IsNull(DLookUp("[ServerName]","tableB","[ServerName] like '*" & [ServerName] & "*'")),"no","Yes") AS Expr1
    FROM TableA;

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    You could try this:
    Put this code in a module

    Public Function InTableB(str As Variant) As Boolean
    Dim rec As DAO.Recordset
    Set rec = CurrentDb.OpenRecordset("Table B")
    With rec
    .MoveFirst
    Do Until .EOF
    If InStr(1, .Fields("Server Name"), str) > 0 Then
    InTableB = True
    Exit Do
    End If
    .MoveNext
    Loop

    End With
    End Function

    Use this:

    SELECT [Table A].[Server Name], [Table A].OS, IIf(InTableB([Server Name])=True,"Yes","No") AS [YesNo]
    FROM [Table A];

    as the SQL for your query.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    IMTheNachoman is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    2
    Thanks guys. Will try this out today.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculated column in a report
    By beanhead0321 in forum Reports
    Replies: 2
    Last Post: 08-16-2011, 08:48 PM
  2. Checking if data already exist in a Table
    By Phillsoft in forum Forms
    Replies: 2
    Last Post: 08-04-2011, 02:07 PM
  3. creating a calculated column
    By bold01 in forum Access
    Replies: 13
    Last Post: 02-10-2011, 08:17 AM
  4. If value already exist or not in the table
    By dada in forum Programming
    Replies: 3
    Last Post: 08-19-2010, 01:57 AM
  5. Check if value exist in a table
    By Lucas83 in forum Programming
    Replies: 2
    Last Post: 06-02-2010, 11:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums