First append query

[Jack Sheet]

Hi all - Access 97

Rather late in the day I have discovered (not surprisingly) a flaw in the
table design of my first database (probably not the only one!)

There is a table T_Tasks that tracks the progress of tax returns and
preparation of accounts, with autonumber primary key ID_Tasks.

Given that I would have to complete a tax return for each set of accounts I
initially and erroneously figured that it would be sensible to use just the
one record to track the progress of both.
That assumption is flawed because more than one set of accounts might be
relevant to one tax return, one set of accounts might be relevant to more
than one tax return, and indeed some tax returns will not require any
accounts (although that is only a minor objection).
So I have decided to use a field "Task" to distinguish the tasks between
accounts and tax returns (and indeed certain other tasks), and use query
tables to match up accounts relevant to particular tax returns and monitor
progress.
The first step in this exercise will be to duplicate a large number of
records currently residing in T_Tasks, append those records to T_Tasks, and
then assign the Task_Type field of the new records to be "Accounts", leaving
the original records dedicated solely to "Returns". There is already a
Yes/No field "Accounts required?" within T_Tasks, and I tried to set up an
append query that would create additional records in T_Tasks, as follows:

INSERT INTO T_Tasks ( [Accs Req'd] )
SELECT T_Tasks.[Accs Req'd]
FROM T_Tasks
WHERE (((T_Tasks.[Accs Req'd])=Yes));

This bombed out with 0 records out of 808 appended, and error message that
there were 808 validation rule violations. I clicked on Help at this point
but I didn't understand the outcome, so it is back to the well of knowledge,
I'm afraid. It did not help if I selected all of the fields in design view
and left the criteria unchanged - same error.

What next? ideas?

 

[Rick Brandt]

Hi all - Access 97

Rather late in the day I have discovered (not surprisingly) a flaw in
the table design of my first database (probably not the only one!)

There is a table T_Tasks that tracks the progress of tax returns and
preparation of accounts, with autonumber primary key ID_Tasks.

Given that I would have to complete a tax return for each set of
accounts I initially and erroneously figured that it would be
sensible to use just the one record to track the progress of both.
That assumption is flawed because more than one set of accounts might
be relevant to one tax return, one set of accounts might be relevant
to more than one tax return, and indeed some tax returns will not
require any accounts (although that is only a minor objection).
So I have decided to use a field "Task" to distinguish the tasks
between accounts and tax returns (and indeed certain other tasks),
and use query tables to match up accounts relevant to particular tax
returns and monitor progress.
The first step in this exercise will be to duplicate a large number of
records currently residing in T_Tasks, append those records to
T_Tasks, and then assign the Task_Type field of the new records to be
"Accounts", leaving the original records dedicated solely to
"Returns". There is already a Yes/No field "Accounts required?"
within T_Tasks, and I tried to set up an append query that would
create additional records in T_Tasks, as follows:
INSERT INTO T_Tasks ( [Accs Req'd] )
SELECT T_Tasks.[Accs Req'd]
FROM T_Tasks
WHERE (((T_Tasks.[Accs Req'd])=Yes));

This bombed out with 0 records out of 808 appended, and error message
that there were 808 validation rule violations. I clicked on Help at
this point but I didn't understand the outcome, so it is back to the
well of knowledge, I'm afraid. It did not help if I selected all of
the fields in design view and left the criteria unchanged - same
error.
What next? ideas?

Did you change your Primary Key on the table? If not, then every record you
copy will be violating the Primary Key.

 

[Jack Sheet]

Further to my earlier post I have a suspicion that the problem might be
caused by an attempt to duplicate the value of the primary key field when
the entire record is being duplicated, when really I just want it to create
a new unique autonumber in that field for each new record. If that is
indeed the cause I still do not know how to solve it, so question stands,
I'm afraid

 

[Jack Sheet]

Jack Sheet wrote:
... SNIP ...
Did you change your Primary Key on the table? If not, then every record
you copy will be violating the Primary Key.

Yup, thanks - I had just about worked this out and responded to myself here
at the newsgroup just as you were also posting.

In answer to your question, no I left the primary key unchanged. But I
still do not know how to achieve the desired result. Whether I remove the
primary key or not the field ID_Tasks will replicate for each record, which
is not what I want. I want all criteria-matching records to be duplicated
with the exception that the ID_Tasks field must increment in an autonumber
fashion in each new record created.

 

[Rick Brandt]

Further to my earlier post I have a suspicion that the problem might
be caused by an attempt to duplicate the value of the primary key
field when the entire record is being duplicated, when really I just
want it to create a new unique autonumber in that field for each new
record. If that is indeed the cause I still do not know how to solve
it, so question stands, I'm afraid

Include all fields in the insert EXCEPT for the Primary Key.

 

[Jack Sheet]

Include all fields in the insert EXCEPT for the Primary Key.

Thanks, that worked.

 

[Jack Sheet]

Include all fields in the insert EXCEPT for the Primary Key.

Thanks, that worked, but there was one peculiar side effect:

Prior to running the update the table has 1608 records, with the highest
autonumber in the primary key being 1625

After running the update query I end with 2416 records, with a gap in the
autonumber series from 1626 to 2435 inclusive, with the next record after
primary key value after 1625 being 2436.

I am not overly bothered about this, as the autonumber key has no real
meaning in subsequent operation of the database, but I did find it
surprising.

 

[John Vinson]

After running the update query I end with 2416 records, with a gap in the
autonumber series from 1626 to 2435 inclusive, with the next record after
primary key value after 1625 being 2436.

I am not overly bothered about this, as the autonumber key has no real
meaning in subsequent operation of the database, but I did find it
surprising.

Autonumbers ALWAYS have gaps. Manual entry will give you a gap if you
hit <Esc> after the first keystroke; Append queries will preassign a
range of autonumbers which will be "used up" even if you cancel the
append query, or it fails due to an error; I've even seen gaps just
from running an append query even when it works correctly.

Bottom line: use Autonumbers as hidden, meaningless unique ID's and
for table linking; don't expose them to people. Especially don't
expose them to IRS auditors, they get really curious when they see
TaxReturns numbered 1623, 1624, 1625, 2436 and 2437...


John W. Vinson[MVP]