Strange Error Message in Append Query?? 3

[quest4]

I have an append query where I am trying to take all of the attendance data which is two years old or loder, to another dbase forarchiving. This is my query:
INSERT INTO tblAttendance(EmployeeNumber, DateWeekStarting)
IN '\\Gamma\Users\Shared Accounting Files\SPIAttendanceArchive.mdb'
SELECT Att.EmployeeNumber, Att.DateWeekStarting, Att.*
FROM tblAttendance AS Att
WHERE (((Att.DateWeekStarting)>=DateAdd("y",-2,Date())))
ORDER BY Att.EmployeeNumber AND Att.DateWeekStarting;
When I run it I get this:
"Duplicate output destination'EmployeeNumber'"
This message makes no sense to me any ideas why I am getting it? How do I corect this problem? Thanks to anyone who answers this.

 

[THoey]

Maq's a giirrll??? Ack!! Spit!! Delete her suggested code (even though it works)!! BTW wanna date?

Just kidding and sorry for posting a "nothing useful" post to the thread, but just had to make a comment. As you all have mentioned, gender, age, race, etc makes no difference in this forum. People helping people... Terry M. Hoey

 

[Maquis]

LOL, thanks for the best laugh I've had today, Terry. I really do like this forum just for the reasons you mentioned. It's nice and professional with plenty of useful code tips.

I joined another programmer's forum (which shall remain nameless) but I didn't like it as well. The guys there have been posting there for years and they don't welcome advice from anyone who's not already in the "in group". (You can ask questions, but how dare you try and offer advice!) Also about a third of the postings are "programmer wanted", "programmer needs job" or "make $$$ fast with your home computer" listings.

You have no idea how refreshing it is to find a forum that is above all that.

Of course, if I ever do need a job, I'll post on the other forum or at least try to figure out how to make $$$ fast on my broken down decrepit 64k computer at home. Maq B-)
<insert witty signature here>

 

[quest4]

Jeff, this dbases aren't yet ready for testing yet. After this thing with archiving gets runing, then it is going to the test people. Now in the archive dbase when I use the link tables I get a copy of tblAttendance and it has all of the tblAttendance data in it. If I delete one record in the tblArchive in the archive dbase, it deletes in both dbase tables, which is the way a link should workd, so I don;t understand why people tell me to use linked tables. Now if I import tblAttendannce, structure only and name it tblArchive, I try and runmy query from the original, it looks like it is working, but notthing ever arrives in the archive dbase. I want to use the function to run the query thru autoexec , but I need to get this thinng working correctly first of all. I do have at least two records in tb,Attendance which are two years old or older and so they should be being sent but they are not. Thanks allot for the help.

 

[THoey]

Yes, you are right, if you are in your archive MDB and delete a record from your linked (production) table, it is going to delete it on both sides. In effect, you are talking about the SAME table. Just two different ways to get to it.

What I thought you wanted was to take the records from the production table, copy them to the archive table and then delete them from the production table. If that is true, I think it's time for you to post your latest SQL statement here again.

What I think you need (very limited query) is:

INSERT INTO MyArchiveTable
SELECT * FROM MyProdTable
WHERE MyProdTable.MyDateField < SomeCriteria;

DELETE FROM MyProdTable
WHERE MyProdTable.MyDateField < SomeCriteria;

Each of these queries would be loaded into a string and the passed to the DoCMD.RunSQL.

Make sense? Terry M. Hoey

 

[quest4]

Thanks for the response, Terry. That is almost exactly what I have been asking. I did modify the append query to what you recommended and still no data arrive in the tblArchive table in the archive dbase, which is now setting right next to the production dbase. Here is what I did:
INSERT INTO tblArchive
IN 'C:\Documents and Settings\gdgens\My Documents\SPIAttendanceArchive.mdb'
SELECT Att.*
FROM tblAttendance AS Att
WHERE (((Att.DateWeekStarting)>=DateAdd(&quot;y&quot;,-2,Date())));
there are two or three records at least that should have been inserted in the tblArchive table in the archive dbase. Thanks for the help.

 

[jebry]

Hi!

Terry has it absolutely correct! One thing I want to make sure you understand. What we have been talking about is for you to both import the tblAttendance structure and rename it tblArchive *and* link back to tblAttendance. After you do that, Terry's append and delete queries will work as promised, though you will have to adjust the criteria to your own needs.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Maquis]

Ok, I got halfway through typing what Jeff just said. (He just types faster).

Either link the tables or use this query to reference an &quot;outside&quot; database. One or the other. Personally I prefer linking. Maq B-)
<insert witty signature here>

 

[jebry]

Hi!

Actually I do type fast. But half of my keystrokes are Backspace!
Jeff Bridgham
bridgham@purdue.edu

 

[quest4]

Maq, thanks for the response. Linking won't give me anything but a updatable copy of the product dbase table, regardless of what its' name is. I did ref and outside dbase in the query with the IN clause and it is now sittingright next to the production dbase, and still nothing is going over to the archive dbase. I am going to take the better course of valare, give up. I am going to try and go back to what worked in the good old days in UNIX, a delinited file. I have a thread out on it to see how it is done it the world of Excess. Thanks again for the help and response.