A different autonumber question - single column table

[Tim Ward]

Table with just one column which is an autonumber column - how do I insert
records?

 

[Tim Ferguson]

Table with just one column which is an autonumber column - how do I
insert records?

Like any other insert command:

INSERT INTO MyTable (MyANcolumn)
VALUES (1024)

but why would you want to..?

B Wishes


Tim F

 

[TC]

Like any other insert command:

INSERT INTO MyTable (MyANcolumn)
VALUES (1024)

but why would you want to..?

I think he means manually, from the UI. He has opened the table datasheet.
How does he add new records to it (manually)?!

TC

 

[Tim Ferguson]

I think he means manually, from the UI. He has opened the table
datasheet. How does he add new records to it (manually)?!

Ummm.... why would he want to?

What about pressing <tab> whilst the focus is on the autonumber field of
the "new record" row...

Puzzled


Tim F

 

[TC]

Ummm.... why would he want to?

What about pressing <tab> whilst the focus is on the autonumber field of
the "new record" row...

Oops, yep, I guess that would do it.

As for why - I fear that we shall never know!

Cheers,
TC

 

[Tim Ward]

Like any other insert command:

INSERT INTO MyTable (MyANcolumn)
VALUES (1024)

Doesn't work, surely, doesn't that just put 1024 into the AutoNumber column
rather than generating an automatic value?

but why would you want to..?

Eh?? A table isn't much use if you can't insert records into it.

 

[Van T. Dinh]

The only purpose of an AutoNumber Field is to provide uniqueness to each
Record in the Table. Thus, the Table with only the AutoNumber Field does
not have any useful information content.

Why would you want to create the Table without any meaningful info. content?

--
HTH
Van T. Dinh
MVP (Access)

 

[Tim Ward]

The only purpose of an AutoNumber Field is to provide uniqueness to each
Record in the Table. Thus, the Table with only the AutoNumber Field does
not have any useful information content.

Why would you want to create the Table without any meaningful info.

content?

Because the only attribute of this object is a multivalued attribute, so the
actual information is in a join table which refers back to this main table
via a foreign key.

 

[Van T. Dinh]

So you have another Field in this Table which is a ForeignKey Field???

 

[Tim Ward]

So you have another Field in this Table which is a ForeignKey Field???

No, I have another table which refers back to the autonumber field with a
foreign key.

 

[Van T. Dinh]

So you have this 1-Field Table as the "One" (Parent) Table in the
One-to-Many relationship???

If you multiple Child Records related to each Parent Record in this Table,
surely there must be some common property / ies whereby you relate Child
Records to a particular Parent Record. The fact that you "group" Child
Records as related to different Parent Records indicates there are some
properties you use to group them. These properties should generally be
store in the One/Parent Record.

I don't know about your set-up, though. However, after some 20+ production
databases (JET & SQL Server back-ends), some with 100+ Tables, I have never
had to use a AutoNumber-Field-only Table.

 

[Van T. Dinh]

Actually, you can't, even with Tab key. You need to enter something (in
another Field) for the AutoNumber Field value to be assigned.

 

[TC]

content?

Because the only attribute of this object is a multivalued attribute, so the
actual information is in a join table which refers back to this main table
via a foreign key.

But surely you want to store some non-key attributes of the object
represented by the main table? I'm not arguing that you are incorrect. I
just can't think of a case where I would want to store child details of a
parent object, but I did >not< need any non-key attributes of that parent
object!

Cheers,
TC

 

[TC]

Ok, should have realized that!

TC

Actually, you can't, even with Tab key. You need to enter something (in
another Field) for the AutoNumber Field value to be assigned.

 

[Tim Ward]

But surely you want to store some non-key attributes of the object
represented by the main table? I'm not arguing that you are incorrect. I
just can't think of a case where I would want to store child details of a
parent object, but I did >not< need any non-key attributes of that parent
object!

No, there are no non-key attributes of the object represented by the main
table. The main table is a table of "lists of widgets", each row
representing one (possibly empty) "list of widgets". There is no information
associated with a "list of widgets" other than the id and the widgets.

(Yes, there is information about who owns which list of widgets, but
different lists of widgets can be owned by different types of objects in a
tree structure, and I've chosen the pattern which has "list of widget" id
fields in the owning objects' tables rather than the pattern which has "type
of owner" and "id of owner" fields in the "list of widget" table. Both
solultions are admittedly mucky, but I've got reasons for claiming the one
I've chosen is slightly less mucky in this particular application.)

 

[Douglas J. Steele]

No, there are no non-key attributes of the object represented by the main
table. The main table is a table of "lists of widgets", each row
representing one (possibly empty) "list of widgets". There is no information
associated with a "list of widgets" other than the id and the widgets.

(Yes, there is information about who owns which list of widgets, but
different lists of widgets can be owned by different types of objects in a
tree structure, and I've chosen the pattern which has "list of widget" id
fields in the owning objects' tables rather than the pattern which has "type
of owner" and "id of owner" fields in the "list of widget" table. Both
solultions are admittedly mucky, but I've got reasons for claiming the one
I've chosen is slightly less mucky in this particular application.)

If all else fails, try a CreatedDate field (setting its default to Date() or
Now()) That way, at least you have a non AutoNumber field to tab into.

 

[TC]

No, there are no non-key attributes of the object represented by the main
table. The main table is a table of "lists of widgets", each row
representing one (possibly empty) "list of widgets". There is no information
associated with a "list of widgets" other than the id and the widgets.

(Yes, there is information about who owns which list of widgets, but
different lists of widgets can be owned by different types of objects in a
tree structure, and I've chosen the pattern which has "list of widget" id
fields in the owning objects' tables rather than the pattern which has "type
of owner" and "id of owner" fields in the "list of widget" table. Both
solultions are admittedly mucky, but I've got reasons for claiming the one
I've chosen is slightly less mucky in this particular application.)

Ok, understood. Well described!

TC

 

[Dave Hall]

Edit -> Append works a treat for me!

 

[Tim Ferguson]

=?Utf-8?B?RGF2aWQgQ29ub3Jvenpv?= <David
(e-mail address removed)> wrote in (e-mail address removed):

If you do an insert and specify a number, it actually uses that number:
INSERT INTO

(testcol) values (0)
Puts an entry with 0 as the "Autonumber" field.

INSERT INTO MyTable (TestCol)
SELECT 1 + MAX(TestCol)
FROM MyTable


.... but it's still a pointless thing to do... <g>

PS: I haven't tested it, but it might fail on an empty table.. it is left
to the reader to find out how Jet treats NULL+1 as a numeric value.

HTH

Tim F

 

[Lynn Trapp]

PS: I haven't tested it, but it might fail on an empty table.. it is left

to the reader to find out how Jet treats NULL+1 as a numeric value.

Any arithmetic operation with Null will always return Null