Append One Record with a button?

[Yecenia]

Hello!

I have been tasked to create an assignment log for our employees. Each task
needs to be kept for historical purposes. When a task reaches a "completed"
status, the record which contains details about the assignment must be saved
in a seperate table.

I have a current assignment log Table, which I have copied and pasted. I
created a select query from the original assignment log and set the query as
an append query. Access 2007 allows me to connect to the copied/history
table, but doesn't finish the wizard. I get an error that the fields don't
match. I JUST copied and pasted the structure....how is it different.

Am I going down the correct path? how to you append only one record and not
an entire table?

Thanks,
Yecenia

 

[KARL DEWEY]

Am I going down the correct path?
In my opinion yes. There should not be a separate table for history. If
you need to run a report that encompasses current projects and the past three
years, how will you do it?
I recommend one table with one additional Yes/No field name something like
Archived or History. If the record is marked its value will be -1 (minus
one) versus a 0 (zero) for current records. Use these values in the
queries to segregate current from history.

 

[Yecenia]

Thank you for the prompt reply Karl!

That is a great idea! Great minds think alike, unfortunately my requirements
don't allow for this approach.

The reason for the history table is due to the size of the data. My boss
only wants to keep the current assignments in one table and all historical in
another. The historical information will grow rather quickly. Optimize
performance is his goal.

If you can explain how I can append one record at a time, that would be great!

 

[KARL DEWEY]

I get an error that the fields don't match.
Open the query in design view and look at the lower part of the grid on the
left. Review the Append To row for any blanks. based on the field name at
the top of the column select from the pull down of the blank to correct
matching field name.

Then switch to SQL View and edit the SQL from this --
INSERT INTO YourTableName ( Field1, Field2)
SELECT FieldA, FieldB, ...

to this ---
INSERT INTO YourTableName ( Field1, Field2)
SELECT TOP ! FieldA, FieldB, ...

This will append only on record. If it is the wrong record then you need to
add criteria to pull the correct data.

 

[KARL DEWEY]

I get an error that the fields don't match.
Open the query in design view and look at the lower part of the grid on the
left. Review the Append To row for any blanks. based on the field name at
the top of the column select from the pull down of the blank to correct
matching field name.

Then switch to SQL View and edit the SQL from this --
INSERT INTO YourTableName ( Field1, Field2)
SELECT FieldA, FieldB, ...

to this ---
INSERT INTO YourTableName ( Field1, Field2)
SELECT TOP ! FieldA, FieldB, ...

This will append only on record. If it is the wrong record then you need to
add criteria to pull the correct data.