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.

 

[jebry]

Hi!

What the message indicates is that Employee Number in tblAttendance has its duplicates allowed property set to no and it already has employee numbers which match some of the ones you are sending.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Maquis]

Actually that isn't what it means. (sorry, Jeff)

It means you've selected att.employeenumber twice in your query. You only need it once.


SELECT Att.EmployeeNumber, Att.DateWeekStarting, Att.* Maq B-)
<insert witty signature here>

 

[Maquis]

P.S. Looks like you'll have the same problem with DateWeekStarting after you clear EmployeeNumber Maq B-)
<insert witty signature here>

 

[jebry]

Hi!

Sorry, I misread the error message!

Good read Maq!
Jeff Bridgham
bridgham@purdue.edu

 

[Maquis]

I've seen it enough times in my own database, I know it by heart now. Maq B-)
<insert witty signature here>

 

[quest4]

Thanks Marquis, I listed out all of the colms and that error stop and it appears to run. When I went to check out the table in the second dbase, I got another error over there on the autoexec, which don't figure, because I just imported it from this dbase along with the function and it works perfectly in this dbase. Do the errors ever stop? I know tom., which never comes. Well Thanks alot for the help.

 

[quest4]

Marquis, thanks for the got the other dbase working, missing the DAO libery. So I tried it again and nothing is transfering. I do have two weeks of data that should have tranndfered because they have DateWeekStrat of 1998 and 1999. Can you give me any ideas on this one? I check the full path and it is OK, except that to be technical, it is E:\Users instead of \Users but I can't see that mattering. Thanks again for the help, this dbase is almost ready for testing once this is straightened out.

 

[Maquis]

Quest, you're over my head now. I've never tried to transfer data from one database to another. Here are a few ideas which may or may not help.

1. Instead of the convoluted query opening and transferring out of the current database, could you just link in the tables from the other database.

2. For debugging purposes, try to transfer the data to a local table and see if it works. It should help narrow down the bug in the query. Maq B-)
<insert witty signature here>

 

[quest4]

Maquis, thanks for the response. I am only trying to archive records, from tblAttendance, to a second dbase, with a table called tblAttendance, too. I did try, briefly the linked tables, but the tblAttendance in the archive dbase was exactly the same as the data in the original tblAttendance, which if I understand links in MS that would make sense. Am I doing something wrong in linking tables? I wouldn't mind use link if I didn't get all of the data that is in the original table. I just want to archive wwhat is two years old or older. So, for everybody tells me to do it this way, but it is not working. Any ideas? Suggestions? Thanks again for all of the help. Don't you just hate it when you are a step from finishing and the last leg won't link up, so to speak.

 

[Maquis]

If it were me, I would link the tables. Even though the table is called tblAttendance in it's own database doesn't mean it has to be called that in the linking database.

Link the table and it should link in with the name tblAttendance1 (or something like that). You can then rename it to tblAttendance_Archive or something else that makes sense to you. You can then freely update it just like a local table.

You control what data is transferred into the archive table. If you only want to copy data older than 2 years then put that criteria in the query. Maq B-)
<insert witty signature here>

 

[jebry]

Hi!

I think that what Maq is suggesting here would be to link from you archive db to the 'live data' db. You will need to change the table name in the archive db, but I don't see where that would be a problem. Now you can set up your query in the archive db to append the data from the live db to the archive db. If you want, you code then create a macro to run the query and open the archive db from the live db and run the macro.

At least, I think that is what he meant! X-)

hth Jeff Bridgham
bridgham@purdue.edu

 

[Maquis]

Uh yeah, something like that.

That's how I feel about now, too. X-) It's been one of those days.

BTW, (she, not he, yeah I know you can't tell with these screennames.) Maq B-)
<insert witty signature here>

 

[jebry]

Hi Maq!

Sorry! I try to keep my comments gender neutral just for that reason. I blew it this time!

Sorry Again!
Jeff Bridgham
bridgham@purdue.edu

 

[quest4]

Jeff, basically you are saying that I got the wrong name on the table and I am run the append query in the wrong place. Correct? I am using the autoexec to run a procedure that will does everything on maned at the being of each year. I now got to figure out get the archive autoexec to run before this one gets opened. Or better yet, is there a way to run a query in another dbase from this public function thru DoCmd.OpenQuery? Thanks again and please let me know, thanks alot.

 

[quest4]

He, she why should it even matter? We are all stuck on the rock together, we all better learn to live together reguardless of anything. Enough said. Thanks both of you people.

 

[Maquis]

Jeff, no need to apologize. My screenname is gender neutral on purpose. Half the guys online won't take tech advice from a &quot;giirrrrrl&quot; and the other half try to pick you up.

Quest, it sounds like all you need is an autoexec query in whichever database you decide should have the linked table. Have the macro check the last time the archive was updated and if it's due to be updated again then run your &quot;update archive routine.&quot;

Maq B-)
<insert witty signature here>

 

[quest4]

Maq, Thanks for the response, but I have a public function running of the autoexec all ready. It check to see to the year has change and when it does it runs three queries in this dbase, well two now. I use it in the other database to clear out data records that are older than seven years old, just one delete query there. But one other thing is still wrong, I created a linked table to the tblAttendance in the original datrabase and then I renamed it to tblArchive, in the archive database, and I still see all of the data in the original tblAttendance table. Any ideas? Suggestions? Thanks agaoin, one and all.

 

[Maquis]

I'm not too sure what you mean. Renaming a table doesn't delete any data in the table. I only suggested renaming it because you had 2 tables with the same name in different databases.

Are you saying that your archive table contains more records than it should? If so just delete the extra records. Of course if this is a live database and not your own personal testing model then make a backup!!!! Maq B-)
<insert witty signature here>

 

[jebry]

Hi All!

guest4: Just add a RunSQL command to the autoexec macro in the archive db. Then use your Append query as the SQL to run. Then all archivable records will be transferred to the archive db. Don't forget that you will need to run a delete query after the append query to actually remove the records from the original table.

Maq: Thank you for your gracious response. I'll try to make sure that I fall into the third half.

hth Jeff Bridgham
bridgham@purdue.edu