Append Query Question

[ramez75]

I have been testing my database and have found an issue when using one of my append query. Everytime I run the append query (On Close form event) I am ending up with duplicates but I want the append query to be smart and only append those rows that are not equal in tblRecords.

Example

tblRecords consist of field1, field2, field3, field4, field5 and currently have lets say 5 records as shown below
field1 field2 field3 field4 field5
Row1 - ABC 3 Test
Row2 - BCA 3 Name
Row3 - CAB 2 Test
Row4 - ZXC 0 Something
Row5 - THC 1 Test

So I am appending information from tblTemp into tblRecords

tblTemp consist of field1, field2, field3, field4 and field5 and currently have lets say 5 records as shown below
field1 field2 field3 field4 field5

Row1 - ABC 3 Test
Row2 - BCA 3 Name
Row3 - CAB 3 Test
Row4 - ZXC 1 Something
Row5 - THC 1 Test

So the append query should append ONLY row3 and row4 from tblTemp to tblRecord as the other rows are similar. So tblRecord will look like this

field1 field2 field3 field4 field5
Row1 - ABC 3 Test
Row2 - BCA 3 Name
Row3 - CAB 2 Test
Row4 - ZXC 0 Something
Row5 - THC 1 Test
Row6 - CAB 3 Test
Row7 - ZXC 1 Something

Basically the criteria is if field1, field2 and field3 in tblTemp are in tblRecord then ignore otherwise if anyone is different then append from tblTemp to tblRecord

Is that possible.

Thanks

 

[jdraw]

Tell us about your table structures.
Tell us about you Primary keys.
And any unique indexes you may have.

Show us your APPEND query sql.

 

[CJ_London]

I use this as a template which is used in VBA - I replace the bits in curly brackets with the actual tables and fields:

INSERT INTO {DstTable} ({DstFields}) SELECT DISTINCT {TmpSrcFields} FROM {SrcTable} LEFT JOIN {DstTable} ON {SrcTable}.SID = {DstTable}.SID WHERE ({DstTable}.ID is Null)

{DstTable} is table you are inserting to
{DstFields} are the fields you want to insert to
{TmpSrcFields} are the values you want to insert
{SrcTable} is the name of your source table

{SrcTable} links to your destination table on a unique ID (I've called SID) which is concatenated from the values that make it unique - in your case field1 & field2 & field3 i.e.

{SrcTable}.field1 & {SrcTable}.field2 & {SrcTable}.field3 = {DstTable}.field1 & {DstTable}.field2 & {DstTable}.field3

Hope that makes sense

 

[ramez75]

Below is what my current append query looks like. Each time I run it just adds to tblRecords even if they exist

INSERT INTO tblRecords ( field1, field2, field3, field4, field5)
SELECT DISTINCT tblEmployees.field1, tblEmployees.field2, qryNewtblRequired.field3, qryNewtblRequired.field4, tblProcedures.field5
FROM (tblEmployees INNER JOIN tblModEmp ON tblEmployees.field1= tblModEmp.field1) INNER JOIN (qryNewtblRequired INNER JOIN tblProcedures ON (qryNewtblRequired.field4= tblProcedures.field4) AND (qryNewtblRequired.field3= tblProcedures.field3)) ON tblModEmp.strDept = qryNewtblRequired.strDept;

Let me know if you need any other info

 

[CJ_London]

Unfortunately I can't work it out from what you have sent me.

Reread my last post and have a go yourself - for example it looks like:

{DstTable} is table you are inserting to - tblRecords
{DstFields} are the fields you want to insert to field1, field2, field3, field4, field5
{TmpSrcFields} are the values you want to insert tblEmployees.field1, tblEmployees.field2, qryNewtblRequired.field3, qryNewtblRequired.field4, tblProcedures.field5
{SrcTable} is the name of your source table - looks like it is (tblEmployees INNER JOIN tblModEmp ON tblEmployees.field1= tblModEmp.field1) INNER JOIN (qryNewtblRequired INNER JOIN tblProcedures ON (qryNewtblRequired.field4= tblProcedures.field4) AND (qryNewtblRequired.field3= tblProcedures.field3)) ON tblModEmp.strDept = qryNewtblRequired.strDept

So I would suggest the total query will look something like this (may be missing a bracket):

INSERT INTO tblRecords
field1, field2, field3, field4, field5
) SELECT DISTINCT tblEmployees.field1, tblEmployees.field2, qryNewtblRequired.field3, qryNewtblRequired.field4, tblProcedures.field5
FROM (tblEmployees INNER JOIN tblModEmp ON tblEmployees.field1= tblModEmp.field1) INNER JOIN (qryNewtblRequired INNER JOIN tblProcedures ON (qryNewtblRequired.field4= tblProcedures.field4) AND (qryNewtblRequired.field3= tblProcedures.field3)) ON tblModEmp.strDept = qryNewtblRequired.strDept LEFT JOIN tblRecords
ON tblEmployees.field1 & tblEmployees.field2 & qryNewtblRequired.field3= tblRecords.
field1 & tblRecords.field2 & tblRecords.field3 WHERE tblRecords
.Field1 is Null)

 

[jdraw]

So it doesn't appear to have any unique indexes.
An append query will add records to a table.
If you have no constraints, it will keep adding records until it runs out of space.

Ypu need to structure your tables. You must define what makes a record unique. Ans you must define what makes a duplicate. And then adjust your structure (unique index) accordingly.

This may mean deleting some existing records.

 

[ramez75]

CJ_London, I will try what you have posted.

I have attached my test database with sample data to give a better understanding of what I have.

