Create Autonumber in QUERY not TABLE in Microsoft Access

see this link
http://support.microsoft.com/?kbid=199679

That's not quite what I'm after.  I just need a simple way for the query to return a sequence without writing to tables.  It should look like this in the QUERY, the NEW NO. is what I want the query to populate automatically for me.:

NEW NO.     CUS_NAM      CUS_ADDR  ....
     1            Smith, John     blahblahblah
     2            Jones, Jim      blahblahblah
     3            Doe, Jane       " "
     4            Kidd, Billy       " "
... so on and so forth.  Now if Jim Jones is removed from the table that the query references, then I want the query to read:

NEW NO.     CUS_NAM      CUS_ADDR  ....
     1            Smith, John     blahblahblah
     2            Doe, Jane       " "
     3            Kidd, Billy       " "

All it does is keep track of the number of entries in a COLUMN or Field without any regard to assigning a number to any of the records.  Then I can use it with DSUM to customize.

Thanks!

We seem to spend an incredible amount of energy trying to make Access 'behave' like a spreadsheet.  The two are worlds apart.  Access is primarily for storing and retrieving data.  Excel is good an analying it - both across and down the page.  Access likes to work only down the page.  Perhaps if you could give us an example of what you are trying to calculate with what - we might be able to come up with a better db solution. Just my 2¢

I want to create running totals and then create a formula to flag a figure once it goes negative, which means that the contract is used up.  I don't want to do it as an aggregate sum, because then it will repeat the sum instead of a running total.  I realize that Report, to a degree, handles running sums or totals.  However, I need to customize the heck out of it.  My example above showing the removal of Jim Jones is exactly what I need.  I know that DSUM will work by date, but that doesn't work, because I may have multiple entries in a day for the same customer.

This is a tough one; I've searched high and low for an answer and still have not found one that will do what I want it to do.  DSUM only works on sequential numbers or dates.  That is the key here.

Thanks for the response.  I do appreciate any help at all...and I don't want to put my database into Excel otherwise I am right back where we started...using Excel as a database...not good.

do you have a unique ID field ? like the OrderID in this query

SELECT A.OrderID, A.CustomerID, (Select Count(*) From Orders As B Where
B.OrderID<= A.OrderID) As NEWNO
FROM Orders A

I have a Ticket Number that is unique, but it is not truly sequential because the ticket number is actually a text field and may look like N00065, which is in Nebraska.  Yet the SAME customer the same day may have a ticket number like C00075, which is in Colorado on the same Contract.  Therefore, Ticket is not the best choice to use for sorting.  I am sorting Descending by delivery date and then by Ticket, because there may be multiple deliveries in one day.

DSUM is really picky about having a sequential number.  When I enter the data into the table raw and restart the numbering using a Number field (as opposed to Autonumber) on the next customer then I get it to work just fine.  That tells me that the problem resolution is some sort of command in the Query telling it to create sequential numbers.  If I figure that one little part out, then I will have a done deal where I can wrap it up and move on.

Again,  I appreciate the help!  :-)

By the way, when I try to use the Ticket number, which is a unique primary key, I get an #ERROR, because the Ticket number isn't always 100% in sequence as stated above.

Again thanks for the suggestions.  I know where you are headed with the OrderID.  I saw that same thing as one of Microsoft's solutions on their website, but my case is a little too quirky for that.

Ticket number does not necessarily be in sequence

capricorn,

Almost there.  It works okay on all of the data from the table, but it doesn't reset when criteria is implemented.  If I didn't have to filter the data, we would have it!  You definitely put me on the right track, though.

Thanks!

I did some customized tweaking, but that did the trick.  DSUM works beautifully, too.