Running Row/Record Number

[Oreynolds]

Hi, I have a totals query that returns any number of results sorted in date order. What I need is to then create a column called [Sequence] that then shows the row/record sequence number as per below:

Year	OrderNumber	SumOfQuoteValue	Sequence
2023	MJ0092763	5987.76	1​
2024	MJ0092763	5987.76	2​
2025	MJ0092763	5987.76	3​

I am sure I am missing something really simple here but I cannot get it to work! Any ideas?

Thanks

 

[June7]

Common topic. Review:

http://allenbrowne.com/ranking.html

https://support.microsoft.com/en-us/office/counting-in-reports-489791e0-dee2-46dd-8a72-f835998bd72e

 

[arnelgp]

as adviced:

SELECT YourTotalQuery.*, (SELECT Count("1") FROM YourTotalQuery AS T WHERE T.Year <= YourTotalQuery.Year) As Sequence
FROM YourTotalQuery Order By [Year].

 

[ebs17]

SELECT
   T.Year,
   T.OrderNumber,
   T.SumOfQuoteValue,
   DCount("*", "TableX", "Year < " & T.Year) + 1 AS Sequence
FROM
   TableX AS T

DCount is significantly faster here than a comparable subquery.
Because of system-given problems (correlated subquery) one should limit the use to small data sets (number of records < 10000).
If TableX is not a table, but a query with its own effort, one should be even more cautious.


In the constellation shown, year is also already a sequential number. This could be "reformatted".

SELECT 
   Year, 
   OrderNumber, 
   SumOfQuoteValue, 
   Year - 
   (
      SELECT 
         MIN(Year) 
      FROM 
         TableX 
   ) 
   + 1 AS Sequence 
FROM 
   TableX

 

[Oreynolds]

as adviced:

SELECT YourTotalQuery.*, (SELECT Count("1") FROM YourTotalQuery AS T WHERE T.Year <= YourTotalQuery.Year) As Sequence
FROM YourTotalQuery Order By [Year].

Hi Sorry, probably being stupid but I cannot get this to work. Here is the SQL from my current query that I want to add another field to called [Sequence], how do I add your script into this query? Thanks

SELECT qryMaintOrderContractYear.Year, qryMaintOrderContractYear.OrderNumber, Sum(qryMaintOrderContractYear.QuoteValue) AS SumOfQuoteValue
FROM qryMaintOrderContractYear
GROUP BY qryMaintOrderContractYear.Year, qryMaintOrderContractYear.OrderNumber
ORDER BY qryMaintOrderContractYear.Year;

 

[Gasman]

You do not. You add the logic into your query.

 

[Oreynolds]

SELECT
   T.Year,
   T.OrderNumber,
   T.SumOfQuoteValue,
   DCount("*", "TableX", "Year < " & T.Year) + 1 AS Sequence
FROM
   TableX AS T

DCount is significantly faster here than a comparable subquery.
Because of system-given problems (correlated subquery) one should limit the use to small data sets (number of records < 10000).
If TableX is not a table, but a query with its own effort, one should be even more cautious.


In the constellation shown, year is also already a sequential number. This could be "reformatted".

SELECT
   Year,
   OrderNumber,
   SumOfQuoteValue,
   (
      SELECT
         MIN(Year)
      FROM
         TableX
   )
   - Year + 1 AS Sequence
FROM
   TableX

Thanks, tried this as follows:

SELECT T.Year, T.OrderNumber, T.SumOfQuoteValue, DCount("*","qryMaintOrderContractYearTotals","Year < " & T.Year) AS Sequence
FROM qryMaintOrderContractYearTotals AS T
WHERE (((T.OrderNumber)="MJ0092762"));

But get the following results which aren't right:

Query2 Query2

Year	OrderNumber	SumOfQuoteValue	Sequence
2023​	MJ0092762	7421​	7050
2024​	MJ0092762	7421​	7616
2025​	MJ0092762	7421​	7727

Any thoughts?

 

[Oreynolds]

You do not. You add the logic into your query.

Sorry I don't follow?

 

[Gasman]

Thanks, tried this as follows:

SELECT T.Year, T.OrderNumber, T.SumOfQuoteValue, DCount("*","qryMaintOrderContractYearTotals","Year < " & T.Year) AS Sequence
FROM qryMaintOrderContractYearTotals AS T
WHERE (((T.OrderNumber)="MJ0092762"));

But get the following results which aren't right:

Query2 Query2

Year	OrderNumber	SumOfQuoteValue	Sequence
2023​	MJ0092762	7421​	7050
2024​	MJ0092762	7421​	7616
2025​	MJ0092762	7421​	7727

Any thoughts?

I suspect you need to use the same criteria in your Dcount/subquery?

 

[ebs17]

SELECT
   T.Year,
   T.OrderNumber,
   T.SumOfQuoteValue,
   DCount("*", " qryMaintOrderContractYearTotals", "OrderNumber = '" & T.OrderNumber & "' AND Year < " & T.Year) + 1 AS Sequence
FROM
   qryMaintOrderContractYearTotals AS T

 

[arnelgp]

SELECT qryMaintOrderContractYearTotals.*,
(SELECT Count("1") FROM qryMaintOrderContractYearTotals AS T WHERE T.Year <= qryMaintOrderContractYearTotals.Year) As Sequence
FROM qryMaintOrderContractYearTotals Order By [Year]

 

[ebs17]

Or as an implementation of the second thought above:

SELECT
   M.Year,
   M.OrderNumber,
   SUM(M.QuoteValue) AS SumOfQuoteValue,
   M.Year -
   (
      SELECT
         MIN(X.Year)
      FROM
         qryMaintOrderContractYear AS X
      WHERE
         X.OrderNumber = M.OrderNumber
   )
   + 1 AS Sequence
FROM
   qryMaintOrderContractYear AS M
GROUP BY
   M.Year,
   M.OrderNumber
ORDER BY
   M.Year

 

[Oreynolds]

SELECT qryMaintOrderContractYearTotals.*,
(SELECT Count("1") FROM qryMaintOrderContractYearTotals AS T WHERE T.Year <= qryMaintOrderContractYearTotals.Year) As Sequence
FROM qryMaintOrderContractYearTotals Order By [Year]

Thankyou, all sorted