Query that can number records on the fly???

How can I make a query number each line in sequence of records found? 
Example if a query return 6 lines of records, I would like the query to 
return 1 for record 1, 2 for record 2 and so on.

Example:
1 Red House
2 Blue House
3 Orange House
4 Green House
5 Yellow House


0
Michael
10/9/2007 3:31:02 PM
access 16762 articles. 3 followers. Follow

3 Replies
479 Views

Similar Articles

[PageSpeed] 42

You'd need to include a "ranking" field to give you that number. What is the 
SQL statement of the current query? How is the "number sequence" to be 
defined?

-- 

        Ken Snell
<MS ACCESS MVP>




"Michael Kintner" <michaelkintner@nospam.com> wrote in message 
news:13gn7lnjjaaffa3@corp.supernews.com...
> How can I make a query number each line in sequence of records found? 
> Example if a query return 6 lines of records, I would like the query to 
> return 1 for record 1, 2 for record 2 and so on.
>
> Example:
> 1 Red House
> 2 Blue House
> 3 Orange House
> 4 Green House
> 5 Yellow House
>
> 


0
Ken
10/9/2007 3:38:48 PM
the current sql is select color from houses

I would like to query to number each house as displayed in order from the 
query.

Something like:  select Count+1 as SeqNumb, color from tables.

I know this does not work but only as an example of how it might work. 
(smile)

Mike


"Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
news:%23tO5vpoCIHA.2268@TK2MSFTNGP02.phx.gbl...
> You'd need to include a "ranking" field to give you that number. What is 
> the SQL statement of the current query? How is the "number sequence" to be 
> defined?
>
> -- 
>
>        Ken Snell
> <MS ACCESS MVP>
>
>
>
>
> "Michael Kintner" <michaelkintner@nospam.com> wrote in message 
> news:13gn7lnjjaaffa3@corp.supernews.com...
>> How can I make a query number each line in sequence of records found? 
>> Example if a query return 6 lines of records, I would like the query to 
>> return 1 for record 1, 2 for record 2 and so on.
>>
>> Example:
>> 1 Red House
>> 2 Blue House
>> 3 Orange House
>> 4 Green House
>> 5 Yellow House
>>
>>
>
> 


0
Michael
10/9/2007 4:09:32 PM
So there is no specific "order" that you need, you just want each record to 
have a number?

This is a generic way of doing this:

SELECT Houses.Color,
(SELECT Count(T.*) FROM Houses AS T
WHERE T.Color <= Houses.Color)
AS.ColorRank
ORDER BY
(SELECT Count(T.*) FROM Houses AS T
WHERE T.Color <= Houses.Color);

-- 

        Ken Snell
<MS ACCESS MVP>



"Michael Kintner" <michaelkintner@nospam.com> wrote in message 
news:13gn9ttfqb98a5f@corp.supernews.com...
> the current sql is select color from houses
>
> I would like to query to number each house as displayed in order from the 
> query.
>
> Something like:  select Count+1 as SeqNumb, color from tables.
>
> I know this does not work but only as an example of how it might work. 
> (smile)
>
> Mike
>
>
> "Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
> news:%23tO5vpoCIHA.2268@TK2MSFTNGP02.phx.gbl...
>> You'd need to include a "ranking" field to give you that number. What is 
>> the SQL statement of the current query? How is the "number sequence" to 
>> be defined?
>>
>> -- 
>>
>>        Ken Snell
>> <MS ACCESS MVP>
>>
>>
>>
>>
>> "Michael Kintner" <michaelkintner@nospam.com> wrote in message 
>> news:13gn7lnjjaaffa3@corp.supernews.com...
>>> How can I make a query number each line in sequence of records found? 
>>> Example if a query return 6 lines of records, I would like the query to 
>>> return 1 for record 1, 2 for record 2 and so on.
>>>
>>> Example:
>>> 1 Red House
>>> 2 Blue House
>>> 3 Orange House
>>> 4 Green House
>>> 5 Yellow House
>>>
>>>
>>
>>
>
> 


0
Ken
10/9/2007 4:15:34 PM
Reply:

Similar Artilces:

