asked on

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

ASKER CERTIFIED SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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
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.

gh081410

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