Incemental nimber in Make Table Query?

[Ladybird]

I want to create an Incremental Number field for this Make-Table Query which
is based on the records from another table. So each line of records in the
new Table has an incremented number starting from 1.
Do I use the ALTER TABLE statement? and how...
SQL:
SELECT Tbl_SBUY_TEMP.TITLE, Tbl_SBUY_TEMP.FORMAT, Tbl_SBUY_TEMP.BCPRICE AS
PRICE, Tbl_SBUY_TEMP.SHELFDATE INTO SD_ITEM_IN_TEMP
FROM Tbl_SBUY_TEMP
ORDER BY Tbl_SBUY_TEMP.TITLE;

 

[John Vinson]

I want to create an Incremental Number field for this Make-Table Query

This was impossible last week when you asked this question two or
three times.

It's still impossible.

YOU CANNOT CREATE AN AUTONUMBER IN A MAKE-TABLE QUERY. Full stop, end
of message.

Asking the same question over and over again will not change its
answer!

John W. Vinson[MVP]

 

[Ladybird]

This is unacceptable answer!
You must learn to think creatively John Vinson. You are not comprehending
this question, YOU HAVING BEEN RAISED ON CHOOK-FOOD PROBABLY ACCOUNTS FOR
THIS!
Autonumber & incremental number are two different things!
Accepted microsoft Access have not implemented Autonumber in make table
queries yet...As I have explained
I WANT AN INCREMENTAL NUMBER AS IN EXPRESSION COUNT, my question/s relate to
this and how to implement it.

 

[Douglas J. Steele]

I believe you need to be a little more polite, since John is one of the more
respected contributors here.

If all you want is a field that will have an incrementing number for all of
the existing rows, but will not provide an incrementing number for any new
rows you might add to the table, that can be done in a query (depending on
the details), and you can then use that query as a make-table query.

For example, if you've got a date field that you're sorting by, you could do
something like:

SELECT DCount("*", "[MyTable]", "[MyDateField] <= " & Format([MyDateField],
"\#mm\/dd\/yyyy\#") AS MyIncrementingField,
Field1, Field2, Field3
FROM MyTable
ORDER BY MyDateField

However, the idea of creating a "snapshot" table that will not be added to
is an extremely unusual one.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[John Vinson]

This is unacceptable answer!
You must learn to think creatively John Vinson. You are not comprehending
this question, YOU HAVING BEEN RAISED ON CHOOK-FOOD PROBABLY ACCOUNTS FOR
THIS!
Autonumber & incremental number are two different things!
Accepted microsoft Access have not implemented Autonumber in make table
queries yet...As I have explained
I WANT AN INCREMENTAL NUMBER AS IN EXPRESSION COUNT, my question/s relate to
this and how to implement it.

I apologize for the tone of my answer, Ladybird. I was getting
frustrated because I have seen the same question (with variant
phrasing) from you over and over, with no response to my or others'
answers to the question.

As noted in another thread, if the purpose of this table is to count
records, a Totals Query counting records would be a much simpler, less
bloating, and faster approach.

To get an "incremental number" in a Query, you will need a field
within the query which defines a sort order. You can put a calculated
field in the query

Incr: DCount("*", "[YourQueryName]", "[Sortfield] <= " & [Sortfield])

where Sortfield is the unique, sorted field.

If you want an incremental number on a Report, it is simpler to simply
put a textbox on the report with its Control Source set to 1, and its
Running Sum property set to Over All.

John W. Vinson[MVP]

 

[Ladybird]

Ok, accepted and please mine for the sharp response...it does all get a bit
frustrating at times.
The reason for dual posts are the result of my news server (ISP) not
downloading new posts when I log on and sometimes not posting my new
messages at all. When it does download/post it can be several days later
after my first attempt to post. So when I go to check to see if message
posted 'is not'... well...hence the double posts. Next time I will send the
'same' message until it posts instead of rewriting it again & again.

I will implement your suggestion:
Incr: DCount("*", "[YourQueryName]", "[Sortfield] <= " & [Sortfield])
It seams to be the right approach for this situation.
Thanks again.

 

[Guest]

Is there a way to implement an incrementing value in a query using a VBA
function?

This is unacceptable answer!
You must learn to think creatively John Vinson. You are not comprehending
this question, YOU HAVING BEEN RAISED ON CHOOK-FOOD PROBABLY ACCOUNTS FOR
THIS!
Autonumber & incremental number are two different things!
Accepted microsoft Access have not implemented Autonumber in make table
queries yet...As I have explained
I WANT AN INCREMENTAL NUMBER AS IN EXPRESSION COUNT, my question/s relate to
this and how to implement it.

I apologize for the tone of my answer, Ladybird. I was getting
frustrated because I have seen the same question (with variant
phrasing) from you over and over, with no response to my or others'
answers to the question.

As noted in another thread, if the purpose of this table is to count
records, a Totals Query counting records would be a much simpler, less
bloating, and faster approach.

To get an "incremental number" in a Query, you will need a field
within the query which defines a sort order. You can put a calculated
field in the query

Incr: DCount("*", "[YourQueryName]", "[Sortfield] <= " & [Sortfield])

where Sortfield is the unique, sorted field.

If you want an incremental number on a Report, it is simpler to simply
put a textbox on the report with its Control Source set to 1, and its
Running Sum property set to Over All.

John W. Vinson[MVP]