?
Solved

Alias query

Posted on 2006-03-31
3
Medium Priority
?
396 Views
Last Modified: 2006-11-18
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
0
Comment
Question by:pillmill
3 Comments
 
LVL 58

Accepted Solution

by:
harfang earned 1000 total points
ID: 16344218
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
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 16344270
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
 
LVL 3

Expert Comment

by:gh081410
ID: 16344280
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

809 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