Alias query

Posted on 2006-03-31
Last Modified: 2006-11-18

I need to build the capability of using aliases for names.

The idea is to have to tables: 1. NAMES; 2.ALIASES
There will be aliases for some of the names.

When a user wants to employ a name that is only known by
an alias, I want the system to first query NAMES and if there
is no result then to query ALIASES.

for the first step, how do I capture the result of the first query
( a SELECT) with the indication that the query produced no result ?

Phil Miller
Question by:pillmill
    LVL 58

    Accepted Solution


    You need this in VB?

        varTrueName = DLookup("strName", "tblNAMES", "strName = '" & strInput & "'")
        If IsNull(varTrueName) Then
            varTrueName = DLookup("strName", "tblALIASES", "strAlias = '" & strInput & "'")
        End If
        If IsNull(varTrueName) Then
            MsgBox "Input: " & strInput & " not found either as name or as alias."
            ' proceed...
        End If

    LVL 13

    Expert Comment

    by:John Mc Hale
    Why not create a Union query like this:

    SELECT FirstName, Surname, 0 AS IsAlias FROM NAMES UNION
    SELECT AliasFirstName AS FirstName, AliasSurname AS Surname, -1 AS IsAlias
    ORDER BY IsAlias DESC, Surname, FirstName ASC;

    Then use the query as the basis for the search.

    Beauty about this is you only need one result set, duplicates are filtered out, and calculated field 'IsAlias' will tell you whether the name is an alias or not. You must take into account the overhead involved in opening/querying etc. of multiple datasets.

    Just my 5 cent worth.

    LVL 3

    Expert Comment

    first paste this funtion in a module
    this function count the number of row of a recordset
    i movelast and movefirst before because there's a bug in the recordcount propertie of recordset in access
    Public Function lngRecordCount(rs As Recordset) As Long
    On Error GoTo err_Gestion

    lngRecordCount = rs.RecordCount

        Exit Function
    If Err.number = 3021 Then
        lngRecordCount = 0
        Resume exit_err_Gestion
        MsgBox Err.number & vbCrLf & Err.DESCRIPTION
        Resume exit_err_Gestion
    End If
    End Function

    private function blablabla()
    dim rs as recordset

    set rs = currentdb.openrecordset("select NamesCollumn FROM Names WHERE NamesCollumn='Someting'",dbopensnapshot)

    if lngrecordcount(rs)=0 then 'no record in your recordset
    'here you can open the next querry
    end if

    if not rs is nothing then rs.close
    end function


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now