|
|
|
Insert into .... |
|---|
| message from =?Utf-8?B?Sk1vcnJlbGw=?= on 12 May 2004 |
Is there such a syntax as INSERT DISTINCT.... in an SQL statement?
insert into TblEmpLeave (LeaveDate, EmpID, SUpdateVal, AUpdateVal, leavetype) SELECT date() as newdate, SSN, saccrue, aaccrue, 'z' from tblemp
When I run this statement as-is in a DoCmd.RunSQL it doubles up on records where there are more than 1 person with the same last name. How can I make sure that there are no update duplications when when I execute this SQL? Key field in table is SSN.
tia,
JMorrell
|
| Wayne Morgan replied to =?Utf-8?B?Sk1vcnJlbGw=?= on 12 May 2004 |
Try making a Select query based on tblemp. You can use Distinct there. Then
use this query as your data source in the Append Query instead of tblemp.
|
| Wayne Morgan replied to =?Utf-8?B?Sk1vcnJlbGw=?= on 12 May 2004 |
I don't know if the Distinct will work within the Append query's Select part
or not, I haven't tried it. But, basically, just work on a Select query.
Once you get it returning the results you want to append, either use it
instead of the table in the Append query or paste the SQL from the Select
query into the Append query for the Select part.
|
| =?Utf-8?B?Sk1vcnJlbGw=?= replied to Wayne Morgan on 12 May 2004 |
I added DISTINCT to the Select section of the Insert Into statement and it still doubled up for the 2 "Smith" records.
I thought about creating a temp table and updating another based on entries in the temp table, but I'm not sure how to go about that. ??
tia
|
| Wayne Morgan replied to =?Utf-8?B?Sk1vcnJlbGw=?= on 12 May 2004 |
Did you try just making a Select query and seeing if you can get it to
return what you're wanting? Are you wanting an Append query or an Update
query? An Append query (what you have) will add new records to the table, an
Update query will change (update) the records that are already there. So,
with the Append query, the records that are returned by the Select portion
will be added to the table. Copy the Select portion and paste it into SQL
view in a new query and see what it returns. Is it what you want to have
added to the table?.
|
| =?Utf-8?B?Sk1vcnJlbGw=?= replied to Wayne Morgan on 12 May 2004 |
My intent is to APPEND new records to an existing table. When I take the SQL and run it through the "query" SQL window and got the same results; double entries. When I take out the INSERT INTO part, it works fine; no double entries. I'm even more confused now as to why it works that way.
How can I create a temp table in my Sub and then append from that temp table to my other table? Or is that even a consideration?
tiaa
|
| Wayne Morgan replied to =?Utf-8?B?Sk1vcnJlbGw=?= on 13 May 2004 |
It's a consideration, but if the Select part is returning only one item,
then I doubt that it would help. Before you go to the trouble of setting
everything to use a temp table, try creating one temporarily with the
appropriate fields and run your append query to append to this temp table.
Start with no records in the table. What is the result?
What do you mean by "duplicate entry". Is the append query adding 2 records
that are the same or is one of the records already in the destination table
and the append query is adding one more?
|
| Pieter Wijnen replied to Wayne Morgan on 13 May 2004 |
The simplest solution is to make a unique index on the field(s) you don't
want to be duplicated in the destination table
HTH
Pieter
"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:%23JVjowKOEHA.3124@TK2MSFTNGP12.phx.gbl...
|
| =?Utf-8?B?Sk1vcnJlbGw=?= replied to Pieter Wijnen on 14 May 2004 |
Please see my reply to Dirk regarding duplicate records and making field(s) uinque. The INSERT statement does give duplicate SSN, but not duplicate names.
|
| =?Utf-8?B?Sk1vcnJlbGw=?= replied to Wayne Morgan on 13 May 2004 |
<shaking my head>
There are 2 records in tblEmp where the last name is Aragon (Joe and Jane, different SSNs, which is the key field). When I APPEND to tblEmpLeave, I get 2 "Appends" for Aragon, Joe and no "Append" for Aragon, Jane. When I do a SELECT FROM tblEmp, I get 2 separate entries; one for Aragon, Joe and one for Aragon, Jane.
I just created a Make Table query with:
SELECT Date() AS newdate, tblemp.SSN, tblemp.SAccrue, tblemp.AAccrue, 'z' AS Expr1 INTO Tbltest FROM tblemp;
which created separate records for Aragon, Joe and Aragon, Jane.
So far, the only time I don't get 2 separate records for the Aragon's is when I do an APPEND query.
</shaking my head>
|
| Dirk Goldgar replied to =?Utf-8?B?Sk1vcnJlbGw=?= on 13 May 2004 |
"JMorrell" <anonymous@discussions.microsoft.com> wrote in message
news:CA022AAB-9597-4B2C-A5B9-819757B114C1@microsoft.com
PMFJI, but I'm puzzled. As far as I can see from the SQL you've posted
in this thread, you are not appending employee names into your
tblEmpLeave at all. So how could you be appending duplicate employee
*names*? Are you actually getting duplicate SSNs, not names? Does your
table tblEmpLeave show you names, not SSNs? If so, that would have to
be the operation of a lookup field, not showing you the data that is
actually in the table, and you could be at the mercy of the combo box
used to lookup and display the name. I can't quite see how that would
be giving you the results you report, but I do have to wonder if what
you *think* is in your tables -- either tblEmp or tblEmpLeave -- is not
what is really there.
|
| =?Utf-8?B?Sk1vcnJlbGw=?= replied to Dirk Goldgar on 14 May 2004 |
I finally figured it out. And I was indeed blind to the reason this was happening. My report was grouping on LastName. I resolved the problem by adding a new field in the query, concatenating the LastName to the FirstName. Then, by grouping on this concatenated field, I get the sorting I was after, and also the unique records from the tables. I feel like such an idiot (but wiser, I hope).
Thanks for all your help and advise on this.
JMorrell
|
| Wayne Morgan replied to =?Utf-8?B?Sk1vcnJlbGw=?= on 14 May 2004 |
Thanks for getting back to us. With what you had shown us, it didn't make
sense that the query was doing it. The grouping makes sense.
|
| =?Utf-8?B?Sk1vcnJlbGw=?= replied to Dirk Goldgar on 14 May 2004 |
Let me back up a little. The INSERT statement doesn't actually append duplicate names. The statement creates duplicate entries into tblEmpLeave (key field is LeaveID) for records in tblEmp that have duplicate names. I can't have tblEmpLeave.SSN a key field because employees will take multiple leaves. The queries that feed the report all take a SELECT DISTINCT approach to extract the entries. I must be blind to not see what's going on here.
I would like to add a statement in the INSERT statement that will insert a row into tblEmpLeave for each entry in tblEmp (tblEmp's key field is SSN, no duplicates). Is there such a syntax for that?
any thoughts on this last bit?
|
| Wayne Morgan replied to =?Utf-8?B?Sk1vcnJlbGw=?= on 14 May 2004 |
If just the Select part, when used as a Select query, returns the correct
answer, then the Append part should simply add that answer to the table
desired - no modifications. If this isn't what is happening, then it sounds
as if there is something corrupted or needs to be updated (bug in earlier
version). Have you installed the most recent Office service pack for your
version of Access? Also, have you installed the newest Jet and MDAC releases
for your version?
Jet & MDAC
http://msdn.microsoft.com/data/downloads/updates/default.aspx
Office
http://office.microsoft.com/officeupdate/default.aspx
|
|