Can't install Publisher
I'm having a tough time installing MS Publisher 2003 in VHP. I get a 1402 error which pertains to a certain registry key in myt Vista registry. I'm supposed to change the permissions in this key but in Vista Home Premium I can't seem to do that. Anybody have any suggestions? Did you try method 4 in the support document? You receive an error message when you install an Office program or open an Office program or document http://support.microsoft.com/kb/838687/en-us If this is not the document, where did you get the security information? This is a permission issue, you migh...

Chinese computers connecting to Exchange 5.5
Hi, We're trying to get some people in a new Chinese office connected to our Exchange 5.5 SP4 server running on NT 4 via a VPN. They are using Windows XP and Outlook 2003, as we are. When they try to create a MAPI profile, it just times out after forty five seconds when Check Name is clicked. Our firewall shows the RPC and TCP high port connections are occurring. The security event log does show a successful logon. When they try to connect by Outlook Express and POP3, they get a message of "There is no such mailbox on this server" when there definitely is. They are able to u...

Combining an update query with a parameter query
I wanted to know how you can update data in a table using the update query and a parameter query. How did you want to run this update query? In query design, you can just put your parameter in the query. To run it programmatically, you can then use RunSQL in a macro or code. A better programmatic approach might be to build the SQL statement in code, concatenating the parameter value into the string. Then Execute the string itself: you don't need a saved query. The advantage is that you know whether it worked or not. Here's an example: Action queries: suppressing dialogs, whi...

I can't get Outlook to connect to an IMAP4 server
I'm trying to connect Outlook 2000 SP3 (Windows XP Pro, all patches installed, 700 MHz Pentium III, 320 Mb RAM) to Mercury/32 v. 4.01 (Windows 2000 Pro, all patches installed, 350 MHz, 416 Mb RAM). The machines are on a small network running Ethernet (100Base-T full duplex) using a Linksys router/switch. The IMAP server PC has a static IP (192.168.1.222) - the Outlook machine does not. I know that the static IP is correct - can connect to Mercury's HTTP server by pointing Internet Explorer 6 at the IP. I have two problems: 1. When I create an IMAP account, I do not get an...

Deleting Duplicate Records 03-20-08
Hi, I've got my duplicates records query and now I want to delete these 1100 dups in my table of 12,000+ records. Can you please explain how I can easily delete these duplicate records in my table now that I have found them in my query. Thanks very much. Dave dberger16@comcast.net Use a subquery to identify which duplicate records to delete, and which one to keep. This example assumes: - a table named Table1; - a primary key named ID; - "duplicate" is defined as same Company, Address, and City; - you want to keep the lowest ID value. DELETE FROM Table1 WHERE ID <>...

Can you do an e-mail based merge in Pub 2003?
I may be bonkers but I believe before I loaded the beta of Pub 2003 I could merge to an e-mail format in Publisher. The new wizard is good but only offers printing and help dosent help... Is this a capability or not? Publisher now lets you create an HTML publication for emailing through Outlook. It still won't allow you to mail-merge to email. "Thomas Loker" <thomas@loker.com> wrote in message news:#FRZ444WDHA.1620@TK2MSFTNGP12.phx.gbl... > I may be bonkers but I believe before I loaded the beta of Pub 2003 I could > merge to an e-mail format in Publisher. The n...

RE: Limit records shown per report
Information per Karl Dewey's request Query for report is named Bart S1 report: SELECT [Customer Information].Customer_ID, [Customer Information].Customer, [Bart S1].Service_Address, [Bart S1].Employee, [Service Address].Manager, ServiceRequirements2.Type_of_System, ServiceRequirements2.Raw_Water, ServiceRequirements2.Treated_Water, ServiceRequirements2.Cycles, ServiceRequirements2.Inhibitor_Level, ServiceRequirements2.Range_1, ServiceRequirements2.Range_2, ServiceRequirements2.Range_3, ServiceRequirements2.Range_4, ServiceRequirements2.Range_5, 1 AS ZZZ FROM ServiceRequi...

