auto numbering in a query

Hi all,
I need to build a query that have in a field auto numbering and in  a
certain formating
for example:
0001
0002
.......
0012
.......
is it posible?

0
thread
4/5/2007 5:41:28 PM
access 16762 articles. 3 followers. Follow

12 Replies
1137 Views

Similar Articles

[PageSpeed] 6

Is this to produce data that will be viewed in a report, by any chance?

"thread" <yaniv.dg@gmail.com> wrote in message 
news:1175794888.467011.108490@w1g2000hsg.googlegroups.com...
> Hi all,
> I need to build a query that have in a field auto numbering and in  a
> certain formating
> for example:
> 0001
> 0002
> ......
> 0012
> ......
> is it posible?
> 


0
BruceM
4/5/2007 6:42:40 PM
On Apr 5, 2:42 pm, "BruceM" <bam...@yawhodawtcalm.not> wrote:
> Is this to produce data that will be viewed in a report, by any chance?
>
> "thread" <yaniv...@gmail.com> wrote in message
>
> news:1175794888.467011.108490@w1g2000hsg.googlegroups.com...
>
> > Hi all,
> > I need to build a query that have in a field auto numbering and in  a
> > certain formating
> > for example:
> > 0001
> > 0002
> > ......
> > 0012
> > ......
> > is it posible?

is that case a increment textbox will be easier

0
Franck
4/5/2007 7:01:22 PM
"Franck" <the_darkblast@hotmail.com> wrote in message 
news:1175799682.718606.309150@w1g2000hsg.googlegroups.com...
> On Apr 5, 2:42 pm, "BruceM" <bam...@yawhodawtcalm.not> wrote:
>> Is this to produce data that will be viewed in a report, by any chance?
>>
>> "thread" <yaniv...@gmail.com> wrote in message
>>
>> news:1175794888.467011.108490@w1g2000hsg.googlegroups.com...
>>
>> > Hi all,
>> > I need to build a query that have in a field auto numbering and in  a
>> > certain formating
>> > for example:
>> > 0001
>> > 0002
>> > ......
>> > 0012
>> > ......
>> > is it posible?
>
> is that case a increment textbox will be easier

My point exactly. 


0
BruceM
4/5/2007 7:30:35 PM
i'm exproting the query to excel,
this is why i need the autonumering

BruceM =D7=9B=D7=AA=D7=91:
> "Franck" <the_darkblast@hotmail.com> wrote in message
> news:1175799682.718606.309150@w1g2000hsg.googlegroups.com...
> > On Apr 5, 2:42 pm, "BruceM" <bam...@yawhodawtcalm.not> wrote:
> >> Is this to produce data that will be viewed in a report, by any chance?
> >>
> >> "thread" <yaniv...@gmail.com> wrote in message
> >>
> >> news:1175794888.467011.108490@w1g2000hsg.googlegroups.com...
> >>
> >> > Hi all,
> >> > I need to build a query that have in a field auto numbering and in  a
> >> > certain formating
> >> > for example:
> >> > 0001
> >> > 0002
> >> > ......
> >> > 0012
> >> > ......
> >> > is it posible?
> >
> > is that case a increment textbox will be easier
>
> My point exactly.

0
thread
4/6/2007 6:26:05 AM
I expect the link provided elsewhere in the thread will guide you in that 
direction.  I wondered about a report because of a specific technique for 
using the running sum property of an unbound text box.

"thread" <yaniv.dg@gmail.com> wrote in message 
news:1175840765.891549.240310@p77g2000hsh.googlegroups.com...
i'm exproting the query to excel,
this is why i need the autonumering

BruceM ???:
> "Franck" <the_darkblast@hotmail.com> wrote in message
> news:1175799682.718606.309150@w1g2000hsg.googlegroups.com...
> > On Apr 5, 2:42 pm, "BruceM" <bam...@yawhodawtcalm.not> wrote:
> >> Is this to produce data that will be viewed in a report, by any chance?
> >>
> >> "thread" <yaniv...@gmail.com> wrote in message
> >>
> >> news:1175794888.467011.108490@w1g2000hsg.googlegroups.com...
> >>
> >> > Hi all,
> >> > I need to build a query that have in a field auto numbering and in  a
> >> > certain formating
> >> > for example:
> >> > 0001
> >> > 0002
> >> > ......
> >> > 0012
> >> > ......
> >> > is it posible?
> >
> > is that case a increment textbox will be easier
>
> My point exactly.


