Show a counter field in an Access 2003 query and report

I have this summary by item report and I want to make this special field that basically adds a +1 to each line item that's listed.  Here's the query listing and as you can see its tied to another query.  The rest is the listing of what is produced...

SELECT DISTINCTROW qTotals.Equip, qTotals.Name
FROM qTotals
GROUP BY qTotals.Equip, qTotals.Name;

Here's some of the output and all I'm trying to do is have another field that says 31022 is line item 1, 52140 is 2, 52150 is 3, etc.

Equip      Name
31022      Shelving Unit: Chrome Wire, 5 Adjustable Shelves, 60x24x74
52140      Disposal Station: Mobile, Includes Locking Waste Dolly and 40 Gallon Waste Container
52150      Station: Bottle Filling, Automated
52222      Cage Rack: Ventilated, Rodents, Double Sided, Includes Cages, Watering Manifold Assembly, Drinking Valves, On Line Flushing System
52230      Trolly: with Supply/Exhaust Fan Units, Includes Pre-Filters and HEPA Filters, with Temp and RH Display

I'm sure that part of the solution is creating a global function that does an internal counter = counter + 1
like the following:  But the problem is that its not adding right and I have to be sure that I add the right field as far as the 'X' value is concerned.  

Also, if I just did a Count([Equip]) for equip code 31022 then it would say 2 because there are 2 codes in the listing.  I just want 313022 to say line item 1 and then next code line item 2...

Finally, it would all be so easy to add this query to Excel and have the 3rd column add +1 but I want to try to get the query to do this...

Option Compare Database
Option Explicit

Global Cntr
Global sCode As String
Global Cntr2
'3. Type the following functions in the module:'*************************************************************
' Function:  Qcntr()
' Purpose: This function will increment and return a dynamic
' counter. This function should be called from a query.

Function QCntr(x As String) As Long
  If sCode = "" Then sCode = x
  If x <> sCode Then
    Cntr = 0
    Cntr2 = Cntr2 + 1
  End If
   Cntr = Cntr + 1
   QCntr = Cntr + Cntr2
End Function

' Function:  SetToZero()
' Purpose: This function will reset the global Cntr to 0. This
' function should be called each time before running a query
' containing the Qcntr() function.

Function SetToZero()
   Cntr = 0
End Function


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RyanProject Engineer, ElectricalCommented:
You could create the linenumber field in the table. Then open a recordset, containing your relavent data, ordered by whatever. Keep the recordset size minimal to increase time. and ForwardOnly-Optimistic.

then something like...
dim rs as adodb.recordset
dim i as integer
with rs
  .open "<YOUR SQL HERE>",currentproject.connection,adOpenForwardOnly,adLockOptimistic
  do while not .eof
    !LineNumber = i
    i = i + 1
end with

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RyanProject Engineer, ElectricalCommented:
This isn't the most ideal, but line numbering sucks in Access. Search around EE, this is a common question that comes up, theres lots of different solutions.
stephenlecomptejrAuthor Commented:
Mr Bullwinkle,

Thank you very much for your reply.
Your solution will work part of the way for what I need to do with slight tweaking.

Basically what I could do is create a temporary table from the summary by item query.
Then once I get that temporary table created I then take your coding and add the counter field.
Finally, take the report and tie it to that newly made table.

Thanks again!
RyanProject Engineer, ElectricalCommented:
Yes. It's not the cleanest way, nor the fastest, but its one that will work, and you have full control.  You can also instead of just putting 1,2,3..could use a string and put Line 1, Line 2, Line 3. If you so desired.  But then again, appending that in the report control with formatting would be more efficient.

Well, hope it works out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.