Problem Sequentially Numbering Records

[Erick C]

Hi everybody. I am having a problem with a query that I am working
on.
I have a table containing customer names, trade dates, and dollar
amounts. Customers can have multiple trades on the same day,
sometimes even the same dollar amount. I want to have the table
sorted by name, date, then dollars so the query pulls everything in
from lowest/earliest to highest/latest.
I was trying to make a query that pulled in the customer name and
trade dates, then added a new column called "Row" that had the record
number. I want the query to show all names and dates, since there are
duplicate dates, and assign a record number to each record. The
number should start over at each change in date. So the final result
will look something like below:

(New Field)
Name Date Row
Bob Smith 1/1/10 1
Bob Smith 1/5/10 1
Bob Smith 1/5/10 2
Bob Smith 1/5/10 3
Bob Smith 2/1/10 1
Joe Johnson 1/1/10 1

My SQL is currently counting the names and dates, and where there is a
duplicate date it will not show each date individually. Instead it
shows the customer name and date once with a record count in the row
field. So using my example above, I only get one record for Bob Smith
on 1/5/10 and the Row has 3. I want to get 3 individual records
showing 1/5/10 three times and the row to show 1,2, and 3
respectively.

Here is the SQL that I have been messing with. Any help would be
greatly appreciated.

SELECT tbl_Nov_Maturities.[Name], tbl_Nov_Maturities.[Trade Date],
(SELECT Count(*) FROM [tbl_Nov_Maturities] AS [XX] WHERE [XX].[Name]
=[tbl_Nov_Maturities].[Name] AND [XX].[Name] & [XX].[Trade Date]<
[tbl_Nov_Maturities].[Name] & [tbl_Nov_Maturities].[Trade Date]) AS
Row
FROM tbl_Nov_Maturities
GROUP BY tbl_Nov_Maturities.[Name] & tbl_Nov_Maturities.[Trade Date],
tbl_Nov_Maturities.[Name], tbl_Nov_Maturities.[Trade Date];

 

[Erick C]

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

I have a table containing customer names, trade dates, and dollar
amounts.  Customers can have multiple trades on the same day,
sometimes even the same dollar amount.  I want to have the table
sorted by name, date, then dollars so the query pulls everything in
from lowest/earliest to highest/latest.
I was trying to make a query that pulled in the customer name and
trade dates, then added a new column called "Row" that had the record
number.  I want the query to show all names and dates, since there are
duplicate dates, and assign a record number to each record.  The
number should start over at each change in date.  So the final result
will look something like below:

                                               (New Field)
Name                         Date              Row
Bob Smith                 1/1/10               1
Bob Smith                 1/5/10               1
Bob Smith                 1/5/10               2
Bob Smith                 1/5/10               3
Bob Smith                 2/1/10               1
Joe Johnson              1/1/10                1

My SQL is currently counting the names and dates, and where there is a
duplicate date it will not show each date individually.  Instead it
shows the customer name and date once with a record count in the row
field.  So using my example above, I only get one record for Bob Smith
on 1/5/10 and the Row has 3.  I want to get 3 individual records
showing 1/5/10 three times and the row to show 1,2, and 3
respectively.

Here is the SQL that I have been messing with.  Any help would be
greatly appreciated.

SELECT tbl_Nov_Maturities.[Name], tbl_Nov_Maturities.[Trade Date],
(SELECT Count(*) FROM [tbl_Nov_Maturities] AS [XX] WHERE  [XX].[Name]
=[tbl_Nov_Maturities].[Name] AND [XX].[Name] & [XX].[Trade Date]<
[tbl_Nov_Maturities].[Name] & [tbl_Nov_Maturities].[Trade Date]) AS
Row
FROM tbl_Nov_Maturities
GROUP BY tbl_Nov_Maturities.[Name] & tbl_Nov_Maturities.[Trade Date],
tbl_Nov_Maturities.[Name], tbl_Nov_Maturities.[Trade Date];

You some extra junk in the subquery's Where clause.  You
also need to remove the Group By clause.

Even after dealing with that, you MUST have some other field
in the Where clause that breaks a tie over the Name - date
combiniation.  It probably doesn't make much difference
which field as long as it it can be used to produce a unique
sort.  If your table has an autonumber primary key field,
that can take care of it:

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.pkfield <= [tbl_Nov_Maturities].pkfield)))
AS Row
FROM tbl_Nov_Maturities

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

 

[Erick C]

Hi again Marsh -
I think I answered my previous question. I tweaked the SQL a bit and
it looks like it is doing what it is supposed to do. I added the date
to the name in the first where statement so they would be found
together. I think it is OK, but I wanted to put it out there so if it
does look like something may not work I can fix it now.

SELECT [Name], [Trade Date],
(SELECT Count(*) FROM [tbl_Nov_Maturities] AS XX
WHERE XX.[Name] & XX.[Trade Date] = [tbl_Nov_Maturities].[Name] &
[tbl_Nov_Maturities].[Trade Date] 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

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

I have a table containing customer names, trade dates, and dollar
amounts.  Customers can have multiple trades on the same day,
sometimes even the same dollar amount.  I want to have the table
sorted by name, date, then dollars so the query pulls everything in
from lowest/earliest to highest/latest.
I was trying to make a query that pulled in the customer name and
trade dates, then added a new column called "Row" that had the record
number.  I want the query to show all names and dates, since there are
duplicate dates, and assign a record number to each record.  The
number should start over at each change in date.  So the final result
will look something like below:
                                                (New Field)
Name                         Date              Row
Bob Smith                 1/1/10                1
Bob Smith                 1/5/10                1
Bob Smith                 1/5/10                2
Bob Smith                 1/5/10                3
Bob Smith                 2/1/10                1
Joe Johnson              1/1/10                1
My SQL is currently counting the names and dates, and where there is a
duplicate date it will not show each date individually.  Instead it
shows the customer name and date once with a record count in the row
field.  So using my example above, I only get one record for Bob Smith
on 1/5/10 and the Row has 3.  I want to get 3 individual records
showing 1/5/10 three times and the row to show 1,2, and 3
respectively.
Here is the SQL that I have been messing with.  Any help would be
greatly appreciated.
SELECT tbl_Nov_Maturities.[Name], tbl_Nov_Maturities.[Trade Date],
(SELECT Count(*) FROM [tbl_Nov_Maturities] AS [XX] WHERE  [XX].[Name]
=[tbl_Nov_Maturities].[Name] AND [XX].[Name] & [XX].[Trade Date]<
[tbl_Nov_Maturities].[Name] & [tbl_Nov_Maturities].[Trade Date]) AS
Row
FROM tbl_Nov_Maturities
GROUP BY tbl_Nov_Maturities.[Name] & tbl_Nov_Maturities.[Trade Date],
tbl_Nov_Maturities.[Name], tbl_Nov_Maturities.[Trade Date];

You some extra junk in the subquery's Where clause.  You
also need to remove the Group By clause.

Even after dealing with that, you MUST have some other field
in the Where clause that breaks a tie over the Name - date
combiniation.  It probably doesn't make much difference
which field as long as it it can be used to produce a unique
sort.  If your table has an autonumber primary key field,
that can take care of it:

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.pkfield <= [tbl_Nov_Maturities].pkfield)))
AS Row
FROM tbl_Nov_Maturities

- Show quoted text -- Hide quoted text -

- Show quoted text -