make a field within a query to increment values for purpose of output only 1

[03Explorer]

I have a query returning any number of rows. For the sake of the user, I want a field that is just a counter (incremental field) to be displayed. Example below:

No. UserName
--- --------
1 Black, Bob
2 Cash, John
3 Doe, Jane
4 Caruba, Crystal
5 Dastardly, Richard


No rhyme or reason for the numbering other than a label for the row. I would like it to allow me to sort by UserName, but for simplicity and it works I am okay with the ordering by 'No.'

IF I were to do this in T-SQL:

Select 
  ROW_NUMBER() OVER( ORDER BY SPG.PreGeneratedAutoID ) AS 'No.',
  Surveyee = AM.LegalLastName + ', ' + AM.LegalFirstName
FROM 
  tblSurveysPreGenerated SPG
  Left JOIN AssociateMaster AM ON SPG.Childid = AM.AssociateID
WHERE 
  PreGeneratedStatus = 2

ORDER BY 1 asc

 

[dhookom]

Try a subquery like:

Select 
  (SELECT Count(*)
   FROM AssociateMaster M 
   WHERE M.LegalLastName & M.LegalFirstName <= AM.LegalLastName & AM.LegalFirstName) as Num,
  AM.LegalLastName & ", " & AM.LegalFirstName AS Surgeyee
FROM 
  tblSurveysPreGenerated SPG
  Left JOIN AssociateMaster AM ON SPG.Childid = AM.AssociateID
WHERE 
  PreGeneratedStatus = 2

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

Isn't that creating a value in the Num column that is the count of items? I just need num to be 1, 2, 3, 4, etc a counting for rows, not items in a row.

My intent is having a list box show two columns... 1=Num (just counts from 1 to number of rows ); 2=Surveyee. Purpose is for user to see a list of items... the number is just for visual purposes row 3 has num = 3; row 15 has num = 15.

Thanks

 

[dhookom]

Did you try the query?
Are there any duplicate LegalFirstName and LegalLastName values in the results or are these unique?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

The query gave me random numbers for num:
(41, 3, 129, 50, 82, 19, 117, 122, etc...)

There are duplicate First/Last names in the list, yes. I don't want them consolidated because they represent a different data set.

 

[dhookom]

If you added ORDER BY AM.LegalLastName & ", " & AM.LegalFirstName they might seem less random but I expect there would be duplicates. What is a unique column in your query?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

I'll try order by AM.LegalLastName & ", " & AM.LegalFirstName... same numbers, but not 1,2,3,4,...
The unique value in the query could be... SPG.PreGeneratedAutoID (it's the PK for the table)

 

[dhookom]

Then try something like:

Select 
  (SELECT Count(*)
   FROM tblSurveysPreGenerated S 
   WHERE S.PregeneratedAutoID <= SPG.PregeneratedAutoID) as Num,
  AM.LegalLastName & ", " & AM.LegalFirstName AS Surgeyee, SPG.PregeneratedAutoID
FROM 
  tblSurveysPreGenerated SPG
  Left JOIN AssociateMaster AM ON SPG.Childid = AM.AssociateID
WHERE 
  PreGeneratedStatus = 2 
ORDER BY SPG.PregeneratedAutoID

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

Select 
  (SELECT Count(*)
   FROM tblSurveysPreGenerated S 
   WHERE S.PregeneratedAutoID <= SPG.PregeneratedAutoID) as Num,
  AM.LegalLastName & ", " & AM.LegalFirstName AS Surveyee
FROM 
  tblSurveysPreGenerated SPG
  Left JOIN AssociateMaster AM ON SPG.Childid = AM.AssociateID
WHERE  PreGeneratedStatus = 2 
ORDER BY SPG.PregeneratedAutoID

I removed the third column in the select and it works like I was planning.

Why is the WHERE clause using the <= symbol? that part I am not following or understanding.

THANKS!!

 

[dhookom]

The <= is in the subquery. The subquery basically states:

Count the records in the tables where the unique value is less than or equal to the same unique value in the main query.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

VERY COOL! Thank you for sharing! I wasn't aware that you can use something other than EQUAL for matching but now you've expanded the understanding... I'm off to share with my TSQL Coworkers!!

You made my Day!!!

 

[03Explorer]

Oh shoot... now that i have test data in the table and it's returning the actual count of the rows of all the data.... so the 1,2,3,4,5, ... etc
means if the data that I want to display begins on row 24... the counter starts at 24, 25, 26, 27, 28, etc

So I updated the code so it only dealt with the PreGeneratedStatus of 2's

SELECT 
  (SELECT Count(*)    
   FROM tblSurveysPreGenerated S     
   WHERE S.PregeneratedAutoID <= SPG.PregeneratedAutoID AND S.PreGeneratedStatus = 2) AS Num, 
   AM.LegalLastName + ', ' + AM.LegalFirstName AS Surveyee, 
   P.PracticeCode

FROM 
  tblSurveysPreGenerated AS SPG 
  LEFT JOIN AssociateMaster AS AM ON SPG.Childid = AM.AssociateID 
  LEFT JOIN tblPractice AS P ON SPG.PracticeID = P.aid

WHERE 
  SPG.PreGeneratedStatus=2 AND 
  IsNull(SPG.Processed)<>False AND 
  IsNull(SPG.GeneratedDate)<>False AND 
  IsNull(SPG.GeneratedTime)<>False

ORDER BY 
  SPG.PregeneratedAutoID Asc;

It now is working like a rock star.

Thanks again Duane!