Append query insert only if the record already not exist Results

I hereby attached a sample file in which i want to run an append query, i have not worked with append query before in ms-access, actually i want to insert data into table if it is already not existing in destination table, the current scenario is that ms-access will automatically add the data everytime when i execute the query that contains alot of duplication's. Please help me out how to insert only those records which are not existed in the appended table (i-e only insert unique records). Thanks

I'm having a hard time getting an append query to function as I want it to. I need it to group a set of data and add that to a table only if the data does not already exist in the table.

I can get my Append Query to take the data I need and write it to the table I designate.

Here's the SQL for the basic Append Query:
INSERT INTO tblEng2 ( employee, week_end, [Date], proj_num, Task, Div_Letter, Total_hours )
SELECT tblEng.employee, tblEng.week_end, tblEng.Date, tblEng.proj_num, tblEng.Task, tblEng.Div_Letter, Sum(tblEng.hours) AS SumOfhours
FROM tblEng
GROUP BY tblEng.employee, tblEng.week_end, tblEng.Date, tblEng.proj_num, tblEng.Task, tblEng.Div_Letter
HAVING (((tblEng.employee)=[Forms]![frmEng1]![cmbEmployee]) AND ((tblEng.week_end)=[Forms]![frmEng1]![week_end]))
ORDER BY tblEng.employee;

What I'm actually doing is automating the paper time sheets we've been using here at work. On a day to day basis people use the MS Access form to enter the tasks they've worked on. At the end of the week (after they've entered all their weekly tasks) I need to summarize this data on another form.

So all the identical tasks get grouped into one task with an "hours" figure that is a sum of the hours used. I do this using a form configured for Datasheet mode. However, two more columns are added to the datasheet. One is "%Completed" and the other is "Hours Remaining". The user then enters values into these two fields for each of the tasks they've completed during the week. Since we typically work on more than one task a week, a user has a list of various tasks on their timesheet.

I can use an Aggregate Query with a Totals statement to get the grouping for me. And with an Append, I can add this data into a table.

But I need to evaluate EACH line of the results of the query (each task) before I allow it to store into the table. You don't want the same task stored twice!

On another forum it was reccomended that I do the following:

You should use a WHERE clause in the append query so it will not add additional rows or error out. You can use a subquery to do that. it would be something like:

WHERE NOT IN (SELECT Employee, WeekEnding FROM TotalsQuery)

I don't know your field names or your Totals query names but so you will have to tailor it to suit. I use employee and WeekEnding, because based on your posts, that seems to create a unique record. If you have to add other fields so that it will not try to duplicate a record, then do that.

I have tried implementing this with little success. But I attribute this mostly to my lack of experience with SQL. I really don't understand the nomenclature of the "WHERE NOT IN".

Hopefully I've explained the problem well enough. If not, I can elaborate. Can someone help me?

Hi all!

I have 2 tables. Table 2 has 6 date fields that belong in existing records in Table 1. If there are already dates in those fields of Table 1, they need to be overwritten with Table 2's data (but those fields ONLY, none of the other Table 1 fields should be overwritten).

The problem is there is no unduplicated primary key field between the two tables; it needs to be checked like:

If [Table 1].[Field 1] matches [Table 2].[Field 1] AND [Table 1].[Field 2] matches [Table 2].[Field 2], insert (the six date fields) ONLY from Table 2 INTO those records in Table 1.

Of course that's not real code but I'm just trying to explain the scenario.

Is this possible? Right now I'm just typing all of these dates in by hand but there are close to 6,000 records x 6 date fields per, and it's going to take me FORever.

I am trying to import records from tally erp 9 through xml.I have created database & form to import from xml file created by tally(tally is name of my accounting software).its also importing database but every time i have to do this import thing manually.Do any one have any macro for automatic importing the data from the specified xml file every time i open the ms access database?

I am using ms access 2003.while importing data from the xml file i keep on the option "append data to existing table(s)" to get only update entries (file>get external data>import>xml>options>append data to existing table(s))

Please help me out.....its very urgent......thank you.
Waiting for reply



while surfing i came accross one page from msdn database which might be useful

