How do I number the entries in a query?

[Guest]

I have a query that looks like below and I want to add a new column that
combines the projectID and another new column that numbers each entry
incrementally by projectID. I need to know how to number items in a query by
projectID.

ProjectID Product Description Number NewColumn
1688 item 1 0001 1688.0001
1688 item 2 0002 1688.0002
1944 item 1 0001 1944.0001

Any help would be great.

Thank you,
JCL

 

[[MVP] S.Clark]

I do it with VBA. Loop through the dataset and perform the numbering.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Duane Hookom]

If your query is updateable you might use:
Update qselYourQuery
SET NewColumn = [ProjectID] & "." &
Format(DCount("ProjectID", "qselYourQuery", "[PrimaryKeyField]<=" &
[PrimaryKeyField]) , "0000");

I question the requirement for this since it isn't good practice to save
calculated values.