Dduplicates in query

[chavez_sea]

Hi colleagues,
I have a table with weekly wages. I would like to extract the data which would indicate when persons received a raise. These are the fields I have in the query.

Begin Date

Hourly Wage

Soc Security Number

Employee Name

I want data for the original rate at time of hire and the first date each new rate was used. So I would want eg data for begin date Nov 4 and Nov 25.

Begin Date	Hourly Wage	Soc Security Number	Employee Name
04-Nov-18	$15.00	000001111	Robert Williams
11-Nov-18	$15.00	000001111	Robert Williams
25-Nov-18	$18.00	000001111	Robert Williams
02-Dec-18	$18.00	000001111	Robert Williams Thanks for your assistance Cristina

[June7]

So you want the earliest record for each Employee/Wage pair. This will probably need a nested query using TOP 1. Review http://allenbrowne.com/subquery-01.html#TopN

Need a unique identifier field - autonumber type will serve.

SELECT Table1.* FROM Table1 WHERE ID IN (SELECT TOP 1 ID FROM Table1 AS Dupe WHERE Dupe.EmployeeName=Table1.EmployeeName AND Dupe.HourlyWage=Table1.HourlyWage ORDER BY Dupe.EmployeeName, Dupe.BeginDate);

Be aware this sort of nested query can perform slowly with very large dataset.

[chavez_sea]

SELECT DISTINCT [Salary Register].[Hourly Wage], [Salary Register].[Social Security Number], [Employee Records]![First Name] & " " & [Employee Records]![Last Name] AS [Employee Name], [Salary Register].[Begin Date] FROM [Employee Records] RIGHT JOIN [Salary Register] ON [Employee Records].[Social Security Number] = [Salary Register].[Social Security Number]; Thanks

[chavez_sea]

I do not see my entire message. I was asking for your assistance, as I am not versed with SQL. I copied the SQL from the query I made.

Would you be able to assist me with the additional codes needed.

Thanks
Cristina

[June7]

Apparently I edited my previous post after you read it. Review again.

[chavez_sea]

This is my trial

SELECT DISTINCT [Salary Register].[Hourly Wage], [Salary Register].[Social Security Number], [Employee Records]![First Name] & " " & [Employee Records]![Last Name] AS [Employee Name], [Salary Register].[Begin Date]
FROM [Employee Records] RIGHT JOIN [Salary Register] ON [Employee Records].[Social Security Number] = [Salary Register].[Social Security Number];
WHERE Salaries Register.Hourly Wage
(SELECT TOP 1 Hourly Wage
FROM Salaries Register AS Dupe
WHERE Dupe.Hourly Wage = Rate
ORDER BY Dupe.Salaries Register, Dupe.OrderID DESC)
ORDER BY Salaries Register.Name, Salaries Register.Begin Date, Salaries Register.Hourly Wage;

[June7]

That's not following my suggested syntax. I doubt OrderID is a field in your table.

Are you saving SSN into Salary Register table? Why? This should only be in Employee Records table. Should not use SSN as PK/FK. Use an autonumber field or an EmployeeNumber you create.

Advise not to use spaces in naming convention.

[chavez_sea]

Hello June7....Grateful for your assistance
I tried the code, but it gives a message. See below. I take note of your suggestions especialy about the SSN Reason I have it is I have a main form Employee records, and a subform Salaries Register to enter salaries. I have linked fields of SSN and wage rate.

SELECT Salaries Register.* FROM Salaries Register WHERE ID IN (SELECT TOP 1 ID FROM Salaries Register AS Dupe WHERE Dupe.EmployeeName=Salaries Register.EmployeeName AND Dupe.HourlyWage=Salaries Register.HourlyWage ORDER BY Dupe.EmployeeName, Dupe.BeginDate);

[chavez_sea]

I do have an auto number field camed ID.

[June7]

Not seeing error message in your post.

[chavez_sea]

[chavez_sea]

Syntax error in query expression 'ID IN (SELECT TOP 1 ID FROM Salaries Register AS Dupe WHERE Dupe.EmployeeName=Salaries

Register.EmployeeName AND Dupe.HourlyWage=Salaries Register.Hourly Wage ORDER BY Dupe.EmployeeName, Please see error msg

[June7]

Error message still not showing in post. Is it an image? Maybe my computer/browser has issue. Happens sometimes with embedded images in post.

You have spaces in names. Need to enclose in [ ]: [Salaries Register]

Advise not to use spaces in naming convention.

[chavez_sea]

I have decided to close this. I did a report and sorted and grouped and was able to get the desired result. Thanks for your assistance. Regards