Show a counter field in an Access 2003 query and report

Posted on 2008-02-12
Medium Priority
Last Modified: 2013-11-27
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


Question by:stephenlecomptejr
  • 3
LVL 13

Accepted Solution

Ryan earned 2000 total points
ID: 20908551
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

LVL 13

Expert Comment

ID: 20908561
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.

Author Comment

ID: 20910020
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!
LVL 13

Expert Comment

ID: 20910193
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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question