Create Autonumber in QUERY not TABLE in Microsoft Access

**Anilrgowda** · 21-Oct-2007, 11:44 PM

I am hating the lack of functionality with DSUM in queries right now. I have several reports I need to run based off of information in several tables which are related. I also need to be able to show the information in forms. Therefore, I created a QUERY that properly filters the information that I need.

However, I need to run some subtotals using DSUM, which I know how to do. Yet, I would like to create some sort of 'autonumber' in the QUERY not TABLE in Microsoft Access so that I can better customize my DSUM formula. It is quite easy to do this in Excel, but I haven't figured out why it is harder than hell trying to subtotal crap and create customized autonumbers in Access.

**Anilrgowda** · 21-Oct-2007, 11:44 PM

SELECT A.[Ticket Number], A.CUS_NAM, (Select Count(*) From NAmeOFTable As B Where
B.[Ticket Number]<= A.[Ticket Number]) As NEWNO
FROM NameOFTable A

21-Oct-2007, 11:44 PM	#1 (permalink)
Anilrgowda Administrator Posts: 18,703 Join Date: Jan 2006 Rep Power: 10 IM:	Create Autonumber in QUERY not TABLE in Microsoft Access I am hating the lack of functionality with DSUM in queries right now. I have several reports I need to run based off of information in several tables which are related. I also need to be able to show the information in forms. Therefore, I created a QUERY that properly filters the information that I need. However, I need to run some subtotals using DSUM, which I know how to do. Yet, I would like to create some sort of 'autonumber' in the QUERY not TABLE in Microsoft Access so that I can better customize my DSUM formula. It is quite easy to do this in Excel, but I haven't figured out why it is harder than hell trying to subtotal crap and create customized autonumbers in Access. ------------------

21-Oct-2007, 11:44 PM	#2 (permalink)
Anilrgowda Administrator Posts: 18,703 Join Date: Jan 2006 Rep Power: 10 IM:	Re: Create Autonumber in QUERY not TABLE in Microsoft Access SELECT A.[Ticket Number], A.CUS_NAM, (Select Count(*) From NAmeOFTable As B Where B.[Ticket Number]<= A.[Ticket Number]) As NEWNO FROM NameOFTable A ------------------

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)