__________________________________________________ ______________
Bind an Access Form's Record Source from an XML File at Runtime
Tim Getsch
Frank Rice
Microsoft Corporation
May 2004
Applies to:
Microsoft® Office Access 2003
Summary: Learn how to bind a form dynamically to a recordset created from an XML file. This simple application can be the starting point for a powerful solution for your customers and should be considered a part of your arsenal when developing Access application. (8 printed pages)
Contents

Introduction
Overview
Import XML and Create an Access Table
Function to Determine the Table Name
Routine to Bind a Recordset to an Unbound Form
Conclusion
Introduction

With the prominence of Extensible Markup Language (XML), it is likely that at one time or another while developing solutions in Microsoft® Office Access 2003, you will need to import and use data from an XML file. Likewise, there may be instances where you need to bind the record source of a form at runtime. Fortunately, both of these operations are relatively easy to create and are equally easy to combine into one. Among the benefits of combining the operations into one smooth function are that you remove a level of complexity from your users. This also makes your solutions appear more professional as these operations are completed behind the scenes and away from your customers.
This article demonstrates programmatically importing an XML file to an Access table. A recordset is then created from the table and dynamically bound to a form. Note The examples in this article are written using the Data Access Objects (DAO) library. For similar examples using ActiveX Data Objects (ADO), see the book Programming Microsoft Office Access 2003 from Microsoft Press written by Rick Dobson.Overview

This application works by first prompting the user for the path and name of the XML file to be imported. The code then sets a variable to the current date and time. This variable is used later to help find the table created during the import operation. Next, the procedure imports the XML file and creates the table. Then, the RetrieveNewestTableName function is called with the current data and time variable created earlier. This function retrieves the name of the table created during the import. The returned value is then checked to make sure it isn't empty and thus, generate an error. Next, the user is prompted for the name of the unbound form. The recordset will be bound to this form. Finally, the subroutine calls the BindRstToUnboundForm function that binds the table's recordset to the form's Recordset property.
Next we'll look at the function that determines the name of the newly created table. To understand why this procedure is necessary, consider that the table created during import is based on the name of the XML file. If the name of the input file matches the name of an existing table in the database, the new table uses the name of the existing table concatenated with a number incremented by 1. So, for example, if the name of the XML file is Employees.xml and the Employees table already exists in the database, the new table is created as Employees1. If you import the file again and the Employees and Employees1 tables exist, the new table is designated Employees2. So to make sure that the recordset is created from the correct table, it is important to determine which table is the correct one.
To determine the correct table, the code loops through the list of tables and first checks for ImportErrors table and, if found, ignores the table. The ImportErrors table is created if there are errors during the import of data from, for example, the XML file. Because this could be listed as the latest table, it is eliminate in the code. Next, the variable containing the current date and time is compared to the DateCreated property of the each table and the name of the latest table is stored in a variable. The name of the table is then returned to the calling procedure.
Once the code determines the table name, the name of the table and form are passed to another function that creates a recordset based on the table. The procedure then assigns the recordset to the Recordset property of the form. The form is displayed and the code displays a test record in the Immediate window of the Microsoft Visual Basic® Editor to verify that the routine is working correctly.
Import XML and Create an Access Table

The code in this section demonstrates how to use the ImportXML method to import data from an XML file and create a table.
Ensure that you have an XML and XSD file in the correct format such as those created by the ExportXML method. You create XML and XSD files from Access by using the ExportXML method in a standard module in the Visual Basic Editor or on the File menu, by clicking Export and then selecting XML in the Save as type drop-down box. To use the ExportXML method, the following code example exports the table named Customers in the current database to an XML file; the data and schema are exported as separate files.

Application.ExportXML _
ObjectType:=acExportTable, _
DataSource:="Customers", _
DataTarget:="Customers.xml", _
SchemaTarget:="CustomersSchema.xsd"


