Archiving Data

[crmayer]

I am trying to make a computer inventory in Access. I have a table and form created, "Monitors" and one called "OldMonitors". I have about 10 fields for each monitor and a check box called scraped. When somebody checks this box, I would like them to click a button that then takes this record from the "Monitors" table and moves it to the "OldMonitors" table. I have created an append query, but everytime I run it, it says that there are "0" records to apend. I have set the scrapped field if = to True. I then created a simple select query with the same "if = true" and it selects this one record. Is there something with an apend query that I might be doing wrong? Or possibly a better way to move records to an history table?

 

[sillysod]

in the criteria for the scrapped field on query designer put

= "yes"

or maybe its = true cant remember off the top, either way theres no need for the if bit

 

[luceze]

or you could use:

=-1


Eric

 

[missinglinq]

I've done this same thing with a patient db, to differentiate between active patients and inactive patients. I just used a single table and 2 forms(with their own queries). The Monitors query/form, in your case, would select and display records that do not have "Scraped" checked. The OldMonitors query/form would do the same for records that have "Scraped" checked.

Your button on your Monitors form could then requery the Monitors query and the record would no longer appear on that form.

You could also do the reverse (if monitors are ever brought out of "retirement" and used again.)

Hope this helps.

The Missinglinq

"It's got to be the going,
not the getting there that's good!"
-Harry Chapin

 

[crmayer]

Sorry,
I do not have the "if" in there. I just have "= Ture" in the criteria field. I also tried "-1", "1", "0", and "False". None of them select any records.

 

[luceze]

Can you copy and paste your SQL here?

Eric

 

[crmayer]

Here it is:

INSERT INTO OldMonitors ( Brand, Model, Serial_no, Model_no, [Size], Inventory, Asset_num, Computer_num, Notes, Scraped )
SELECT Monitors.Brand, Monitors.Model, Monitors.Serial_no, Monitors.Model_no, Monitors.Size, Monitors.Inventory, Monitors.Asset_num, Monitors.Computer_num, Monitors.Notes, Monitors.Scraped
FROM Monitors INNER JOIN OldMonitors ON Monitors.Serial_no = OldMonitors.Serial_no
WHERE (((Monitors.Scraped)=True));

 

[luceze]

No need for the join. Try this:

INSERT INTO oldmonitors ( Brand, Model, serial_no, model_no, [size], inventory, asset_num, computer_num, notes, scraped )
SELECT Monitors.Brand, Monitors.Model, Monitors.serial_no, Monitors.model_no, Monitors.size, Monitors.inventory, Monitors.asset_num, Monitors.computer_num, Monitors.notes, Monitors.scraped
FROM Monitors
WHERE (((Monitors.scraped)=True));


HTH,
Eric

 

[crmayer]

That did it.
Would you know what I did get get that JOIN added in there? If not, no big deal, I can create the query and go in and delete the JOIN.

But that did work, thanks alot Eric...

 

[luceze]

If in your global relationships you have a relationship assigned between the two tables, and you add those two tables to your query (either through the wizard or in design view), Access will automatically add the predefined relationship. In this case you only needed the one table. If you only select one table in your query then the relationship will not appear.

Hope that makes sense.

Eric