0
BruceM
4/6/2007 11:29:42 AM
"thread" <yaniv.dg@gmail.com> wrote in message 
news:1175840765.891549.240310@p77g2000hsh.googlegroups.com...
i'm exproting the query to excel,
this is why i need the autonumering

You could build a basic tabular report and export that to Excel.  Reports are 
processed sequentially so things like counters and running sums are trivial to 
set up.  Queries are processed as a set so while doing that sort of thing is 
possible, it is more difficult and is very inefficient.  On larger sets of data 
the performance could be a real problem.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
Rick
4/6/2007 12:01:17 PM
so i understand it not possible to do t direcly in the query

Rick Brandt =D7=9B=D7=AA=D7=91:
> "thread" <yaniv.dg@gmail.com> wrote in message
> news:1175840765.891549.240310@p77g2000hsh.googlegroups.com...
> i'm exproting the query to excel,
> this is why i need the autonumering
>
> You could build a basic tabular report and export that to Excel.  Reports=
 are
> processed sequentially so things like counters and running sums are trivi=
al to
> set up.  Queries are processed as a set so while doing that sort of thing=
 is
> possible, it is more difficult and is very inefficient.  On larger sets o=
f data
> the performance could be a real problem.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt   at   Hunter   dot   com

0
thread
4/7/2007 9:02:58 AM
so i understand it not possible to do t direcly in the query

Rick Brandt =D7=9B=D7=AA=D7=91:
> "thread" <yaniv.dg@gmail.com> wrote in message
> news:1175840765.891549.240310@p77g2000hsh.googlegroups.com...
> i'm exproting the query to excel,
> this is why i need the autonumering
>
> You could build a basic tabular report and export that to Excel.  Reports=
 are
> processed sequentially so things like counters and running sums are trivi=
al to
> set up.  Queries are processed as a set so while doing that sort of thing=
 is
> possible, it is more difficult and is very inefficient.  On larger sets o=
f data
> the performance could be a real problem.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt   at   Hunter   dot   com

0
thread
4/7/2007 9:03:00 AM
"thread" <yaniv.dg@gmail.com> wrote in message
<1175936578.822384.319920@l77g2000hsb.googlegroups.com>:
> so i understand it not possible to do t direcly in the query
>
> Rick Brandt כתב:
>> "thread" <yaniv.dg@gmail.com> wrote in message
>> news:1175840765.891549.240310@p77g2000hsh.googlegroups.com...
>> i'm exproting the query to excel,
>> this is why i need the autonumering
>> 
>> You could build a basic tabular report and export that to Excel. 
>> Reports are processed sequentially so things like counters and
>> running sums are trivial to set up.  Queries are processed as a set
>> so while doing that sort of thing is possible, it is more difficult
>> and is very inefficient.  On larger sets of data the performance
>> could be a real problem.
>> 
>> --
>> Rick Brandt, Microsoft Access MVP
>> Email (as appropriate) to...
>> RBrandt   at   Hunter   dot   com

If you have something unique to sort on (SortField), you could do
something like this

SELECT t.field1, t.field1, ... ,
    Format(
       (SELECT Count(*)
       FROM yourTable s
       WHERE s.SortField <= t.SortField)
    ) As MyRank
FROM yourTable t
ORDER BY t.SortField

though it would probably be very inefficient

-- 
Roy-Vidar


0
RoyVidar
4/7/2007 9:17:24 AM
"RoyVidar" <roy_vidarNOSPAM@yahoo.no> wrote in message
<mn.3aa57d74a6c760e0.59509@yahoo.no>:

Forgott the formatting, sorry

SELECT t.field1, t.field1, ... ,
    Format(
        (SELECT Count(*)
        FROM yourTable s
        WHERE s.SortField <= t.SortField),
    "0000") As MyRank
FROM yourTable t
ORDER BY t.SortField

-- 
Roy-Vidar


0
RoyVidar
4/7/2007 9:23:44 AM
thread wrote:
> so i understand it not possible to do t direcly in the query

