×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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:

CODE --> TSQL

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 

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

Try a subquery like:

CODE --> SQL

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

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

(OP)
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 (n)); 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

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

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

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

(OP)
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.

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

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

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

(OP)
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)

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

Then try something like:

CODE --> SQL

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

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

(OP)

CODE --> SQL

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!!

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

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

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

(OP)
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!! :D

You made my Day!!!

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

(OP)
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

CODE --> Code

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close