Convert nickname to fullname when querying data

**NeoPa** · May 25 '07, 07:24 PM

You should be able to do this in a query after linking the tables in the relevant way. Your question is not currently clear enough to provide a direct answer.
If you post the table Meta-Data we can probably get you further with this.
Here is an example of how to post table MetaData :
Table Name=tblStudent

Code:

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

You will need to post (in this format please) for all the tables that link the data you need together.

**CindySue** · May 25 '07, 08:48 PM

I'm sorry, I'm pretty much a novice. I'm not sure I understand about Meta-data. Is this it?

I have three tables, by the way:

Table Name=[Licensed Providers]

Code:

[I]Field; Type; IndexInfo[/I]
License No;Text;PK
Admin Agency;Text
and some other fields

Table Name = Nicknames

Code:

[I]Field; Type; IndexInfo[/I]
Nickname;Text;PK
Admin Agency;Text
these are the only two fields

Table Name = TA

Code:

[I]Field; Type; IndexInfo[/I]
License No;Text (this is the same as the Providers table)
and some other fields.

I've created several entries in the nicknames table where I have a short abbreviation in the nickname field and then the full name in the Admin Agency field. For example, doe for the nickname and Department of Education for the Admin Agency field.

When I enter a new Licensed Provider, I can either enter the full name in the Admin Agency field or an abbreviation (or nickname) and if an entry exists for that nickname in the nicknames table it will change that entry to the full name, or leave it as typed if one doesn't exist. I'd like to be able to use this same thing in a query.

My query has both the Providers table and TA table, linked by the license number field. I can enter a part of the Admin Agency name (such as dep or edu) and it will look up records where the Department of Education is the Admin Agency. I would like to be able to enter either a part of the Admin Agency name or the nickname as it exists in the nickname table and find records. If it would be easier to have an extra field in the query that would be OK. I'll know ahead of time whether I want to look up by partial name or nickname, so if it would be easier to do with two separate queries, that would be fine.

Does that make more sense? Thanks so much. I really appreciate the help.

**NeoPa** · May 25 '07, 09:31 PM

It does (sort of) make sense.
My confusion now is how you're typing in a search string. You talk of running a (presumably SELECT) query, but there's no mention of a form or other such method of filtering or searching.
The SQL can be created for linking in the NickName stuff, but I'd need a better understanding of what you're doing with it to know what to suggest.
A good start would be to post the SQL of your current query. Please DO use the [CODE] tags (#) provided. It saves me the job of adding them for you afterwards ;)

**CindySue** · May 25 '07, 09:57 PM

Here's my query code: ( I hope this is what you mean by code tags)

Code:

SELECT TA.[License No],
       [Licensed Providers].[Admin Agency],
       TA.[Type TA],
       TA.Consultant,
       TA.[Date Received],
       TA.[Date to RC],
       TA.[Date Assigned],
       TA.[Reason 1],
       TA.[Reason 2],
       TA.[Date Scheduled],
       TA.[Date Completed],
       TA.[Requested By CCLS],
       TA.[Sent to CCLS],
       TA.Comments
FROM [Licensed Providers] INNER JOIN TA
  ON [Licensed Providers].[License No] = TA.[License No]
WHERE ((([Licensed Providers].[Admin Agency]) Like "*" & [Enter Partial Admin Agency Name] & "*")
  AND  ((TA.[Date Completed]) Is Null));

I've got a form that is attached to the query.

I'm opening the form, which in turn starts the query which asks me to enter a partial agency name. When I do that, the records are displayed in the form. Thanks for helping me. I realize I'm terribly inadequate in providing the information in the proper form.

**NeoPa** · May 25 '07, 10:08 PM

To add [CODE] tags to your post, simply select all the text that is code, then click on the graphic above the message you're typing, which looks like a hash (#). This will put [CODE] tags around the selected text for you. I fixed your last post for you, don't worry.

I will have to look at your question again later in the weekend as it's after 23:00 here atm and I'm getting tired.

**NeoPa** · May 26 '07, 02:09 PM

First lets deal with tidying up some things to make life easier for ourselves.
I've simplified usage of the [Licensed Providers] table with a pseudonym LP. This makes it easier to work with and understand at a glance. I assume that your table MetaData is the incorrect version and that the actual name of the [Providers] table is [Licensed Providers]. I've edited your post to reflect this.
I've changed the strings to use the more correct quote (') within SQL.

Code:

SELECT TA.[License No],
       LP.[Admin Agency],
       TA.[Type TA],
       TA.Consultant,
       TA.[Date Received],
       TA.[Date to RC],
       TA.[Date Assigned],
       TA.[Reason 1],
       TA.[Reason 2],
       TA.[Date Scheduled],
       TA.[Date Completed],
       TA.[Requested By CCLS],
       TA.[Sent to CCLS],
       TA.Comments