So basically each time I run "qrytblRecordsNew", tblRecords gets appended with all the data regardless if they are there.

 

[CJ_London]

The query structure I provided will only append when the record does not already exist - thats what this bit of sql does

WHERE tblRecords
.Field1 is Null

PS - Your db has not attached

 

[ramez75]

CJ_London, the database is attached in post#7. I just downloaded it and unzipped the file. Its in 2003 format.

Let me know if you having issue and I will try to load it without zipping it

The query structure I provided will only append when the record does not already exist - thats what this bit of sql does

WHERE tblRecords
.Field1 is Null

PS - Your db has not attached

 

[CJ_London]

Sorry - db was attached

Here is the working query:

INSERT INTO tblRecords ( strEmpNum, strLastName, strProcNum, strProcRev, strProcName, lngID )
SELECT Src.strEmpNum, Src.strLastName, Src.strProcNum, Src.strProcRev, Src.strProcName, tblRecords.lngID
FROM (SELECT DISTINCT tblEmployees.strEmpNum, tblEmployees.strLastName, qryNewtblRequired.strProcNum, qryNewtblRequired.strProcRev, tblProcedures.strProcName
FROM (tblEmployees INNER JOIN tblModEmp ON tblEmployees.strEmpNum = tblModEmp.strEmpNum) INNER JOIN (qryNewtblRequired INNER JOIN tblProcedures ON (qryNewtblRequired.strProcRev = tblProcedures.strProcRev) AND (qryNewtblRequired.strProcNum = tblProcedures.strProcNum)) ON tblModEmp.strDept = qryNewtblRequired.strDept)  AS Src LEFT JOIN tblRecords ON (Src.strProcRev = tblRecords.strProcRev) AND (Src.strProcNum = tblRecords.strProcNum) AND (Src.strEmpNum = tblRecords.strEmpNum)
WHERE (((tblRecords.lngID) Is Null));

You didn't tell me which fields you didn't want to duplicate so I have ignored the names since these should be 'standard' (Not sure why you need them in tblRecords anyway - you should just look them up as and when required. It will give you problems in the future in keeping the data stable)

Also one of them is a memo field which cannot be linked anyway - you may want to consider changing this to a text

On the test data supplied, it didn't return any records for appending, but when I deleted a record from Tblrecords, the query was ready to append it again

Signing off now - if it works, please click the thanks box!

 

[ramez75]

CJ_London,

I will try what you provided. I fixed by changing the Memo field to a Text field.

I didnt want to duplicate the fields in tblRecords if strProcNum and strProcRev and strProcName in tblRecords are equal to strProcNum and strProcRev and strProcName in qryNewtblRequired but if strProcNum or strProcRev or strProcName is different then I want to append the whole row to tblRecords.

tblRecords is a temp table that is used in frmRecordsByDoc and frmRecordsByEmp and holds the info till the "dtmDate" field is filled out by the USER once done and the save button is clicked data is moved to tblRecordsArchive and tblRecords is emptied.

There are instances where "dtmDate" is not filled out for every document as certain individuals have not read them yet and in the time being a revision of the document could change so when I run the append query I want to keep the ones that has "dtmDate" blank without duplicating records.


In the SQL you provided what is table "Src"

Sorry - db was attached

Here is the working query:

INSERT INTO tblRecords ( strEmpNum, strLastName, strProcNum, strProcRev, strProcName, lngID )
SELECT Src.strEmpNum, Src.strLastName, Src.strProcNum, Src.strProcRev, Src.strProcName, tblRecords.lngID
FROM (SELECT DISTINCT tblEmployees.strEmpNum, tblEmployees.strLastName, qryNewtblRequired.strProcNum, qryNewtblRequired.strProcRev, tblProcedures.strProcName
FROM (tblEmployees INNER JOIN tblModEmp ON tblEmployees.strEmpNum = tblModEmp.strEmpNum) INNER JOIN (qryNewtblRequired INNER JOIN tblProcedures ON (qryNewtblRequired.strProcRev = tblProcedures.strProcRev) AND (qryNewtblRequired.strProcNum = tblProcedures.strProcNum)) ON tblModEmp.strDept = qryNewtblRequired.strDept)  AS Src LEFT JOIN tblRecords ON (Src.strProcRev = tblRecords.strProcRev) AND (Src.strProcNum = tblRecords.strProcNum) AND (Src.strEmpNum = tblRecords.strEmpNum)
WHERE (((tblRecords.lngID) Is Null));

You didn't tell me which fields you didn't want to duplicate so I have ignored the names since these should be 'standard' (Not sure why you need them in tblRecords anyway - you should just look them up as and when required. It will give you problems in the future in keeping the data stable)

Also one of them is a memo field which cannot be linked anyway - you may want to consider changing this to a text

On the test data supplied, it didn't return any records for appending, but when I deleted a record from Tblrecords, the query was ready to append it again

Signing off now - if it works, please click the thanks box!

 

[awse]

 

[CJ_London]

Hi,

Src is a subquery which is basically your original query - open the query in design view and you will see how it works. Suggest you read up on subqueries - they are very useful

I didnt want to duplicate the fields in tblRecords if strProcNum and strProcRev and strProcName in tblRecords are equal to strProcNum and strProcRev and strProcName in qryNewtblRequired but if strProcNum or strProcRev or strProcName is different then I want to append the whole row to tblRecords.

Open the query in design view and change the joins between Src and tblRecords to what you require.

There are instances where "dtmDate" is not filled out for every document as certain individuals have not read them yet and in the time being a revision of the document could change so when I run the append query I want to keep the ones that has "dtmDate" blank without duplicating records.

Don't understand - are you saying don't append if dtmdate is null?