Problem using Expr1 in an Unmatched Query (1 Viewer)

AOSB

Registered User.
Local time
Yesterday, 17:21
Joined
Sep 12, 2011
Messages
18
Hi all. In my database I have a linked table called NewClients (produced by another application and thus beyond my control) which has a field called [Fullname]. Typical contents are "John Paul Smith".
I have a local table called tblEmployees which includes two fields [LastName] and [FirstNames]. Typical contents are "Smith" and "John Paul".

The unmatched query that I require needs to find records with the [NewClients].[FullName] fields which are not matched by [Employees].[FirstNames] & " " & [Employees].LastName] expression. This should give me a list of people and contact details etc to add to my Employees table. In fact I intend to go on to make an update query to do just that.
So far, just to get it working, I have tried

SELECT NewClients.FullName, [tblEmployees].[LastName] & " " & [tblEmployees].[FirstNames] AS Expr1
FROM NewClients, tblEmployees
WHERE (tblEmployees.[FirstNames] & " " & tblEmployees.[LastName]) Is Null;

This runs but gives an empty recordset, I think because the query needs a JOIN clause but I can't get the syntax right.
Is there someone clever out there please?
 

r.harrison

It'll be fine (I think!)
Local time
Today, 01:21
Joined
Oct 4, 2011
Messages
134
Try this;


SELECT NewClients.FullName, tbl_Employees.LastName, tbl_Employees.FirstNames
FROM NewClients, tbl_Employees
WHERE ((Not (NewClients.FullName)=[tbl_Employees].[FirstNames] & " " & [tbl_Employees].[LastName]));
 

Brianwarnock

Retired
Local time
Today, 01:21
Joined
Jun 2, 2003
Messages
12,701
You are correct that you need a Left Join from NewClients to tblemployees however the field you would join on does not exist, I am too rusty to remember if you can join on a calculated field/alias being created in the join, but you could create a query with that alias(Expr1) and the rest of the table and join new clients to that query on Expr1.

Brian
 

AOSB

Registered User.
Local time
Yesterday, 17:21
Joined
Sep 12, 2011
Messages
18
Yes Brian, thanks for the speedy reply, you have understood my exact problem. I cannot make a left join to the alias and I suspect you are correct and it it not supported. Could you be a bit more specific with your suggested workaround please. I am not rusty - I just never knew how to do something like that in the first place!
 

r.harrison

It'll be fine (I think!)
Local time
Today, 01:21
Joined
Oct 4, 2011
Messages
134
To create a field to join to you could add a calculated column to your Employees table that stores the Full Name then create the join on that value.
 

AOSB

Registered User.
Local time
Yesterday, 17:21
Joined
Sep 12, 2011
Messages
18
I have had a go myself but without much success
Here is the qryPending code

SELECT NewClients.FullName
FROM NewClients LEFT JOIN qryLongName ON NewClients.[FullName] = qryLongName.[Expr1]
WHERE (((qryLongName.Expr1) Is Null));

and here is the "Longnames" query referred to in the above


SELECT tblEmployees.LastName, tblEmployees.FirstNames, ([tblEmployees].[FirstNames] & " " & [tblEmployees].[LastName]) AS Expr1
FROM tblEmployees;

"Longnames" works but qryPending spawns an empty recordset in spite of the table "NewClients containing plenty of names that are not in the tblEmployees
I still need some help obviously
 

r.harrison

It'll be fine (I think!)
Local time
Today, 01:21
Joined
Oct 4, 2011
Messages
134
I figured out how to do it with code, but couldn't do it with queries. If you're interested then here's what I came up with.

Code:
Sub Update_Employees()

    Dim strFullName, FName, SName As String
    Dim RS, RSE As Recordset
    Dim Nms As Variant
    Dim NmeCount As Integer
    
    Set RS = CurrentDb.OpenRecordset("NewClients")
    
    RS.MoveFirst
    Do Until RS.EOF
        strFullName = RS![FullName]
        Nms = Split(strFullName, " ")
        NmeCount = 0
        For Each i In Nms
            NmeCount = NmeCount + 1
        Next
        FName = ""
        For i = 0 To NmeCount - 2
            FName = FName & Nms(i) & " "
        Next
        FName = Trim(FName)
        SName = Nms(NmeCount - 1)
        Set RSE = CurrentDb.OpenRecordset("SELECT COUNT ('*') AS [Total] FROM [tbl_Employees] WHERE [Firstnames] = '" & FName & "' AND [lastname] = '" & SName & "' ;")
        If RSE![total] = 0 Then
            CurrentDb.Execute ("INSERT INTO [tbl_employees] ([Firstnames], [Lastname]) VALUES ('" & FName & "', '" & SName & "');")
        End If
        RS.MoveNext
    Loop
    
End Sub
 

Brianwarnock

Retired
Local time
Today, 01:21
Joined
Jun 2, 2003
Messages
12,701
The query approach was correct, interesting that that did not work but the code which splits the full name rather than combining the first and surname from employees does??

Btw dim stra ,strb,strc as string only sets strc the others will default to variant type, at least that's the case upto 2003

Brian
 

r.harrison

It'll be fine (I think!)
Local time
Today, 01:21
Joined
Oct 4, 2011
Messages
134
@ Brian,

Why use the stra, strb, strc? I used a variant as there could be more than 3 names in the string so thought this would cover all bases.
 

Brianwarnock

Retired
Local time
Today, 01:21
Joined
Jun 2, 2003
Messages
12,701
Sorry for the delay in replying, seem to be having connection problems.

It was this line I was refering to

Code:
Dim strFullName, FName, SName As String

only Sname will be declared as a string


Brian
 

r.harrison

It'll be fine (I think!)
Local time
Today, 01:21
Joined
Oct 4, 2011
Messages
134
Oh, I wasn't aware of that! I've always coded that way without errors. Maybe just been lucky up until now.

And just tested it too. You are right! Thanks for the tip.
 

Brianwarnock

Retired
Local time
Today, 01:21
Joined
Jun 2, 2003
Messages
12,701
It won't normally cause errors but there is the possibility especially, I think with numbers, however there will be a performance cost with type conversions.

Brian
 

Users who are viewing this thread

Top Bottom