Next, ensure that you have an unbound form with fields similar to the imported table to bind to the recordset. For the purpose of this article, you can create one from the existing Customers form in the Northwind sample database. To do this:Start Access and open the Northwind sample database. By default, this database is located at C:Program FilesMicrosoft OfficeOffice11SamplesNorthwind.mdb. Click the Customers form. On the File menu, click Save As, type a name for the new form (Customers1, for example), and then click OK. Make sure that the Record Source property is blank by opening the form in Design view. On the View menu, click Properties to open the Properties window for the form. Click the All tab, scroll to the top to the Record Source box and clear it, if necessary. Then, create another unbound form. Note This form is different that the form created in step 3 and is used to run the procedures that make up the application. Add a command button by performing the following steps:In the Database window, click Forms under Objects. Click the New button on the Database window toolbar. In the New Form dialog box, click Design View. Leave the table or query source drop-down box blank. Click OK. Double-click a command button from the Toolbox to add it to the form. If the Toolbox isn't visible, click the View menu and then click Toolbox. Click Cancel on the Command Button Wizard dialog box. Position the command button in the center of the form. Add code to the command button with the following steps:With the form in Design view, double-click the command button. In the Property window, click the Event tab, and then click the OnClick property. Click Build button next to the property box to display the Choose Builder dialog box. Double-click Code Builder to display the event procedure Sub and End Sub statements in the form module. Next, set references to the Microsoft Office 11.0 Object Library library and the Microsoft DAO 3.6 Object Library library with the following steps:On the Tools menu, click References. In the Available References box, select the check box next to the Microsoft Office 11.0 Object Library library. Next, select the check box next to the Microsoft DAO 3.6 Object Library library reference, if necessary. Add the following code to the OnClick event procedure between Sub and End Sub statements:

Dim fd As Office.FileDialog
Dim strTableName As String
Dim strFormName As String
Dim datBeforeImport As Date
Const strFileExt = ".xml"

On Error GoTo Path_Err

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Filters.Add "XML", "*.XML"
fd.Show

'User didn't enter a file path.
If fd.SelectedItems.Count = 0 Then
MsgBox "You must select an " & _
"XML file. Please try again."
Exit Sub
End If

'Set variable later used to find table created
'by ImportXML method.
datBeforeImport = Now
'Invoke the ImportXML method against the xml file.
Application.ImportXML fd.SelectedItems(1), acStructureAndData

'Check to make sure that RetrieveNewestTableName
'function doesn't return an error code which would
'generate a false error in this procedure.
strTableName = RetrieveNewestTableName(datBeforeImport)
If strTableName = "" Then
Exit Sub
End If

'Prompt user for name of form to use.
strFormName = InputBox("Type the name of the form you want to " & _
"use. It should have the same fields as the " & _
"recordset (table) the form will be based on.")

BindRstToUnboundForm strTableName, strFormName

Exit_Sub:
Exit Sub

Path_Err:
If Err.Number = 31527 Then
MsgBox "The XML file was not found. Check the spelling " & _
"or that the file exists and try again."
GoTo Exit_Sub
Else
MsgBox "Operation aborted for the following reason. " & _
vbCrLf & "Error Number: " & Err.Number & " " & _
vbCrLf & "Error Description: " & " " & Err.Description
GoTo Exit_Sub
End If


This subroutine essentially imports a XML file and creates an Access table. It also calls the other procedures in the application.
The procedure starts by displaying a file dialog box using the FileDialog method of the Application object, filtered for XML files in order to prompt the user for the name and path to the XML file. Then, the code sets the variable that compares against the creation date and time of the table created by the ImportXML method. Next, the subroutine calls the ImportXML method to read the XML file and create a table for the data. The subroutine then calls the function RetrieveNewestTableName and checks to see if that function has returned an empty String. Next, the procedure prompts the user for the name of the form that the recordset will be bound to. Then the BindRstToUnboundForm function is called.
Function to Determine the Table Name

The following function cycles through a filtered list of tables in the database and determines the name of the table just created during the import of the XML file.
Add the following function in the area outside of the previous subroutine.

