Field with running count of records

Hi,

I have a query with fields such as date, Item Number and Quantity.  I want 
to create an additional field called Count that will act like an autonumber.  
It will assign a value of 1 for the first record, 2 for the second number 
etc.  Can someone tell me how to do this?

Thanks,
-- 
Chuck W
0
Utf
4/26/2007 2:04:00 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1816 Views

Similar Articles

[PageSpeed] 7

There are tons of posts on how to create a "ranking" query. You must have a 
field or fields that uniquely identify the sort order. If you provided 
significant table and field names as well as your desired sort order, someone 
could create the SQL for you if you can't search for and find a solution.

-- 
Duane Hookom
Microsoft Access MVP


"ChuckW" wrote:

> Hi,
> 
> I have a query with fields such as date, Item Number and Quantity.  I want 
> to create an additional field called Count that will act like an autonumber.  
> It will assign a value of 1 for the first record, 2 for the second number 
> etc.  Can someone tell me how to do this?
> 
> Thanks,
> -- 
> Chuck W
0
Utf
4/26/2007 3:04:05 PM
Reply:

Similar Artilces:

Unique records in Pivot Tables
Excel 2000 I have data that stores Client Invitation to Tender and Supplier Contract details. Each Client ITT is uniquely identified. However we send out the same ITT to many suppliers so each request for service is given the same identifier, eg Reference Client/Supplier Contract Name Value Status AC/001 Client blah 4,000 pend AC/002 Client blah2 5,000 pend AS/001 Supplier blah3 3,000 pend AS/001 Supplier blah3 3,000 pend When I create my table by Client to get the total value pending ...

How to disable BCC field in Outlook 2003 clients running on Exchange 2003 ?
How to disable BCC field in Outlook 2003 clients running on Exchange 2003 ? (make BCC field unvalible) In news:u6NrOZiyFHA.2212@TK2MSFTNGP15.phx.gbl, RikS <riks.tw@yahoo.com.tw> typed: > How to disable BCC field in Outlook 2003 clients running on Exchange > 2003 ? (make BCC field unvalible) Don't know how you'd do this offhand, but I wouldn't. If users are misusing BCC, educate them, and make this an HR issue. It really isn't a technical one. HI Riks go to new mail over there on toolbar u will be getting option in that opti...

Records will not open
I am experiencing a problem with certain records within CRM that will not open. The system will try and try to open the record (pinging out two processors) until it finally times out. This is only with certain records but not others... Any suggestions? We experienced a similar problem. We created those records again (Leads) and deleted the old ones... all worked fine then "Phillip Sperry" <phillip.sperry@mxsecure.com> wrote in message news:9FeSb.10804$QJ3.6760@fed1read04... > I am experiencing a problem with certain records within CRM that will not > open. The syst...

