| Author |
Topic |
|
AlanS
Starting Member
28 Posts |
 Posted - 2004-03-17 : 10:26:55
|
| In Access 97, I have an append query involving 1 local table plus 3 tables linked in from SQL Server 2000 (the SQL View of the query is shown below). I also have a macro set up to clear several local tables (including the local table involved here) and then run a number of queries, including this append query. When the macro gets to this query, it always blows up with the error message:[Microsoft][ODBC SQL Server Driver][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique. (#169)In fact, the query has only a single field in the order by list, and that field is specified only once. Sometimes, I have been able to get around this problem by manually running this and all subsequent queries after the macro blows up, but now even that doesn't work.What is causing this error and how can I get around it?INSERT INTO KronosMostRecentActivity ( ActivityCode, ActivityStartDate, SSN, InitialVisitDate )SELECT dbo_Activities.ActivityCode, dbo_Activities.ActivityStartDate, KronosT1.SSN, KronosT1.InitialVisitDateFROM ((KronosT1 INNER JOIN dbo_ApplCert ON (KronosT1.InitialVisitDate = dbo_ApplCert.InitialVisitDate) AND (KronosT1.SSN = dbo_ApplCert.SSN)) INNER JOIN dbo_Enrollment ON (dbo_ApplCert.InitialVisitDate = dbo_Enrollment.InitialVisitDate) AND (dbo_ApplCert.SSN = dbo_Enrollment.SSN) AND (KronosT1.EnrollmentID = dbo_Enrollment.EnrollmentID)) INNER JOIN dbo_Activities ON dbo_Enrollment.EnrollmentID = dbo_Activities.EnrollmentIDORDER BY dbo_Activities.ActivityStartDate DESC; |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-03-17 : 10:35:22
|
| i would get rid of the ORDER BY completely. you don't need it with an INSERT .... the order of the insert is irrelevant (unless you have some fancy running totals VB functions in there).- Jeff |
 |
|
|
AlanS
Starting Member
28 Posts |
Posted - 2004-03-17 : 10:39:58
|
| Unfortunately, I can't get rid of the ORDER BY clause. The table into which we're inserting is keyed by social security number and contact date, and for this query we need to insert only the most recent record for each social security number. |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-03-17 : 11:07:48
|
quote:
Originally posted by AlanS
Unfortunately, I can't get rid of the ORDER BY clause. The table into which we're inserting is keyed by social security number and contact date, and for this query we need to insert only the most recent record for each social security number.
I am not sure what you mean here. Does the SELECT statement without the ORDER BY return the expected results you want inserted into the table? You have no aggregate (MIN()/MAX()) activities in the query or filtering (WHERE...) so the data you are inserting is a result of the SELECT and if that is correct then the order they are inserted to the table is irrelevant.Unless there is information we currently don't have, it appears that the Dr.'s diagnosis is correct. |
|
|
|
AlanS
Starting Member
28 Posts |
Posted - 2004-03-17 : 11:25:42
|
| Sorry if my reply was not clear. The output of this query may contain multiple records for a given combination of Social Security Number and Initial Visit Date (each with its own Activity Start Date). In the destination table, we need to have only the latest record for each combination of Social Security Number and Initial Visit Date. To enforce that, we key the destination table by Social Security Number and Initial Visit Date, and add an "ORDER BY dbo_Activities.ActivityStartDate" clause to the query so that the latest records will be returned first.The order of the inserts is relevant here, because the table will only accept one record for each Social Security Number/Initial Visit Date combination, and we need that record to be the latest one. |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-03-17 : 12:00:05
|
| The only things I can think of with the information I have now is:In the build of the local table, do so as to only have the most recent activity in that table and run the insert.You could try to remove any indexes on the date on the local table and see if that helps.I cannot duplicate your error at this time. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-03-17 : 20:24:00
|
| OK, I still don't get it. Alan, are you saying that rather than using filtering, you are relying on some sort of Primary Key violation to only insert the most recent record for the group, and the violation will reject the others? I'm shocked that would even work. In SQL Server the entire INSERT...SELECT statement would be an atomic transaction and when the PK violation arose it would roll back. I would have expected Access to behave the same way, but it might not.Nonetheless, I think a better (if not the only) approach would be to modify the SELECT portion of your statement to return only the records you want inserted. I'm thinking probably a GROUP BY clause will be needed with a max().--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
AlanS
Starting Member
28 Posts |
Posted - 2004-03-18 : 10:37:09
|
| Yes, we are doing exactly what you described. Although this won't work in SQL Server, it's perfectly legal in an Access database. In our experience, it's the easiest way to handle the situation where multiple records would qualify for insertion for each unique value of a prospective key, and we need to limit the insertion to no more than one record per such value, and the record we want to select from among those having each such value can be identified by having either the minimum or maximum value in some other field that is not part of the prospective key. In Access, the entire INSERT operation is not atomic unless you explicitly contain it within a transaction.I don't think using the GROUP BY option will help here. My understanding is that when GROUP BY is used, each column in the select list must either be an aggregate expression or included in the GROUP BY clause, and that won't work here. For example, If there 6 records for a given Social Security Number and Initial Visit Date, with each having a different Activity Start Date value, we only want the one that has the latest Activity Start Date value. We can't use GROUP BY because there are many other fields that we also want from that record. If we include those fields in the GROUP BY clause, we'll end up getting all 6 records anyway, totally defeating the purpose of the clause. Nor can we make those fields part of aggregate expressions, because we need the specific value from each selected record.I'm not sure how to accomplish this with filters, but of course would welcome any suggestions. |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-03-18 : 10:51:06
|
quote:
Originally posted by AlanS
In our experience, it's the easiest way to handle the situation where multiple records would qualify for insertion for each unique value of a prospective key...
...but it is not the best or most portable way.You could try this. Build a select query in your database from your worktable KronosT1 with three fields: SSN, InitialVisitDate, and MAX(ActivityStartDate). In your insert query JOIN the select query back to KronosT1 on three join conditions SSN=SSN, InitialVisitDate=InitialVisitDate, and MaxOfActivityStartDate=ActivityStartDate. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-18 : 10:58:04
|
| You'd be surprised (or maybe you wouldn't) at how many applications, even enterprise-level, high-priced applications, rely on methods like this. eiStream's Workflow for Windows (now called WMS), in order to determine if it needs to search the archives for a record, first attempts to insert the record into the workflow table....if it fails, it knows the item is already in the workflow table and so does not attempt the archive retreival.It's pretty goofy. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-03-18 : 11:39:26
|
| All I can say is .... Holy Schnikies !!! this is scary stuff, based on what CrazyJoe and Alan are saying....Alan -- you need to work on some SQL skills, and you will see (as DrymChaser has demonstrated) that you can quite easily distinctly select what you need. You shouldn't rely on expected errors or anticipated side effects to get the results you need ! as you've discovered, this approach isn't very scalable and certanly not efficient and/or intuitive.What is the PK of the table you are inserting into?- Jeff |
|
|
|
AlanS
Starting Member
28 Posts |
Posted - 2004-03-18 : 15:39:03
|
| I will probably end up using a modified version of drymchaser's approach. It turns out the SSN, InitialVisitDate and ActivityStartDate are not sufficient to guarantee only one record per SSN/InitialVisitDate combination, so I'll need to add another field.The primary key of the table I'm inserting to is presently SSN+InitialVisitDate, but it only has a primary key to prevent duplicate values. The data ultimately ends up in a text file, so keys are only relevant to this intermediate processing. |
|
|
|
|
resolving bogus error message (#169)