SQL Query

[Reggie2004]

I have a table that is linked to a spreadsheet. There are several names and years listed. Each name has multiple year entries. I need to remove 1 year from each name that has multiple years. If there is just one year I need to leave it there. Please help?

Reg

 

[SQLBill]

Give us a sample of how the data looks and how you want it to look.

-SQLBill

Posting advice: FAQ481-4875

 

[Reggie2004]

The data comes out looking like this

Name Year
B. Bob 2001
B. Bob 2002
B. Bob 2003
T. Talk 1999
R. Rooster 2001
R. Rooster 2002

For the names with multiple years I need to remove the earliest year. So the output would look like this.
Name Year
B. Bob 2002
B. Bob 2003
T. Talk 1999
R. Rooster 2002
Remember that the data is being loaded from a spread sheet.
That is the way I have to do it because of multiple spreadsheets that have to be processed.

 

[SQLBill]

Maybe something like:

DELETE FROM tablename
WHERE Year = (SELECT MIN(Year) FROM Tablename)
GROUP BY Name
HAVING Name > 1

(Change the "DELETE" to "SELECT Name, Year" for testing purposes).

If that works the way I think it should, it will group the Name column and then apply the DELETE to only those GROUPs that have more than one Name. It will then DELETE only those rows that match the MIN year value for that GROUP.

-SQLBill

 

[Reggie2004]

Thanks... Will give it a try.

Reg

 

[Reggie2004]

Brain is not working today. How would I add that code to this query?

SELECT final.[SSN P ], final.TXPD, final.[LFRZ-RFRZ], final.[TC-150], final.[TC-290], final.[TC-291], final.[TC-300], final.[TC-301], final.[TC-420], final.[TC-421], final.[TC-424], final.[TC-530], final.[TC-540], final.[TC-590], final.[TC-591], final.[TC-594], final.[TC-599], final.[TC-976], final.[TC-977], final.[ DOB ], final.[ DOD ]
FROM final
WHERE (((final.[TC-290])="TC-290")) OR (((final.[TC-291])="TC-291") AND ((final.[ DOD ])="dead")) OR (((final.[TC-300])="TC-300")) OR (((final.[TC-301])="TC-301")) OR (((final.[TC-976])="TC-976")) OR (((final.[TC-977])="TC-977")) OR (((final.[TC-420])="TC-420")) OR (((final.[TC-421])="TC-421")) OR (((final.[TC-424])="TC-424")) OR (((final.[TC-150])="TC-150")) OR (((final.[TC-420])="TC-420")) OR (((final.[TC-421])="TC-421")) OR (((final.[TC-424])="TC-424")) OR (((final.[TC-530])="TC-530")) OR (((final.[TC-540])="TC-540")) OR (((final.[TC-590])="TC-590")) OR (((final.[TC-591])="TC-591")) OR (((final.[TC-594])="TC-594")) OR (((final.[TC-599])="TC-599"));

This is the code that produces the query table.