I don't believe my post indicated that.   Only that it might be very slow if 
dealing with a large data set.  It is "possible" by using a sub-query IF you 
have a unique field in your data that can be used for sorting.

See Roy-Vidar's post.


-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
Rick
4/7/2007 11:42:57 AM
can you give me some example for this?

Rick Brandt =D7=9B=D7=AA=D7=91:
> thread wrote:
> > so i understand it not possible to do t direcly in the query
>
> I don't believe my post indicated that.   Only that it might be very slow=
 if
> dealing with a large data set.  It is "possible" by using a sub-query IF =
you
> have a unique field in your data that can be used for sorting.
>
> See Roy-Vidar's post.
>
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt   at   Hunter   dot   com

0
thread
4/7/2007 1:45:10 PM
Reply:

Similar Artilces:

auto number index snafu
I have a 2003 DB I've been adding to for 2 years. It's at 423 records = now. I export to a PDF for simpler searching and viewing. Using the search the other day, I went to grab DVD 186, on carousel tower= 2. And what came up didn't match the record. The index was totally screwed. I went thru all 423 records and created a numeric index by hand. The auto number field is useless. But I would still prefer an auto number field. Trouble is when I create the auto number field, it's still off the actual numbers of the records.. ie: 99-100 is actually 99-186,187, 188, 189 100-385,...

Numbers and text
I have a link table from an Excel sheet. The first column is for account numbers but some are numbers only and I get the "#num!" error. The only way I can get rid of the error is to retype the number in Excel. I have tried formatting the cells in Excel as "text" but it doesn't work. Is there a work around? Cheers ...

Auto complete in Excel (Office) 2003
Is it possible to increase the *memory* of autocomplete?? I notice in the columns that excel will attempt to autocomplete if the text has been used in the last 100 or so cells (i think). What I would like is it to attempt to remember , say, 500 cells back. Is this possible?? Have I explained myself well enough? :-) Cheers Bob Check http://www.excelforum.com/showthread.php?s=&threadid=219730 fo possible solution -- Message posted from http://www.ExcelForum.com ...

Outlook 2003 Imap Auto Purge wish list
I hope someone at Microsoft can code in a auto purge Imap deleted email function into there next Service Pack. I can see a problem rolling this out to our employees. There going to delete messages and those messages are going to stay on the Email server until they do a Purge. I'm afraid they just won't do this. We use Imap. No auto purge means emails staying on the server which over time just takes up hard drive space and makes tape backups take longer. wayne Hi Wayne, You should send suggestions to mswish@microsoft.com as the newsgroups are not monitored for feature request...

Auto Forward mail to a group of External addresses
Can someone please tell me how I create an Auto Forward to a group of external email addresses. I want to setup an auto forward on my exchange so that all mail sent to forward@mydomain.com is then forwarded to a group of email accounts such as me@hotmail.com, me@yahoo.com, user@mail.com. Can someone please tell me how I set this up on an exchange server. I know how to do it for an individual but I can't work out how I create an auto forward for a group of people. Thanks for any advice! what version Exchange? only the terminology is different...create custom recipients/contacts ...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

Sequential ticket numbers and printing 4 to a page
Hi, i have mail merged my numbers from excel into my publisher ticket. Thanks that worked well, now i want to print 4 tickets to an A4 page and in print preview, i get 4 tickets all with the same number on one page. how do i get around this? Many thanks Print preview showing all the same is a bug. Print a test page. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Neyol" <Neyol@discussions.microsoft.com> wrote in message news:90034D4C-9951-4550-9DAC...

