Sequential number in a query

Hi All,

I am working on a YTD Sales report. I would like to know if I can create a
cell in a query that will be a squential number. Starting with 1 on up. If so
how would I do this?

Report would look something like:
Num  Customer    YTD Sales
1       Bobs Sales   495,000
2       Denny's        395,000
3       Biffs Supply  390,000
4....

-- 
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.accessmonster.com

0
mattc66
11/3/2007 12:23:35 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
2924 Views

Similar Articles

[PageSpeed] 15

You can use a text  box in the report:
    Control Source: =1
    Running Sum: Over All

-- 
Duane Hookom
Microsoft Access MVP


"mattc66 via AccessMonster.com" wrote:

> Hi All,
> 
> I am working on a YTD Sales report. I would like to know if I can create a
> cell in a query that will be a squential number. Starting with 1 on up. If so
> how would I do this?
> 
> Report would look something like:
> Num  Customer    YTD Sales
> 1       Bobs Sales   495,000
> 2       Denny's        395,000
> 3       Biffs Supply  390,000
> 4....
> 
> -- 
> Matt Campbell
> mattc (at) saunatec [dot] com
> 
> Message posted via http://www.accessmonster.com
> 
> 
0
Utf
11/3/2007 3:13:00 AM
Not sure I understand. I don't want to do it on a report. I'd like to do it
on form or in a query.

Duane Hookom wrote:
>You can use a text  box in the report:
>    Control Source: =1
>    Running Sum: Over All
>
>> Hi All,
>> 
>[quoted text clipped - 8 lines]
>> 3       Biffs Supply  390,000
>> 4....

-- 
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200711/1

0
mattc66
11/3/2007 4:52:14 AM
My suggestion was based on your comment "Report would look something like:"

If you want this outside of a report, you must identify a unique field that 
provides the numbering order. Then you can use this field in a subquery or 
DCount() expression.

-- 
Duane Hookom
Microsoft Access MVP


"mattc66 via AccessMonster.com" wrote:

> Not sure I understand. I don't want to do it on a report. I'd like to do it
> on form or in a query.
> 
> Duane Hookom wrote:
> >You can use a text  box in the report:
> >    Control Source: =1
> >    Running Sum: Over All
> >
> >> Hi All,
> >> 
> >[quoted text clipped - 8 lines]
> >> 3       Biffs Supply  390,000
> >> 4....
> 
> -- 
> Matt Campbell
> mattc (at) saunatec [dot] com
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200711/1
> 
> 
0
Utf
11/3/2007 5:18:01 AM
Reply:

Similar Artilces:

Hide duplicates in a query ...
Hi folks I would like to hide any duplicate records in a query. I have already used the "Unique Values"parameter, but I would like to hide any records where the first field is duplicated. Did that make any sense?? ... eg. Field 1 Field 2 1 a 1 a 2 b 2 c I would only like to show one record for "1" & one for "2" etc ..... Can anyone help? Thanks If it makes no difference which record of the group you want to see, you could use an aggregate query where you group by field 1 and specify firs...

Elapsed Time returned from query
I am using excel to query DB2. The field in DB2 Elapsed_Time has been calculated from a End_Date_Time Start Date Time. The Problem is that excel treats this value 00:00:00 as mm/dd/yyyy 00:00:00 AM/PM and there is nothing I can do to change the format once it is in Excel. Right now I have reverted to querying the Start/End Date Time and performing the calculation in excel and formating as an elapsed time. I am using the Microsoft Query Wizard from excel. Unfortunately that will not work for some of the data as CPU Elapsed Time is calculated and there is no CPU Start/Stop D...

Query Help 12-18-09
Hi: I=92m having trouble with the following query=85any suggestions? * tblProposal is a linked table in SQL Server * qryProposalFilter is a local query that limits the list of ProposalID to the desired subset. What I want to do is return the value of the field ContingencyGeneral if the value is the same for all the proposals in the desired subset. If there are different values, I want the word =93Varies=94 to be returned. I=92m opening a recordset in code and pulling out the returned value to plug into a Word report. Thru much trial and error, I came up with the following ...

Rerun an open query in Access 2007
Hi, In Access 2003 I was able to place the "exclamation point" RUN button on my Access toolbar so that I could easily re-run a query that I already had open (rather than try to find it in a list of a few hundred queries). The queries I run usually ask for user input, so by re-running them I am able to give different input. Is there any way in Access 2007 to place a "RUN button" on the "Home" tab toolbar? -- Dan R Changing the ribbon is quite a bit more work, but it's easy to customize the Quick Access Toolbar. If you really want to customize the r...

Office 2004
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3169551770_24466 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit I can�t remember if this was an issue with Office X, but I�ve found when I reboot my Mac, and the Office Notifications daemon appears, it does not initially show the number of reminders in its Dock icon. I have to go the the Notifications window, and perform some action on on of them (snooze, dismiss, whatever) and then the Dock icon shows the ...

