Append Query 08-21-07

I have two tables.
Table A has 349,353 records. 
Table B has 377,787 records.
I need to add the 28, 434 records that are not in table A but are in table B.
Table A and B have a unique 6 digit number as its primary Key. 
When I attempt to run an append query from B to A, I get an error message 
stating it did not add 28, 434 records due to Key Violations. 

Sorting the unique 6 digit numbers tells me right off the bat that there are 
some in B that are not in A, yet when I attempt an append query, it tells me 
there are Key violations. 
I even selected on specific record whose 6 digit number I know is not in 
table A...and it still states that it didn't append due to a Key Violation. 
Are there "invisible" keys Access puts in place that might be causing this? 

TIA
Antonio
0
Utf
8/21/2007 3:26:01 AM
access 16762 articles. 3 followers. Follow

1 Replies
605 Views

Similar Articles

[PageSpeed] 52

On Aug 20, 10:26 pm, Antonio <Anto...@discussions.microsoft.com>
wrote:
> I have two tables.
> Table A has 349,353 records.
> Table B has 377,787 records.
> I need to add the 28, 434 records that are not in table A but are in table B.
> Table A and B have a unique 6 digit number as its primary Key.
> When I attempt to run an append query from B to A, I get an error message
> stating it did not add 28, 434 records due to Key Violations.
>
> Sorting the unique 6 digit numbers tells me right off the bat that there are
> some in B that are not in A, yet when I attempt an append query, it tells me
> there are Key violations.
> I even selected on specific record whose 6 digit number I know is not in
> table A...and it still states that it didn't append due to a Key Violation.
> Are there "invisible" keys Access puts in place that might be causing this?
>
> TIA
> Antonio

use an outer join (use the Find Unmatched wizard if you want).  Then
append those that are not already in the destination table.

0
pietlinden
8/21/2007 3:33:12 AM
Reply:

Similar Artilces:

Business Units 03-08-05
I have an organization with a top level Organization and the 4 subsidiary B U’s. This is all set up in CRM and the CEO can see leads across all BU's. The problem that I am having is that a sales manager can also see all leads through out the organization also ie, leads that they do not own and have not been assigned to them. The sales manager BU is set so that members can only see (read) the leads in their own business unit. Structure – Organization – CEO Business Unit A – sales manager A Business Unit B– sales manager B Business Unit C– sales manager C Business Unit D– sales...

Installation Question 03-21-05
Hi everyone, I have a MSCRM installation on Small Business Server SBS (1 machine installation), I want to change to the medium installation (3 servers: Active Directory server, MSCRM server, Exchange server). is that possible? how can I do it? Thanks. From what I understand, there is an "upgrade" license you can purchase that allows you to "upgrade" the SQL & Exchange licenses so that they don't run on the SBS box. Another option is to purchase a seperate SQL license and leave the version on the SBS server for other usage. Then, once you get SQL setup, there ar...

Append to an existing spreadsheet
How can I append one line or more from one excel spreadsheet to another one, not by copying and paste? Thanks galsaba galsaba, Retyping? Aside from copying and pasting, that's about it. Even a VBA solution must copy and paste. Kevin "Galsaba" <galsaba@aol.com> wrote in message news:20030921133717.06003.00001199@mb-m07.aol.com... > How can I append one line or more from one excel spreadsheet to another one, > not by copying and paste? > > Thanks > galsaba Do you mean something like =Sheet2!A1 typed into a cell on Sheet1? Bernard "Galsaba"...

running query
Hello, I was trying to create a chunk of code like follows: Dim qry As DAO.QueryDef 'the actual query to be generated Dim qryName As String 'the name given to the query sql = "SELECT * INTO AppendAllFields FROM [some table];" Set qry = db.CreateQueryDef(qryName, sql) DoCmd.OpenQuery (qryName) where qryname is alr. defined. When I wronf it, it kept giving me the error on the DoCmd line saying that runtime error 3001, invalid argument.... Anyone could help? Thanks! On 27 Apr 2007 11:09:43 -0700, azhu.uwaterloo@gmail.com wrote: >Hello, >I was trying to create a ch...

leading zeros 03-05-08
I have a table with a list bldg numbers some are 3 characters and others are 4 characters, the question is how can I place the leading zeros before each bldg number for example I have a building 100 it needs to be 00100 or 01200 how can I make access do this for me? Thanks for your support -- Robb In the table, you can do so by changing the field to text. The number 0100 is simply stated as 100. If the leading zeros are important, then it is not a "number" as far as Access is concerned. Numeric fields are used primarily if you will be doing math on the numbers. Will you ...

