Alias query

Hi:

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 ?

Thanks,
Phil Miller
pillmillAsked:
Who is Participating?
 
harfangCommented:
Hello,

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."
    Else
        ' proceed...
    End If

Cheers!
(°v°)
0
 
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectCommented:
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
FROM NAMES, ALIASES
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.

Regards.
0
 
gh081410Commented:
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
rs.MoveLast
rs.MoveFirst

lngRecordCount = rs.RecordCount

exit_err_Gestion:
    Exit Function
err_Gestion:
If Err.number = 3021 Then
    lngRecordCount = 0
    Resume exit_err_Gestion
Else
    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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.