Splitting fields to last and First Name

[rich1838]

I have a database where information is imported from another source. The other party sends 2 files that are imported. In the first file, they send employee information such as name, address, etc. The other file is automatic deductions that an employee makes monthly. I need to create a query that combines the two which I have no issue with. Here is the problem. The first file (Table) the name is split into 2 fields, and is just First and Last names with no "titles" (Jr Sr II III)the second file comes in as Lastname Jr, Firstname Middle initial (Smith Sr., John A.). I have split the fields and the query works but people with the tile in Last Name (Smith Jr.) are omitted.
Can I get rid of the title part of the name?

LastName: Left([Name],InStr([Name]," ")-1)

FirstName: Mid([Name],InStr([Name],",")+1)

It would be great if I could remove the Middle initial as well. Smith Jr., John A. becomes Smith John)

Thanks in advance.
Rich

[Rabbit]

Split it by the comma first to separate the last name and first name. Then take the result of that and split it by the space. So kind of what you have already but with an extra embedded call.

[rich1838]

Unfortunately that did not work. When I do the initial split, I get the single names are ok but the ones with titles are still attached. When I split again by space, the single names are omitted and only the ones with tiles are present.

[rich1838]

I have an access 2010 database where I import two tables from excel into 2 tables the database from a 3rd party. The first table is an employee list with demographic information and the fields are Last, First, Title, Address, Address 2, City, State, Zip, Telephone, and Hire Date. A new list is sent monthly, so Once the first set of records is in the table, the next step is an update query which adds new employees, updates former employees, as well as updates demographical information in the event of a change. The second table is a monthly import reporting biweekly deductions made by the employees from their payroll.

Here is my issue, The second table, (Dues Report), has fields that are sent as a combined name (Brown, Joe) With these individuals, I am either split the names on one table Left([Name],InStr([Name],",")-1) in one field and FirstName1: Mid([Name],InStr([Name],",")+1) in a second field. I can also due a name combine from the first table [Last] & ", " & [First] to cross reference table.

The second table come in with the combined names using titles and Initials (Smith III, Robert A.)

If I name combine From table one, Smith III, Robert A. <> Smith, Robert , If I Left([Name],InStr([Name],"," then Smith III, Robert still don't match.

I am willing to either Split the combined field to get just Smith Robert, or have the query find results from using Like so Smith III, Robert A. will match Smith, Robert.

I was willing to add the titles and initials to cure the problem, however every time I update the table with the new imported info, all my changes will revert to the original.

The Table "Employees" has everyone's demographic information. Every month I get a new list and then import into the database which in turn updates and adds to the employee list and then deletes all the records from the "Update Employees" table where the excel sheet was imported to so there is no redudancy in storage of data, just updates. If I change the records in the Last Name and First Name fields of the table by adding titles like Jr. and Sr. or initials like A. and B. so that the name combine will match the "Name" field in the second table, the next monthly update will revert the records to the original data and again will need to be manually updated.

Employee Table
Fields
Last ex. Brown
First ex. John
Address
Address 2
City
State
Zip
Date of Hire
NameCombine ex:Brown, John (Calculated)

duesreport Table
Fields
Name ex . Brown Jr., John C.
Dues Paid
Initiation Paid
Date Paid

I can use LastName1: Left([Name],InStr([Name],",")-1) which makes Smith, Bob just Smith but Brown Jr., John C. becomes Brown Jr. and not Brown.

I also tried the following after reading the forums:

SELECT duesreport.Name
FROM duesreport, employeelist
WHERE (((duesreport.Name) Like ([employeelist].[NameCombine] & "*")));

This works on all except those with Jr., Sr. on the end.

[Rabbit]

Please show me how you attempted to implement my suggestion. Can't tell you what's wrong if I can't see it.

[rich1838]

LastName1: Left([Name],InStr([Name],",")-1)
Same query
LastNamedone: Left([LastName1],InStr([LastName1]," ")-1) "Invalid Cell Call Error"

Created New Query
LastNamedone: Left([LastName1],InStr([LastName1]," ")-1)
Result-#Funct!

[Rabbit]

You can't reference an alias that is created within the same level. Instead, embed the function calls in each other.

[rich1838]

OK. How would you do that, I keep getting The expression you entered has a function containing the wrong number of arguments.

LastName1: Left([Name],InStr([Name],",")-1, Left([Name],InStr([Name]," ")-1))

[Rabbit]

Something along the lines of
Left(Left([Name],InStr([Name],",")-1), InStr(Left([Name],InStr([Name],",")-1) & " ", " ")-1)