A way to speed this up 1

[Blorf]

SELECT Alias.PartID, Alias.AliasID, Alias.Type, Alias.Comment, 1+DCount("PartID","Alias","[PartID]='" & [PartID] & "' And [IdNum]<" & [IdNum]) AS PC INTO Alias_T
FROM Alias;


Have a Part ID, and alias for items in stock. The Part ID is repeating with multiple aliases.

I want each alias to be numbered for the Part ID, like 1, 2, 3 then new Part Id starts, so the numbers start over.

The query works, but takes about 30 seconds to execute.

I am running against a SQL Server table if that makes any difference. IDnum is an autonumber identity field.

Thanks,
ChaZ

 

[PHV]

You may try this:
SELECT A.PartID, A.AliasID, A.Type, A.Comment, (SELECT Count(*) FROM Alias B WHERE B.PartID=A.PartID And B.IdNum<=A.IdNum) AS PC INTO Alias_T
FROM Alias A;
Or this:
SELECT A.PartID, A.AliasID, A.Type, A.Comment, Count(*) AS PC INTO Alias_T
FROM Alias A INNER JOIN Alias B ON A.PartID=B.PartID And A.IdNum>=B.IdNum
GROUP BY A.PartID, A.AliasID, A.Type, A.Comment;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Blorf]

WOW!!!

Second one snapped through like nobody's business. Thank you very very much.

ChaZ