Lock the number of pages in a word Doc
Hi, I really need some help, quick :-) I have built a Word doc in Office 2003, I have managed to to built different headers on different pages. BUT! I need to look the document to 4 pages, how? I don't won't page number 5, just 4 pages thats it.... Thank you!!!! So what do you want Word to do if you (or someone else) put more than 4 pages worth of text and graphics in the document? The usual way to do this is to use tables with fixed row heights to create a layout that won't change. If more text is typed or pasted into a cell than will fit within the fi...

limit number of rows & colums in a worksheet
is there a way to limit the number of available colums & rows in a particular worksheet. --- Message posted from http://www.ExcelForum.com/ David, You can hide all the unused rows and columns, or you could set the ScrollArea property to a specific range address. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "davidbrowne17" <davidbrowne17.ya2hm@excelforum-nospam.com> wrote in message news:davidbrowne17.ya2hm@excelforum-nospam.com... > is there a way to limit the number of available colums & rows in a > ...

OLE DB Query in Excel
Is it possible to pass parameters to SQL in an OLE DB query within Excel. For example: Select * from property where prop_id = ? Somehow link the parameter to a cell in the spreadsheet or something in like. Thanks. Hi Joe, You can link parameters from your spreadsheet to SQL statements but right off hand I can't remember if you need to do this with a VBA SQL line or you can imbed it directly in an SQL statement. Could you show us the SQL statement or VBA SQL line. -- Thanks, Shane Devenshire "JOE OROZCO" wrote: > Is it possible to pass parameters to SQL in an OLE...