can i use filters to compare data instead of vlookup?
I have to compare membership lists to identify if customers belong to certain groups. Currently, I have been using Vlookup but I notice that there are too many nuisances. Could I use a filter to compare two membership lists and give me a more accurate answer? Maybe try setting up a new sheet with List1, and entering a new column indicating List1 in each row......then adding List 2 at the bottom and entering List2 in the new column for each row in that section............then do Data > Filter > AutoFilter and check the arrows at the tops of the columns to filter from there...........

can i import a database from access into visio #2
im want to import a database from access to visio in hopes it will draw my er digram for me. any syggestions? ...

"Runtime Error 429: ActiveX can not create object"
When I try to open Microsoft Retail Management Service POS, it opens correctly, and seems to have contact with the back-office computer that hosts the database. However, when I try to open either the Manager module or the Administrator module, I get an error that reads "Runtime Error 429: ActiveX can not create object". To my knowledge, the computer was operating correctly when the store closed Saturday, and when we opened Monday morning, this was the message. The only new thing I am seeing is Office 2007, which was installed several weeks ago. Also, one of the employees see...

Contacts I need I can't get to show as default contacts address b
With some great help from this site...!(Thanks) I have reinstalled a clean version of OL2007 back to my computer after recovering the C: drive. All seems well with PST but I cant get my old "Contacts" (That I guess that came with "copied" PST) to show as defalt when I want to choose an e-mail address from my contacts. If I click the "To box" I get offered under address book a balnk contacts called "Accounts" If I click the down arrow my contacts are there and if I double click they are live. How can I make them the default. I have right clicked ...

can i user exchange 2007 client acc & 2003 mailbox?
i have exchange 2003.i would like to install exchange 2007 client access and leave my mailbox on exchange 2003.would i be able to use new owa? No, clients will see the OWA 2003 (or OWA 2000 if the mailbox sits on an Exchange 2000 mailbox server). -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- "sham" <shamun@mekorot.co.il> wrote in message news:envBPoGJHHA.2236@TK2MSFTNGP02.phx.gbl... >i have exchange 2003.i would like to install exchange 2007 client access >and > le...

adding a '+' in front of a positive number
Hello all, anyone know how to make a '+' sign show up in front of a positive number? also, if there is no 'clean' way to do it, how do you concatenate a text string with an equation (i.e., something like '=A1+A12') thanks, Mike Use a custom format like +0.00;-0.00;0.00 ="Text string"&TEXT(A1+A12,"format") so using the custom format ="Text string"&TEXT(A1+A12,"+0.00;-0.00;0.00") -- Regards, Peo Sjoblom (No private emails please) "mike" <mike_newsgroups@yahoo.com> wrote in message news:11180959...

Can you sort Excel data by font color?
I heard that you can sort Excel data by font or column colors. Is this true and how do you do it? Hi see: http://www.cpearson.com/excel/SortByColor.htm and http://www.xldynamic.com/source/xld.ColourCounter.html -- Regards Frank Kabel Frankfurt, Germany "Help Me Sort" <Help Me Sort@discussions.microsoft.com> schrieb im Newsbeitrag news:EF1E9176-5818-4185-9DD7-A74A613914E2@microsoft.com... >I heard that you can sort Excel data by font or column colors. Is this >true > and how do you do it? Only with VBA or helper columns: http://www.cpearson.com/excel/SortByC...

In Microsoft Outlook how can you delete email without opening it?
I am an Outlook Express user and you can click on email once and delete without opening it or double click to open it. Is there a way to delete without opening in Microsoft Outlook? Hello Corey, you wrote on Fri, 26 Aug 2005 06:48:02 -0700: > I am an Outlook Express user and you can click on email once and delete > without opening it or double click to open it. Is there a way to delete > without opening in Microsoft Outlook? Sorry, but I don't understand your question really. Which program is used? Outlook Express or Outlook? -- Best Regards Christian Goeller Hello C...

search for numbers with file/open/tools/search
I need to look for excel documents containing specific numbers. We used to do this using File / Open / tools / search. However after a new installation we cannot get any results back. If we search for plain text in the same documents, the result is shown. So it seems like searching for numbers is the problem. ...