Function RetrieveNewestTableName(datStartDate As Date) As String
Dim datDateComp As Date
Dim strNewestTableName As String
Dim tbl As DAO.TableDef
datDateComp = datStartDate
For Each tbl In CurrentDb.TableDefs
If tbl.DateCreated >= datDateComp Then
If Left(tbl.Name, 12) = "ImportErrors" Then
' Ignore ImportErrors tables
Else
strNewestTableName = tbl.Name
datDateComp = tbl.DateCreated
End If
End If
Next tbl
RetrieveNewestTableName = strNewestTableName
Debug.Print RetrieveNewestTableName
End Function


This procedure determines the name of the table created during the import operation. It does this by comparing the DateCreated property of the tables.
The procedure first sets a local variable to the date and time that later will be compared to the creation date and time of each table. Then, the procedure loops through each table in the TableDefs collection of the CurrentDB object and, first, determines if the current table is the ImportErrors table. If there were errors during the import process, the records that weren't imported are stored in this table. Since this table might be the latest created, it would erroneously be bound to the form if selected. Next, the For Each...Next loop compares the creation date to (initially) the current date. If the creation date and time of the table is larger, then the name and creation date of the table are stored in variables. The cycle repeats and the comparison is now between the last table that met the criteria and the current table. Either way, when all of the lists of tables are examined, the code passes the name of the latest table to the RetrieveNewestTableName procedure and the table name is then displayed to the user.
Routine to Bind a Recordset to an Unbound Form

The following subroutine illustrates dynamically assigning a recordset to an unbound form at runtime. Insert the procedure to the area outside the previous function:


Function BindRstToUnboundForm(strDataSource As String, strFormName As String) As DAO.Recordset
On Error GoTo ErrorHandler

Set BindRstToUnboundForm = CurrentDb.OpenRecordset(strDataSource, dbOpenDynaset)

'Assign recordset to Recordset property of form.
DoCmd.OpenForm strFormName
Set Application.Forms(strFormName).Recordset = BindRstToUnboundForm

'Print record to check that things went smoothly.
Dim fld As DAO.Field
For Each fld In BindRstToUnboundForm.Fields
Debug.Print fld.Name & " = " & fld.Value
Next

Exit_Sub:
Exit Function

ErrorHandler:
Select Case Err.Number
Case 2102
MsgBox "The form was not found. Check the spelling " & _
"or that the form exists and try again."
GoTo Exit_Sub
Case 2494
MsgBox "You must type a name for the form."
GoTo Exit_Sub
Case Else
MsgBox "Operation aborted for the following reason. " & vbCrLf & _
"Error Number: " & Err.Number & " " & vbCrLf & _
"Error Description: " & " " & Err.Description
GoTo Exit_Sub
End Select
End Function


This subroutine starts out creating a recordset from the table created with XML data. It does by using the OpenRecordset method of the CurrentDB object. Next, the unbound form is displayed using the OpenForm method of the DoCmd object. Then the table's recordset is assigned to the Recordset property of the form. And finally, a test record is displayed to verify that the application executed as expected.
Conclusion

Dynamically setting the record source of a form is a handy way to make your forms more versatile. Likewise, programmatically importing an XML file to a table is a quick way to put that data to use. Combining the two operations hides a level of complexity from your users. This simple application is the starting point for an even more powerful solution for your customers and should be considered a part of your arsenal when developing Access application.

__________________________________________________ _______________

Hi,

I am working on an unbound form that is used to add/edit records, there is a command button on the form that should recognize if a new record it's being added to my table, or if the users is modifying an existing record, the command button should update the fields instead of adding a new record.
What would be the best way to do this?
I have a vague idea,I could check if the primary key (InquiryID) already exists then do and update and if the primary key doesn't exist do an Insert query, Im not sure how to code this.

The following is my command, it only adds records, what do I need to add or modify?
I'll be glad to provide more details.

Private Sub Submit_Click()
On Error GoTo Err_Submit_Click

If MsgBox("Are you sure you want to add a new inquiry?", vbYesNo) = vbYes Then

Dim stDocName As String
stDocName = "APPEND - DATA" 'This is the query that adds new records
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True


Err_Submit_Click:
MsgBox Err.Description
Resume Exit_Submit_Click

Else
Cancel = True
End If


End Sub


Not finding an answer? Try a Google search.