Append Query for single record

[azgambit]

Good evening (OK, it's evening here, now)

I have a simple db that I created to make a reporting process easier. Within this db I have two tables, a main table and an archive table.

My thought process is that when a report is finished the user can click a command button and the record containing the report is moved from the main table to the archive table (and then deleted from the main table).

I thought to do this to keep the main table from becoming too large (and so forth).

Anyway, I though the best way to do this is with an append query; however, when I try this approach, the query moves the whole main table and appends it to the archive table.

Is there anyway to set the criteria for the append query so that it only appends the current record to the archive table?

I searched through the forums, and while I found several references to this, none seemed to fit what I was trying to do.

Thanks in advance!!!!!!!!!!!!!!!!

 

[pbaldy]

You should be able to do this by adding a WHERE clause to your append query that references the key field on the form/report, thus limiting the append to that one record.

Having said that, you probably don't need an archive table, unless the number of records will get into the hundreds of thousands. You could just have a field in one table that signified an "archived" record.

 

[azgambit]

Thanks for the reply, it is appreciated. I have two questions, the first concerns the size of the db. I had heard or read somewhere that it was best to keep the size of the tables small to avoid slowing down the db. I have one huge table with a couple of memo fields. If I am mistaken, I would be more than happy to keep this db to a single table, or simply have an archive table to backup data.

Second, how would you write the WHERE statement is the criteria?

Thanks again for the suggestion, believe me, I can use all the help I can get

 

[pbaldy]

Well, all things being equal, a smaller table will be quicker than a larger one. I'd be curious what you mean by "huge". While archiving off records to another table may make the first table faster to access, it creates other problems when you want to see the old/new data together again for a report or something. I'm not saying you shouldn't do it; just not to do it frivolously. I have 2 or 3 applications where I archive data like that, but it's only in situations where the number of records is at least in the hundreds of thousands, and the "main" table gets queried constantly, so speed is critical.

I assume you currently have an append query such as:

INSERT INTO TableName
SELECT WhatEver
FROM TheOtherTable

You’d simply add a WHERE clause that restricted the selected records to the one you want (this assumes that that record is in a form):

INSERT INTO TableName
SELECT WhatEver
FROM TheOtherTable
WHERE KeyField = Forms!FormName.ControlName

By the way, I drove through the northern part of AZ a week or so ago. Hit a brutal stretch of icy road on I40, from before Flagstaff all the way into NM. I thought AZ was nice and warm?!?