Auto Number Field in Query

**krjoshi** · 01-18-2012, 02:15 PM

Folks,

I have database with a table "All_Jobs" and a Query "Justified", what I am looking for when I run the Query I want to show a Field "SL" and should get Auto Number (Records Count). I have attached a file of what I'm trying to accomplish. Thanks in advance for your expertise!

Thanks in advance.

Joshi

**alansidman** · 01-18-2012, 03:29 PM

Changed Expressionn to surround each variable with square brackets and it seems to work.

SL: DCount("[Equipment Class]","[All_Jobs]","[Equipment Class] <='" & [Equipment Class] & "'")

See attached.

**krjoshi** · 01-19-2012, 11:16 AM

Hi alansidman,

Thanks for your reply, actually what I am looking for when I run the query the SL field should be showed Auto number, like 1, 2, 3 uniquely not the count of records. Can you please help me to resolve this issue?

Thanks in advance.

Joshi

**alansidman** · 01-19-2012, 12:41 PM

There may be some other ways, but the simplest way that I can think of is to either create a report from your query and put in row numbers as shown in this video
http://www.datapigtechnologies.com/f...minreport.html
or use the select query that you have and do a maketable query. In your new table you can then add an autonumber field with the table open in design mode.

**krjoshi** · 01-19-2012, 02:53 PM

HI alansidman,

Thanks for your advice, Can we make a Macro to create maketable of my "Justified" query??. Please help me?

Thanks,
Joshi

**alansidman** · 01-19-2012, 03:13 PM

You don't need a macro or VBA (my preferred method of automation). With your query open in design mode, and the ribbon tab open to design, select the make table icon.

Here is some VBA for you to consider if you want to do this with a command button.

http://bytes.com/topic/access/answer...-query-via-vba

**krjoshi** · 01-19-2012, 05:32 PM

HI alansidman,

Thanks for your advice. Finally I got a module to get Serial Number Field for my first query "Justified" , But I could not use the same method for my second query "NotJustified". I have attached the database. Could you please check and advise??

Thanks,
Joshi