Formula counts incorrectly
Help please. Column K contains dates and blank cells. I would like to count how many of these dates fall within a given date range. For example, in the first week in May. Column K contains these dates. 5/2/2005, 5/3/2005, 5/5/2005, 5/5/2005. I would like to count this as 4 dates within the first week of May. I created this formula but the output is 3. =SUMPRODUCT(--(ECNT!F2:F515>=DATE(2005,5,2)),--(ECNT!F2:F515<=DATE(2005,5,6)),--(ISNUMBER(ECNT!K2:K515))) I assume that it is only counting 5/5/2005 one time? I changed the dates in the formula to check a second 2nd date range (5/9 to ...

writing calculated fields
I am trying to avoid starting a Crystal report from scratch, please help me! I need to use the equivalent of the "If, Then, Else" formula in Crystal reports in Report Writer. I need my PO in report writer to: if {IV00101.UOMSCHDL}="xxxxx" then (PO ordered QTY * 30) else if {IV00101.UOMSCHDL}="xxxxx" then (PO ordered QTY * 40) else PO ordered QTY I can only place PO's to the vendor in eaches but I am entering the PO into GP in cases. I have the PO processing side setup correctly, I just need the PO to print now. Thank you! -- CK Create two new calculate...

Could very much use a Field List
I apologize if this is out there already, but it seems that at least a few field names changed from 1.2 to 1.3. Although I had downloaded a field list before, from here, I cannot even find that original post, let alone any more recent that might contain the correct field names. I need to do some custom SQL and would prefer not to reinvent the wheel. Thanks in advance very much to anyone who could help. Bud Izen Salem Oregon Do you have MS Access? Make a new project and attach to the database as your data source. You will be able to see all the tables and the field names. It has been ...

screen freeze after long time running
Hi I have a program written in Microsoft Visual C++ .NET . Its interface is based on CFormView. It includes three tabs to show information and plots. The plots are made by BitBlt() which copies data from the vectors to the screen around every 5 minutes. The problem is that after the program runs continuously for more than 3 days the screen freezes. Nothing is updated. I have to minimize the window and it may return to normal look. Some static labels on the interface also freeze or appear strange look. Does anyone have similar experience ? Thank you for your hints or suggestions! <e...

How can i null out a field going through CRM Update object
I have a custom field that i want to set back to null. Any ideas how i can do this through the object.update function? thanks ...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Print employee record summary Inquiry report
How do i print the employee record summary Inquiry report? There's not a report you can print from the window. You can either do a screen shot of it or create a custom report. -- Charles Allen, MVP "Hazvi" wrote: > How do i print the employee record summary Inquiry report? ...

Combo values from query based on form fields
I am setting the values for a combo box in a form(s) via a query that 'filters' the results with criteria based upon the values of other fields on the form. The combo is a field that is bound. However, this is giving all kinds of problems ranging from Access completely crashing to being asked for the parameter values of those criteria fields when closing the form. I have tried making the combo an unbound field and then setting the value of the bound field to that unbound field after update, but that still leads to the same issues. How can I do this? As example - I have a form w...

HELP! Record Macro in TOOLS > MACRO is gone!
I was in Customize function trying to enable the Stop Recording toolbar and now in TOOLS > MACRO the Record Macro option is gone. I'm working on a project and I need this back. Can someone help? I'm using Excel 2003. Thanks, Joe M. Try this: In the customize window > click the Options tab > push the reset toolbars & menus button. Exit excel and re-load it Micky "Joe M." wrote: > I was in Customize function trying to enable the Stop Recording toolbar and > now in TOOLS > MACRO the Record Macro option is gone. I'm working on a ...

Conditional text field Total
Hi, In the detail section of a report I have a text box that based on a condition, it will be either a 0 or a 1. I want to total the control at the end of the report. I did this in another report a couple of years ago but it is not working now. In the previous report, the ControlSource of the total is = [TextBoxNameFromDetailSection] and it sums correctly. What am I doing wrong?? Thanks. Leah -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201001/1 Thanks but I see what was missing - "running sum over ...

How to record sell of asset & see true cost?
I had bought a car 4 years ago on loan and some downpayment. And recently I sold it. I had the following accounts setup in Money 2004 Delux: 1. Car Asset with only Opening Balance of $30,000 2. Car Loan account which tracked Principal and Interest amounts (the loan is fully paid) 3. Auto category as Expense category with sub categories such as Purchase Cost (downpayment), Fuel costs, Insurance, etc. All car expenses were tracked under one of these sub categories. I would like to record the sell of the car. Where would I record it and how would I categorize so that I can get...

conditional running sum
Is it possible to have a conditional running sum in access. I have found for Excel but no details for access 2007. I would like to have in a report or query that will have a weekly grouping. I have a query that has calculations in it and that will be by source. I have the following sample fields: Date Adbn% 01/01/09 (Mon) 5% 01/02/09 (Tues) 6% 01/03/09 (Wed) 9% 01/04/09 (Thurs) 6% 01/05/09 (Fri) 8% I need it to end up with the following: Date Abdn% 01/01/09 5% 01/02/09 5.5% Avg of Mon&Tues 01/03/09 6.6% Avg Mon...

ADO Field is Nothing error
Running ANY integration on version 10 (have SP 4 installed in Dynamics AND integration manager) yields this error for EVERY user except me (I am an administrator on our domain). There are no symbols or weird charcters. These worked fine a couple months ago, but now no one can run any. These are not even sql optimized eConnect one. They are basic standard integrations. Are you using a shared Integration Manager database or does each user have their own. If the latter, then users may not be seeing the file you are seeing in the location you are seeing it. Best regards, --...

trying to select the last 3 digits of a field
hello all this is the query im using (I am looking to get the last 3 characters of a field): rep_code: Right(tablename.fieldname,3) And I get this error: IDBC--call failed [Informix][Infomirx ODBC Driver][Informix]A syntax error has occurred. (#-201) Is it something wrong with my query or something outside Access 2003 (since the table I am trying to work with its using an ODBC conexion)? Any ideas??? Thanks ...

Continuous Form, Window Top Record Ordinal
On continuous view forms, there should be a form property that has the recordset ordinal of the top-most record currently displayed in the form’s window. For example, if a recordset contains 100 records and the CurrentRecord is 5 and the user scrolls down until the CurrentRecord disappears off the top of the screen, what is the recordset ordinal for the record that is now at the top of the window? Left clicking on the scrollbar slider will display “Record: x of y”, but there should be a property that contains the value of x for code purposes. Perhaps call it “WindowTopRecord...

Extracting records from a list
I would like to extract records from a long list of 500 rows. The list is already sorted by the first column. There is a maximum of four possible values for the first column with multiple values for the other ones. I would like to create four different lists, each with only one of the possible value in its first column by extracting them from the "master" list. Any help or pointer will be gratefully appreciated. Here is some very simple generic code Code: -------------------- Set SourceSht = Sheets("Sheet1") Set DestSht = Sheets("Sheet1") ...

Reversing a cancelled contract in Field Service Contract Adminstra
I am a regular lurker on this board and really appreciate the information I have learned here so far. I have a specific question that I'm hoping someone can help me with. We are on GP 10.0 and use Field Service Contract Administration to manage customer contracts and billing. We processed a cancellation on a contract in Contract Administration (Field Service - Transactions - Contract Administration - Contract Entry/Update - Selected a contract - clicked Cancel button - completed info). This contract is now showing as "Cancelled" in the bottom right corner of that screen. ...

No records!! No errors!! after Sync.
We installed the sales for outlook client on a laptop and then replicated with the central database without any issues. The new user is a part of the business unit that owns all the records in the central database. After the replication there are no records being transferred to this laptop. We tried replicating a few times with no errors provided but no records being transferred. Any ideas appreciated. Thanks, Nathan ------=_NextPart_0001_6228563F Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Nathan~ Check and see if you received any errors within the subscriptions of...

counting dates <= 7 days ago based on criteria in a diff column
I have a spreadsheet that holds all tasks for a project. Column D holds a catagory and column Q holds the date closed. I need a formula (on a separate sheet) that counts all tasks of a specific category that were closed in the past 7 days. I already have a formula that calculates all tasks that were closed in the past 7 days, just need to add the additional criterion of the category. Hi, Try this =sumproduct((sheet1!D2:D30=A2)*((today()-sheet1!Q2:Q30)=7)) A2 on sheet2 has the specific category for which you want to count the closed tasks -- Regards, Ashish Mathur...

excel command that counts conditions met in 2 columns?
is there a form of countif that increments only if conditions are met in two (or more) columns? e.g., countif(colA = 1 and colB = 2) Hi You need SUMPRODUCT. Have a look here for some guidance and post back if you need some more help. http://www.contextures.com/xlFunctions01.html#SumProduct Hope this helps. Andy. "brendalw" <brendalw@discussions.microsoft.com> wrote in message news:F21CD5F8-8679-4A8E-90FC-AA35657C19DC@microsoft.com... > is there a form of countif that increments only if conditions are met in > two > (or more) columns? e.g., countif(colA = 1 a...

How to keep data fields an graphs together when sharing presentati
When I create a graphic chart in PowerPoint and then send it for comments to other parties thru e-mail, they can't edit the data fields. PowerPoint gives the error that can't find the data. How can I keep the worksheets attached presentation slides? And... how can I fix the problem on slides already made? In article <D13996A0-BF28-4E70-8A4A-BEB9E06F0F02@microsoft.com>, Margie wrote: > When I create a graphic chart in PowerPoint and then send it for comments to > other parties thru e-mail, they can't edit the data fields. PowerPoint gives > the error t...

List records that do not include specified value
I have a very basic select query that list all the records from a table SELECT tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no FROM tbl_cngu LEFT JOIN tbl_cngu_recalls ON tbl_cngu.cngu_no = tbl_cngu_recalls.cngu_no ORDER BY tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no DESC; Now I need to apply a twist and do no know how. The [recall_no] field is a numeric field 1,2,3,4,5,... and what I would like to do is supply the query with a [recall_no] value and have the query return those records for which there is no such [recall_no] for a each given [cngu_no]. How would I do this? T...