Reply to thread

Message: [QUOTE="Erick C, post: 13886122"] Marsh - Thank you for the reply. I apologize for the jumbled mess in my SQL, I am still learning how to write the stuff. I had another query doing something similiar in another database and I just tried to modify what I already had, so it is no surprise that I had way too much crap in the statement. I took your advice and re-imported all of my source data so I could add a primary key to the table. The SQL is adding the Row column correctly, but it the record count is not working correctly. As it is right now if I have a customer with 6 records, one with a 1/1/10 date and five with a 1/10/10 date for example, I get a count of 1 through six in the Row column. I am hoping to get a count of 1 for the 1/1/10 date and a count of 1 through 5 for the 1/10/10 date. So basically the SQL is not just restarting the count at each change in customer name, but also at each change in date. Here is the updated SQL that I am using. I just added the field name for the primary key number. SELECT [Name], [Trade Date], (SELECT Count(*) FROM [tbl_Nov_Maturities] AS XX WHERE XX.[Name] = [tbl_Nov_Maturities].[Name] AND (XX.[Trade Date] < [tbl_Nov_Maturities].[Trade Date] OR (XX.[Trade Date] = [tbl_Nov_Maturities].[Trade Date] AND XX.[ID] <= [tbl_Nov_Maturities].[ID]))) AS Row FROM tbl_Nov_Maturities [/QUOTE]