Recipient policies #21
In Exhcnage 2003 SP2 will Recipient policies effect PST files. We are putting in place a policy to wipe mail after 90 days. We would like the recipient policies to do the same with PST files. Is this possible? Thanks No. Recipient polices only affect the servers, and cannot reach onto the client machines. "Infintiy266" <Infintiy266@discussions.microsoft.com> wrote in message news:FE1CC79A-8F41-4121-A68B-2F0AF575AAD3@microsoft.com... > In Exhcnage 2003 SP2 will Recipient policies effect PST files. We are > putting > in place a policy to wipe mail after 90 day...

SFO problems 02-21-06
Re-posting because i didnt get a response. Can someone please help?? Matt, John, anyone??? We have developed a custom multiselect lookup in .net. This .net page is called from an iframe inside the entity form. This works fine on the server. However, when we deploy this code on SFO (we have copied the project folder and created a virtual directory on the SFO client machine), an alert message saying "access is denied" is popping up. Can someone please help with this???? CRM 3.0; SQL 2000; VS.NET 2003. Looking to upgrade to SQL 2005 and VS.NET 2005 Good chance it has something ...

crosstab query question 01-26-08
I'm using A 2000. I've created a crosstab query that finds data based on a date range that I supply just before opening it. The query will add or remove columns depending on that date range, and it will label the top of the column with the appropriate dates.The query works perfectly. One of the fields in the query is a UserName. I want to be able to double click the Username, and have it open a form and go to that Username's record. Of course, queries don't have events like a form does. So I don't see any way to open my filtered form from a record in the query. If I base...

CrossTab Query that shows original data (text)
I am a scheduler and would like to build a pivot table in Excel or a Cross Tab Query in Access that shows the data in its original form (text) instead of calculating the count, sum, avg., etc. Row headings would have member’s names. Column headings would have times. Data items would be the name of the assignment. Any suggestions would be appreciated. Thanks. Jim G You can use a text field for the Value of a crosstab with FIRST or LAST or MIN or MAX. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.h...