Update Table Field Value with SQL Query
Good day, I am seeking help to resolve a query that I want to run from a class Module in access 2003. I first designed the query by linking the two tables and set the wanted criteria and then copied the query from SQL View and pasted it in the Class Model which is as follows: UPDATE Tbl_Risk_LocalProfiles LEFT JOIN Tbl_Risk_LocalProfiles_ResponsiblePersons ON Tbl_Risk_LocalProfiles.ProfileID = Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileID SET Tbl_Risk_LocalProfiles_ResponsiblePersons.RO_ProfileStatus = Tbl_Risk_LocalProfiles.Frame_ProfileStatus WHERE (((Tbl_Risk_Lo...

Wrong week number
Hi, (Access 2007) I have a query with an expression in a field like: WEEK: Format$([Table1].[Date1];'ww') Above expression is always giving next week's week number if the day is Sunday. What can it be the problem. Any idea? Best Regards. Art, No, this is not wrong. The date formats use Sunday as the first day of the week by default. If you want it to use another day, you need to specify. Also, drop the $. To start the week on Monday, try it like this: WEEK: Format([Table1].[Date1];'ww';2) -- Steve Schapel, Microsoft Access MVP Art Vandaley wrote: > Hi...

Page Numbering #4
I need to do page numbering on my worksheet so I can create a document where 2 facing pages of a sprial bound book will read...left side 2 and the right side 2A. Please advise.... "Amy" <anonymous@discussions.microsoft.com> wrote in message news:7885F161-FA05-4891-9174-9935E2253A6C@microsoft.com... > I need to do page numbering on my worksheet so I can create a document where 2 facing pages of a sprial bound book will read...left side 2 and the right side 2A. Please advise.... This is not possible in Excel. Dear Amy As far as I am aware, this can't be done in ...

displaying Query results to subform
First--the disclaimer--I am in no way a programmer, but I am working diligently to support my team in any way possible. With that said, I have painstakingly created a simple build SQL string code in Access Visual Basic Editor: Private Sub Command8_Click() Dim SQL As String SQL = "SELECT * " & _ "FROM [BCSG_CARDFILE] " & _ "WHERE ((([BCSG_CARDFILE].[IMPORT_FILENAME]) Between " & _ """" & _ Forms![BCSG_CARDFILE_SEARCH]!cboDateFrom.Value & _ """" & _ " And &quo...

Still showing numbers not text in display
Since my previous post I went through both tables and did the lookup wizard on all the fields and selected only the RATING not ID. When I pull both tables into my query it shows the number for some and the word for others. The frustrating part is they were all created the exact same way so why are they displaying differently? I checked all the properties on both tables and everything is exactly the same and the information is still displaying in 2 different formats. Help before I pull all my hair out... =( Thanks for the quick responses by the way! Also... When I pull each...

formula for non-sequential columns
I've got data in columns separated by two other columns. I wish to create a formula in another column that will include the data from these two non-sequential columns. How do I do this? Ex: data are found in columns G(rows 1) and J(rows1) and would like to create formula which lists such data in column S in the following order: info from column G, row 1 then info from column J, row 1. Thank you for any assistance. I've pulled out most of what hair I've got left over this. in column "S" =G1 & J1 -- bgeier ------------------------------------------------------...

Cannot run make-table query
When I run a make-table query in my Access 2003 database, I receive error: Record(s) cannot be read; no read permission on 'Measures'. Please note that I can open table Measures (linked) and edit, add, or delete data in table view, so I believe the message is misguided. While this may possibly be security related, it should not be. We had put this database through the security wizard, but when deployment did not work out, I did the following three things: 1. Gave Admin full rights on all objects. 2. Added Admin to the Admins group, which has full rights. 3. Gave the Users group f...

Question about query optimization
Hi All, i have one query in ms access Database 2002. which is like: select distinct ( Field1) from table1 where Field1 not in ( select Field1 from table2) ; Table1 has more than 70000 records I guess even more.. So when i open ms access database and run this query it run fine and around 30-40 minutes it returns with result, But after this If I try to run the same query again , no result :( at all no matter how long I wait. And again If I close the database reopen and run i get the results. Could you please advise how to resolve such strange issue. Please help. Than...

large picture
I have a few pages in my document with 9x7 pictures that work well if I create a separate section, and put the page in landscape mode. (I additionally have to change the top margin to 1.5" (to match the left margin being 1.5"), but that's not really what I"m asking about.) The problem is that the page number in the header comes out landscape-oriented instead of portrait-oriented, which is unacceptable to the editor. Is there a way of making the page number be rotated so it shows up as portrait using this method? I need to keep the page number as a page ...

counting number of values
Hi, I am glad I have discovered this post, because I am new to excel and on a steep learning curve due to the requirements of my work. I have a large one-column list of values which includes duplicate values. i would like to calculate and display the number of times each value occurrs. for example. 1 2 4 4 6 The result should display something like 1=1, 2=1, 4=2, 6=1. Any help would be appreciated. Thanks. Mark =) Mark, Select your column of numbers - make sure that there is a header - and then choose Data / Pivot table and click OK. This will create a new sheet with a blank pivot ta...

Access 2003 number of records
Hi all, I'm wondering if there's a limit/recommendation in the number of records that it can be inserted in an Access 2003 table before the performance is affected considerably. I know that in Access 200 the limit was around 100,000 records but I'd like to know it for Access 2003. thanks in advance, Marc Soleda Last I new the limit was the file size of the database itself. AFAIK this has not changed from 2GB so if you have tiny records you can have a huge number, with fat records - obviously nowhere near as may. The performance of the database in Access is much the sa...

Date numbering out of order in Outlook 07
Hi guys, I am a function organiser and within Outlook I have mail files for each event I am running. So for example, I would have a mail folder set up that looks like this: 12 December 1 Dec Pharmacy Guild 6 Dec 15 Dec Event Central 25 Dec Christmas Day But what I find is that Outlook puts things in a different order than I would expect and so I have ended up with this: [IMG]http://i14.tinypic.com/6kz8umq.jpg[/IMG] Is there a work around (other than renaming every sub-folder a-z)? Thanks in advance for help on sorting in order, Jo Actually, none of your sub-folders a...

Formulas in custom number formatting allowed?
I need a quick way to change both calculated and data entry cells in a fairly large workbook into custom number formats based on whether they are integers or have up to 2 decimal places. The custom cell formats I would like to get working TOGETHER are: [=INT()]# #.## has Microsoft added anything to Excel to finally make this possible? The macros to do this seem to take a lot of time to work through the workbook. On Thu, 27 Aug 2009 23:45:26 -0700 (PDT), kgriff <kgriffee@gmail.com> wrote: >I need a quick way to change both calculated and data entry cells in a >fairly large wor...

Random Number Generator
Anyone anyone -- Flamike ----------------------------------------------------------------------- Flamikey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=461 View this thread: http://www.excelforum.com/showthread.php?threadid=26530 One way: =INDEX({"A",2,3,4,5,6,7,8,9,10,"J","Q","K"},INT(RAND()*(13) +1)) HTH Jason Atlanta, GA >-----Original Message----- > >Anyone anyone? > > >-- >Flamikey >---------------------------------------------------------- -------------- >Flamikey's Profile: htt...

Numbering pprinted pages
Hi I have a single worksheet that prints out on a single A4 page. I need to print out over 100 copies of the page but I need each one numbered uniquely, from 1 to 108, is there a way do do this? thanks Daniel Daniel Only with code. If you would like to explore that post back -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Daniel" <nospam@nowhere> wrote in message news:%23WGe$HCIFHA.3536@TK2MSFTNGP14.phx.gbl... > Hi > > I have a single worksheet that prints out on a single A4 page. > I need to print ...

Max Account length and number of segments
Just discovered today (after about 10 straight hours of tinkering) that your max account length and number of segments will affect your ability to test 3rd party dictionaries in TWO (Fabrikam). I had the account length maxed out to 66 and the number of segments set at 5 (sufficient for TWO's accounts) however I got an error everytime I tried to debug in the script "Setup_Account_Format". I found that switching to max account length 12 and max number of segments 3 alleviated this problem. Not sure if this has been posted anywhere else, but I sure couldn't find it so ...

Ability to Change PO Number When Generating PO From Sales Order
When generating a Purchase Order using the SOP to POP link (from Sales Transaction Entry) would like the ability to change the next PO Number. ...