Need help with query, please

[djclntn]

I have a dbs/tbl/fld with the first & last names. I want to know how I can separate the ‘First’ & ‘Last’ names in 2-seperte flds. *Note, I had a dbs with such a query; I lost the qry, but was able to find the FirstLast Name tbl in another dbs.



I.E.

Fieldname: Data/Text
FirstLastName Donald Duck

I want:
FirstName Donald
LastName Duck

Thanks in advance!

[djclntn]

*Please excuse my careless/misspelled title, “Need help with query, please

[June7]

Will the names always have only two parts - no Wernher von Braun or Mary Jo Carter?

PS: I fixed thread title

[BobBridges]

...Or Mr. Robert Conrad, or Joe Schmoe IV?

And the next question: How many rows? Because if there are 100, then fixing the exceptions by hand won't be too laborious; if there are 250 000....

[djclntn]

Hey June, I just need it for two parts. Thanks.

[June7]

Try:

SELECT Left(FirstLastName, InStr(FirstLastName," ")) AS FirstName, Mid(FirstLastName, InStr(FirstLastName," ")+1) AS LastName FROM tablename;

Be aware the InStr() function will fail if field is null.

So try:

SELECT Left(FirstLastName, InStr(FirstLastName," ")) AS FirstName, Mid(FirstLastName, InStr(Nz(FirstLastName,"")," ")+1) AS LastName FROM tablename;

[djclntn]

June, it worked!!! Thanks. It took me about 10-minutes to figure out/change some things, but as many other times you came through for me.
I was given another project/dbs to look at & make changes which I’m struggling with. Here’s the dbs tbls & qrys structure:

1. EmploeeEntriesTbl: TDate, EmployeeID, & Hrs (Hours worked per-date
2. EmployeesSaleryTbl: EmplyeeID, & EmplyeesSaleries ($)
3. EmployeesEntriesqry: TDate,,EmployeesID, Hrs, EmployeesSalaries. The PK is, EmployeesID

From this, EmployeesEntriesqry I have lots of SUMS queries. I.E. Format ‘Month or ‘Mnth/Yr’ from the TDate

Ok, here’s my big dilemma, I need to increase ALL of the Employee Salaries per a certain date I.E. 10/1/2013. I cannot go in the EmployeesSalariestbl & change the Employees Salaries because it’ll affected everything pre 10/1/2013.

Now, in ALL fairness to you, I had posted this as a thread many months ago. I think it might have been pd & someone else who replied; they didn’t make any sense, wanted more info, & I didn’t know what else to tell them. Let me know if you’re interested in this, “project” very well.

[June7]

You should probably have a table of salary rates.

Options to use the salary rate table:

1. When rates change, create new records in the salary rate table, include an effective date field. Have a field in employee table for the RateID. Change the ID and they have a new rate. But, as you pointed out, that would change the calculation of pay for existing pay periods. So instead of the RateID in employees table, have related table for EmployeeSalaryHistory. New record for each employee who's pay changes along with a field for effective date. Use the latest record to calculate employee pay.

2. Save the salary figure for each employee in every pay record so that it will never be changed, even if the salary rate table is changed. In this situation, don't create new salary rate records, just change the data in the table and have a field in Employee table for the RateID, which changes if they get promotion or new position.

[djclntn]

Thanks for responding. It's very late my brain is about fried; I don't understand what you mean by, "Rate?" Maybe you can provide some info/example? Hope to have a better understanding tomorrow; catch you if your around... Thanks, June

[June7]

Salary rate - ie., wage, amount, $

$25 per hour
$20 per hour
etc

[djclntn]

June, 1) Rather than using your word, “Rate” I use the word, “Salary” (for right or wrong). In my Employees_Salerytbl I added an ‘EffectiveDate’ fld, & I change the PK to SaleryID. 2) I went into my Employees_Payrolltbl (which has the flds, ‘TDate’ ‘EmployeeID’ ‘Hrs’). 3) When I added the SaleryID fld from the Emplyees_Salerytbl to the Employees_Payrolltbl switched to Datasheet View the SaleryID is blank/Null??? This of course prevents me from relating the 2-tbls on SaleryID because the SaleryID the Employees_Payrolltbl SalaryID fld is blank/empty. Any further suggestions?

[June7]

I guess you need to populate the SalaryID field in Employees_Payrolltbl. If there is no data in it how could records be joined?

Note that you have misspelled salary as salery in table and field names.

[djclntn]

I don’t understand – we’re not connecting. The SalaryID originates in the EmployeesSalarytbl, right?

In the EmployeesSalarytbl I have:

1. SalaryID
2. EmployeeID
3. Salary
4. EffectiveDate

In the EmployeesPayrolltbl I have:

1. Employee_PayrollID
2. SalaryID
3. EmploeeID
4. EmplDate (Date fld)
5. Hrs (Hours worked per-date)

You tell me to populate the ‘SalaryID’ into the Employees_Payrolltbl; haven’t I done the according to the above. BTW, after my last post to you I picked up/sew the discrepancy I had with “salary” & “salary;” I correct it

[June7]

Aren't the two SalaryID fields a primary key/ foreign key association? Just creating the SalaryID field in one table does not populate the field in the other table.

Are there multiple records in EmployeesSalarytbl for each employee? Is this a history of all salaries an employee has ever been associated with? You must select the correct record each pay period, presumably this will be the latest salary record for each employee.

[djclntn]

June, I need to be honest with you. I’ve been jockeying between 2-major projects & I really need to focus on one project. For the past week I’ve been in my wheelchair at my computer for 10-hours a day which isn’t really good for my back. At this end of this month I have to have surgery (my one kidney has to be removed); I haven’t done well in recovering after the last few surgeries I’ve had so I don’t know how long I’ll be in the hospital. Bottom line I need to put this employee project on the back burner for now, but may need some help at a later date. Most importantly, thank you for your time with me; right now I cannot understand your direction/suggestions. TC