Query of External Data
I'm using a ODBC connection to query a oracle database. If I refresh a query and email the file to the intended recipients, I can limit what they can see by changing the query to include/exclude certaing markets. Is there a way that I can set up the query so the recipient cannot edit the query and have access to all the data...? Or is there a way to disconnect the query from the excel file, this way the query could never be refreshed to the database. We could email them the file daily, but they wouldn't be able to refresh or change the query(to view markets they normally ...

Median 06-08-07
I'm using the following function which I found on another site. Public Function DMedian(FieldName As String, _ TableName As String, _ Optional Criteria As Variant) As Double On Error GoTo Err_DMedian 'Returns the median of a given field in a given table. 'Returns -1 if no recordset is created Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim RowCount As Long Dim LowMedian As Double, HighMedian As Double 'Open a recordset on the table. Set db = CurrentDb strSQL = "SELECT " ...

Shared database 05-21-07
Hi, I have a database file (MDB) on my assistant s PC. When I open this file in Access in that PC, i am unable to open the same file on my PC. (2 PCs on peer to peer) If I open the same file from my PC first, then it opens fine on her PC too. On both PCs it is set to open in Shared mode. In both cases, the .LDB file is created on opening. Where could the problem be? Every time i need to use the file, i am having to ask her to close the db, till i open it for my use. Once i open it, then she re opens to continue her work. Could anyone help me save this pain please? Thanks Rame...

INSERT / UPDATE Query in same operation
Hi This is a backend database for .NET application Table1 is a linked table to a text file on the network, which is updated from an ERP system. Table2 is the table my application uses when the PC is offline. I want to update Table2 with Table1 on those rows who exist in both tables, and insert the new rows from Table1 into Table2, in the same SQL call. Is it possible at all? Regards JensB No. However you could put both queries in a macro or code and they would run so fast that you wouldn't know the difference. You could even Set Warnings off before the first quer...

ISV.config query
Hi, Im not a programmer and have limited experience with coding etc. However I wish to add a button to the account page/form via the isv config file. I want this button to get the website address of the company from the form and then use it to open the company website in a new window. Could anyone give me an indication of how to do this or even a sample bit of code...? This would be very helpful Thanks in advance Hi, The easiest way is probably for you to take a look in the isv.config file and just copy and paste. The test.gif and the url is just an example! Here is an example of...

Unable to bring back all results from table with query
For a uni assignment we have a designated query that we have to create. But when I run the query, I get one result returned to me. I should have two returned to me, as I have two records in the Problem table. The SQL is:- SELECT Problem.[Problem Number], User.[User ID], User.[User Name], User.[Job Title], User.Email, User.[Telephone No], Asset.[Asset Code No], Problem.[Problem Description], Problem.[Current Activity Log], Problem.[Solution Description] FROM [User] INNER JOIN ((Asset INNER JOIN Problem ON Asset.[Asset Code No] = Problem.[Asset Code No]) INNER JOIN [Contact Log]...

DSum 01-10-08
Im using a DSum function on my form to total a column in a query. However, when there are no records that meet the criteria, the DSum field is blank. How can I make it show a 0 instead of just an empty textbox?? You can probably wrap the whole expression in the Nz function: Nz(DSum("SomeField","SomeTable"),0) It would have helped had you posted the actual expression. "Tray" <Tray@discussions.microsoft.com> wrote in message news:6346868D-AEB8-4C02-9425-C67C6AFB0879@microsoft.com... > Im using a DSum function on my form to total a column in a query. Ho...

get value from a query /set a variable for a report
I put this question in the forms area -- it is a form/report problem. - don't mean to double post, but I should have put it in this area instead. sorry! hello, I have a report that shows the detail for 3 different values of a field named "TransType": (values: pmt, adj, act). The name, date, and TransType fields are set via a form. In the report, I want the detail to show for the chosen TransType(s), name, and date. AND I want a total of the "ADJ" records for the specified name and date to show in a field in the footer. strDocName = "Sales for 1 month&...

Using external query (mdb) for dynamic date range
I'm trying to set up an Excel workbook with a dynamic link to an Access database containing quality data, involving dates. I want the workbook to always import data for the last 90 days. When I try to set this up using MS Query, to try to filter data I've tried several different variations where time is greater than or equal to: date()-30 today()-30 now()-30 And every time, it comes back as an error. It seems to be setting the query so that the entire statement is a date variable, eg "Syntax error in query expression '((Table1.Time>=#date()-30#))'...

Query data from same workbook
Hello all - I have an excel workbook that queries external data that is stored on a different workbook but same computer. It works fine and I haven't had any issues with it. But, now I need to make this workbook easy to send to other users. I would like to consolidate the data and put it on the same workbook with my written queries. I figured it would be easy to do, similar to doing a pivot table on sheet1, and the data source on sheet2. This would make it much easier to send out to all the users so it can find the "data source" every time regardless of who's computer ...

Change Connection of a Query
Hi I try to modify a Query, i want to change the server where the query is pointing to. How can I do that? JF need specifics, lots of ways to do it.... post current query connection string ie: code with current server address "Jean-Francois" wrote: > Hi I try to modify a Query, i want to change the server where the query is > pointing to. How can I do that? > > JF ...

auto save 12-05-07
I am not sure what happen but now when I open my database and start a new query I requires I save it. Instead of giving me the option to click no it only has the yes and cancel button available. Can someone help? On Dec 4, 8:06 pm, Sarah at DaVita <sarah.petter...@davita.com. (donotspam)> wrote: > I am not sure what happen but now when I open my database and start a new > query I requires I save it. Instead of giving me the option to click no it > only has the yes and cancel button available. Can someone help? Hi Sarah I had a similar problem recently with Access 2003. I...

Query to find outliers
I have two tables, one table listing names of people who did not receive a gift card and a second table of people who were taxed for the gift cards. Both tables use the name field ( I did not set up the tables but this field is first and last name) in common. I need to find out if there are people on the tax list who did not receive a gift card. So if a name is on the tax list it should not be on the did not receive list. Therefore, they were taxed for the card but never received it. ...

Sorting a query with a twist
I have a query I am trying to create based on another query (inventory) and a table. The inventory query shows the date, shift, Die set and the # of shifts remaining for each die set in inventory. The table has a corresponding date and shift field as well as two more fields that tell me what die set was left in the line at the end of the previous shift and what die set is staged to go in next (again left from the previous shift). When the inventory query is run it tells me what die set has the lowest inventory to the highest. However, the die set with the lowest inventory may not be the one ...

CRM Exams 02-06-07
hi, What are the exams available for CRM. please let me know the exam codes and pass percentage. where can i get the manuals /books for the same. thanks ...

Pass through query giving an invalid column error
I am trying to write a pass through query using Access 2000. My source table has a field that has three blank spaces as a place holder. I do not want or need records that have three blanks in this field as well as records numbered 840. I am trying to write an SQL statement that will eliminate those records from my query. I am using the following code: select [table].[field] from [table] WHERE ((Not ( [table].[field] )= "840" or Not ( [table].[field] )=" ")); I get an error at line 3 invalid column name '840' and ' '. I can remove the quote...