Summing in A Query
Hello, I have a database which fuel records are stored in. The data is stored in two tables. The first records the daily logs that operators use each time they fuel up. It stores their name, the key they used (keylock fuel system - it's ancient) the unit number of the equipment using the fuel, and the amount of fuel they took. The second table stores the month end information retrieved from the key lock print out. It keeps a running total of the amount of fuel taken with each key, and the operator using that key. We have problems making sure all of the fuel is accounted for each mon...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

Auto forward incoming mails.
Outlook 2003. How to configure such that all incoming mails to exchange will auto forward to my personnal email address? You can create a rule for that; Tools-> Rules and Alerts Note that automatic forwarding is disabled by (secure) default on the Exchange server. To enable see; http://www.howto-outlook.com/howto/automaticreply.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Emyeu" <cmchong20@yahoo.com> wrote in message news:uNQsdGAkEHA....

Ordering by number and text
I use a report to print out checklists that in the detail section have item number to delineate each checklist item. The item numbers as an example are 1-1, 1-2, 1-2a, 1-2b, or 2-1, 2-2, 2-3, 2-3a, 2-3b and so on. The report is grouped by checklist section and the grouping works perfectly but when the report is printed the item numbers are ordered in this order 1-1, 1-10, 1-11, 1-2, 1-3, 1-3a, 1-3b, 1-4, 1-5, 1-6, 1-7, 1-9. Obviously I want the the order to be in proper numerical sequence where 1-10 comes after 1-9. but even replacing the '-' with a decimal point doesn't ...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

RPC over HTTP/S on Exchange 2003
I have been configuring my single server with exchange to use RPC over https I have followed the instructions in MS guide and another simplified guide at http://www.petri.co.il/configure_rpc_over_https_on_a_single_server.htm Server spec is: Server 2003 standard SP1, Exchange 2003 SP1, XP client SP2 with outlook 2003 sp2 The bottom line is that when testing from the WAN, the outlook client will not connect and say that the exchange server is unavailable. I have a lot of experience configuring rpc over http/s with sbs2003 but this is the first time for server 2003 standard. I have outlook ...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

How do I change numbers to negative without re-typing?
I have a large range of data that needs to be changed to negative numbers, Can I do this in Excel? ...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Problem with vba code to export query result in excel
Hi, I have a access report that exports to excel with click of a button after choosing parameters. This works well. However I have to modify couple of fields to utilize formula in the export module. I am not sure how to do this. I am writing the above code which seems to cause problem. I appreciate any help to resolve this issue. Thanks. Code: If lngColumn = 12 Then xlc.Offset(0, lngColumn).Value = =([UnitPrice]*[OriginalShippedQty])/1000 End If It seems the fields UnitPrice and OrigianalShippedQty are not being recognized here Jack wrote: >Hi, >I have a acces...

Converting number to text
Is it possible to convert, say 1234 to one thousand two hundred and thirty four Thanks There is no direct functions to convert this. For a VBA solution check out the below links http://www.ozgrid.com/VBA/ValueToWords.htm http://support.microsoft.com/kb/213360 http://www.xldynamic.com/source/xld.xlFAQ0004.html -- Jacob (MVP - Excel) "booshi" wrote: > Is it possible to convert, say 1234 to one thousand two hundred and thirty > four > > > Thanks > > > . > ...

Sequentially number lines automatically
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How can automatically number the lines as I enter data. <br><br>so box A1 would automatically be 1 <br> and the next time I created an entry on another line that line would be 2, etc. <br><br>The reason is so that I can sort, delete and the numbers will remain, so I can also go back to the order than things were entered into. <br><br>Could do this manually, but a pain. <br><br>Or could possibly do this with a date and time? <br><br>Thanks Steevee Se...

Excel Number Format Codes
I can't figure out how to format numbers so that when you type 3220 it will look like 32.20 kinda like how you can enter numbers on an adding machine. I would greatly appreciate this number format code. Thank you. -- 1:~ Hi, I think you want to go Tools | Options | Edit | Fixed Decimals - 2. That will divide all the numbers you enter by 100. You will also have to format the row or column to display 2 decimals. Select the cells or range, right mouse click, format cells, numbers, 2 decimals. Hope that helps. Best regards, Kevin "MBB" <MBB@discussions.microsoft.com> w...

Difficult query
Hi, I have a table called WT,contains the fields "Type of call","DateW" and "ID", this table is used to by users to add rows that determine type of calls received in a call center,I want to create a query with the following criteria: 1- To view number of calls received in each type per day. 2- To show the field "Type of call" in this query,even the type that wa not used,and to view number 0 in the count field. 3-Prcentage of each type of call . On Dec 11, 3:52 pm, Pietro <Pie...@discussions.microsoft.com> wrote: > Hi, > I have a tabl...