Not sure if this will help if you're querying a DW... this is one way:

SELECT c.CarMake

, c.Country

, c.EventDate

, c.RowNum

FROM

(SELECT CarMake

, Country

, EventDate

, ROW_NUMBER() OVER (PARTITION BY CarMake, Country ORDER...

Inside Access, right-click on one of your linked tables, select Linked Table Manager from the menu, and it will show you where the tables are pointing to.

Thanks for the help. I think it's closed. I tested it with grade 4, just to short-circuit the record creation, since the "fatal" grade is somewhat arbitrary. ...

You're welcome... if you run my code, make sure the CREATE TABLE code for Protocol is before the code for Enroll. (That part was confusing in my post, I...

You can do it using CROSS APPLY.

Read these two articles (definitely worth the read!):

http://www.sqlservercentral.com/articles/APPLY/69953/ (Part I)

http://www.sqlservercentral.com/articles/APPLY/69954/ (Part II)

(Basic plan)

Get the TOP 1 Values from the child table (pass...

"I cover this in my Common TSQL Mistakes SQL Saturday session..." (just not #272? Not coming to NashVegas?? Drat!

And don't forget MSDB. That's where all your jobs etc are stored.

Before the original server goes away, make sure you have a working SQL Server instance where you need...

okay... I think this fixes the "you can't report symptoms after you're dead" issue...

CREATE VIEW vwEnrolleeFatalCycle

AS

SELECT enrollmentID

, MIN(cycle) AS FatalCycle

FROM Symptom INNER JOIN Data ON Symptom.SymptomID = Data.ID

WHERE Grade =...

Okay, here's the DELETE as a CTE, which is probably the easiest way to do it... (once you read the crib notes)

; WITH CTE_DupeAddr(DupeID, ID, Cid, FName, LName, DOB, City)...

So sue me.

--Mark the records we want to delete, just to be sure we don't do anything terrible.

UPDATE #TableA

SET IsActive = 1

WHERE [SID] IN

(SELECT MIN([SID]) AS GoodID

FROM #TableA

GROUP BY ...

So in theory they could merge two records that don't match? Could you post an example? Explain why you can't do it with either a CTE or a...

Cursor is a bad word around here... careful.

That said, I don't think you need a cursor at all.

CREATE Table #TableA

(

ID int IDENTITY(1,1),

SID INT,

FNAME varchar(50),

LNAMEvarchar(50),

DOB date,

CITYvarchar(50),

IsActive bit

);

GO

INSERT INTO #TableA

VALUES ('245','Smith','John','1/10/1998','Los Angles','0');...

Made a little progress. I added some columns just to understand what's going on (since I'm sort of stumbling around in the Dark Ages). Here's my latest attempt:

DECLARE...

What's the maximum number of cycles? Can they overlap? Presumably you want a datetime on each symptom row?

As best I can remember, protocols run for a non-predetermined period of...

Correct. It's modeling cancer treatment therapy, so sometimes they get fatal complications, and after that you can't test them anymore. (Cheery huh?)