Where condition on a query in VBA
Hey there, Is it possible to have a where condition on a query in VBA? I am opening a recordset based on my query and wanted to add a where condition on the product type as we have many products. So here is my statement: Set rs = CurrentDb.OpenRecordset("qryPromotions", dbOpenDynaset, dbSeeChanges) I want to add "where ProdType = DI" Then after I am done the processing needed for this recordset, I want to open another one, based on the same query, but for a different ProdType. I don't want to have to create 10 different queries for 10 different ProdTypes, ...

show a rounded number without rouning it
how do i show a rounded number without rouning it. for example: an actual number is $724,647 and although i need to use the full (not rounded) number in the calculation, i have to show it as $724,600 in a separate cell. maybe there is a way to program a custom formatting type, but i don't know? thank you! svetlana Hi svetlana, You cannot use formatting to round a number, nor can you use formatting to put the decimal point somewhere else. (unless you count percentage) HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcri...

Random Number Macro
hi everyone what i need is a macro which takes a random number from Cell B2 and puts only the value in Cell B5. I have made it so that the random numbers can only be between 2 and 12 and they are weighted on a distribution. then, if the value in Cell B5 is a 2, 3 or 12 then stop the stream and place a "Lose" in Cell C5, if the value in Cell B5 is a 7 or 11 then place a "Win" in Cell C5. if the value in Cell B5 is any other number (i.e. 4, 5, 6, 8, 9 or 10) then recalculate the random number and the value only in Cell B6. i then want the stream to continue of re...

Query/Expression Help Required!
Hello, i would appreciate any help in regards to the problem i am having. I will try and explain it to the best of my ability (im a newbie to Access 2000) Here is a snipt of the information i have extracted via a query. I have been able to work out Duration via a calculation. "Agent Pin" "Start Time" "AUX Name" "End Time" "Duration" "7015" "13:45:21" "After Call Work" "13:54:18" "537" "7015" "14:30:25" "After Call Work" "14:31:03" &...

can't send/receive #3
Outlook 2003 I keep getting an error Operation Failed. An object couldn't be found (0x8004010F) How do I fix it? ...

Excel Data Connections to Access Queries: Limits?
I have an Access 2007 database with data connections to: + Sharepoint Lists + SMS SQL tables/views I've written Access queries that use tables from those data connections. I want to use Excel to connect to those queries in the Access database to run various reports (such as charts, lists, etc...) However, I'm having 2 problems: 1) In Excel, when I go to Data / Get External Data / From Access, I cannot see all of the queries listed in the Access Database. 2) The queries I do see only return a small subset of records. (I've tested the query within Access, and I should get 17...

Record Fields Collection
I'm trying to set one of the fields in the current record, which I assume I can do if I have the right name for the collection. I thought it was "Fields", but either that's wrong or how I've used it here is wrong????? Me.Fields("SubCat" & level "ID") = lngNewID Where the current record contains fields of: SubCat1ID, SubCat2ID,,,,,,,,,SubCatxID. Bill Bill wrote: >I'm trying to set one of the fields in the current >record, which I assume I can do if I have the >right name for the collection. I thought it was >"Fields"...

can you mail merge in Excel
I have created an enrollment form in Excel (master file) and would like to merge it with data from a different worksheet and then print all of the merged files simultaneously, just like you would in Word. I did not create in Word because some fields require complex calculations. I am not up on macros, so if there is a way to do this w/o macros, even better. Excel doesn't have a merge feature. Perhaps you could do the merge in Word, using the calculations from Excel. Otherwise, you would need programming, to loop through the records you want to print. Ft. Worth user wrote: > I...

Can links between Excel 2003 Pivot Charts and their pivot table b.
Can links between Excel 2003 Pivot Charts and their pivot table be turned off or on? I am trying to show pivot charts for each page variable and if any formating is changed in one of the charts they all revert back to the default chart formatting. Loss of formatting is a known problem with pivot charts. There's information in the following MSKB article, suggests recording a macro as you apply the formatting: XL2000: Changing a PivotChart Removes Series Formatting http://support.microsoft.com/?id=215904 Or, you could create a normal chart from the data. On Jon Peltier's ...