FROM ([Licensed Providers] AS LP
     INNER JOIN TA
  ON LP.[License No]=TA.[License No])
     INNER JOIN NickNames AS NN
  ON LP.[Admin Agency]=NN.[Admin Agency]
WHERE ((TA.[Date Completed]) Is Null)
  AND  ((LP.[Admin Agency] Like '*' & [Enter Partial Admin Agency Name (or NickName)] & '*')
   OR   (NN.Nickname Like '*' & [Enter Partial Admin Agency Name (or NickName)] & '*'));

Now to the changes more relevant to your particular issue :
First of all you need to link in the extra table (See the FROM clause).
Now you need to change the WHERE clause to include a check for the Nickname. This should be ORed with the other name check, but together these checks should be ANDed with the date check.

I have made the assumption here that all [Admin Agency]s have associated Nicknames. If that is not true then this will not work as expected and some further changes will be required.

**CindySue** · May 29 '07, 04:47 PM

Thanks so very much for your help. The Admin Agencies do not all have nicknames. Nicknames only exist for some of the longer ones or the ones most frequently used.

**NeoPa** · May 29 '07, 05:15 PM

Originally posted by CindySue

Thanks so very much for your help. The Admin Agencies do not all have nicknames. Nicknames only exist for some of the longer ones or the ones most frequently used.

OK, but I'll have to come back to this tomorrow (or later today if I'm lucky).
If I haven't replied by tomorrow morning, then bumb the thread to re-add it to my list for tomorrow.
For future reference, that would have been handy to know before-hand ;)

**NeoPa** · May 30 '07, 12:10 AM

Originally posted by CindySue

Thanks so very much for your help. The Admin Agencies do not all have nicknames. Nicknames only exist for some of the longer ones or the ones most frequently used.

The JOIN is changed and the Nz() function is used to handle unwanted Nulls.

Code:

SELECT TA.[License No],
       LP.[Admin Agency],
       TA.[Type TA],
       TA.Consultant,
       TA.[Date Received],
       TA.[Date to RC],
       TA.[Date Assigned],
       TA.[Reason 1],
       TA.[Reason 2],
       TA.[Date Scheduled],
       TA.[Date Completed],
       TA.[Requested By CCLS],
       TA.[Sent to CCLS],
       TA.Comments
FROM ([Licensed Providers] AS LP
     INNER JOIN TA
  ON LP.[License No]=TA.[License No])
     LEFT JOIN NickNames AS NN
  ON LP.[Admin Agency]=NN.[Admin Agency]
WHERE ((TA.[Date Completed]) Is Null)
  AND  ((LP.[Admin Agency] Like '*' & [Enter Partial Admin Agency Name (or NickName)] & '*')
   OR   (Nz(NN.Nickname,'') Like '*' & [Enter Partial Admin Agency Name (or NickName)] & '*'));

**CindySue** · May 30 '07, 03:30 PM

I'm terriby sorry, but I'm not getting any results from the query. I copied the query info and pasted it into SQL view. I copied the Licensed Providers and Nicknames tables and named them LP and NN so the names would be the same. In LP I deleted all but 6 records. In TA I deleted all but 10 entries, using each license number from the LP table once and one four times. I used Dept. of Education as the Admin Agency in the LP table, and it exists exactly the same (I copied and pasted to be sure) in the NN table, with doe as the nickname. When I query I can't get any results at all, either from a nickname or from a partial name.

**NeoPa** · May 30 '07, 05:36 PM

The table names I used were the ones you provided. The query simply uses aliases, that's all. The SQL code was designed to copy and paste straight into a QueryDef's SQL view.
What do you mean you copied the tables?
What do you have in the way of tables now?

**CindySue** · May 30 '07, 08:55 PM

I didn't understand about the aliases, so since I didn't have tables with those names I copied the Licensed Providers table and named it LP and copied the Nicknames table and called it NN. I also complicated things when I didn't make sure the date completed field was null in my practice data. I put their names back and made sure the date completed field is empty, and it seems to be working now. I REALLY appreciate all your patience and effort. Thank you so very much.

**NeoPa** · May 30 '07, 10:39 PM

Not a problem :) I'm glad it worked when you 'reverted'.
Wherever you see the keyword AS in SQL it indicates that the preceeding expression is to be named as the succeeding name.
Tables can get this treatment, but so can fields and more complicated expressions (including sub-queries, but let's not go there for now :D). It can also be written by simply forgetting the AS keyword, but It's my practice never to do that, as it's more confusing to read and understand.