alarming(?) discovery - table violates first normal form

[AccessMan]

I created a 'Make table' query for the first time and used an existing select
query as the source of data. I executed this query and inspected the new
table in design view. This table had no key field. (I anticipated that
Access might insert an autonum key field.) The data had no duplicate rows.
I copied a row and tried to paste in a duplicate, and this was allowed. Does
anybody else find this alarming? I have a saved table object that violates a
basic requirement for a database table.

 

[Allen Browne]

Not sure it's alarming. The fact that Access permits you to do something you
should not do could be seen as a benefit.

Sometimes it is really convenient to connect up bad data and work with it in
Access. Hopefully you will be sensible enough to create a normalized
struture in the end, but - particularly when importing - it's quite handy to
have the ability to manipulate bad data.

Similarly, I think I would be disappointed if Access did start adding fields
that were not part of the SQL statement. I tend to get annoyed with software
that does what it thinks I should have done, rather than what I asked it to
do. IMHO, Microsoft software does make that mistake at times.

In general, I find Make Table queries are of very limited use. It is much
better to create the table as you want it, with the desired fields,
properties set (e.g. Required for foreign keys), indexes (e.g. primary key),
and so on, and then populate it with an Append query.

 

[AccessMan]

I agree with your points. I certainly do get annoyed when Microsoft acts
like it knows best. I'm just concerned that Access may run into trouble if
it tries to handle this pseudo-table like a real table.

I am one who plans my tables and keys very carefully, so I agree that Make
Table should be used in limited situations. My situation certainly called
for it, and I was glad this functionality was there. I just hope I don't
start experiencing issues.

Thanks!

 

[Fred]

Same as the other guys said except more emphatically.

Even though I really shouldn't eat 2 donuts, it would not find it alarming
if the donut shop sold me two, and, in fact, would find it alarming if they
refused to sell me two.

Software should do what you tell it to without trying to elevate itself to
the status of Big Brother or a Nanny.

 

[Jeff Boyce]

In the event that you wish to ensure that your "new" table has a primary key
(autonumber or otherwise), you could easily accomplish this by first
creating the table structure you desire, then using an Append query (rather
than a Make Table query) to "load" the table.

If you need to periodically empty it and reload, also create a Delete query
to clear it out.

This approach gives you considerably more control over what gets put into
the table, and how.

Regards

Jeff Boyce
Microsoft Office/Access MVP