Append Query to a table (Without Duplicates)

[cmorten]

I am really knew to Access so my apologies if this is covered already.

Every month I get a download in Excel and I save that download in the same location every time and overwrite the existing Excel file. That file is a linked table in Access which I created a query from. That query has 15 fields and I take that query and run an append query that appends that data to a Master table called STATE RECON TABLE. My Master table has 16 fields and 15 of which are the exact same fields in the query and the last field is a reconciling field. So this Master table that I have a use to reconcile against another set of data and each time I reconcile a record in the Master Table I put the word RECON in that 16th field. Next month when I get a new download I will go through this same process, however when I run the append query this time I would like the append query to only bring in new records. I have no specific field and I am not entirely sure how an append query works but the append query would have to identify duplicates based on the entire record and not just one key field. not really sure how I get from point A to point B. below is my append query

INSERT INTO [STATE RECON TABLE] ( [Index], Fund, [Fund Code], Orgn, [Unit Code], Acct, GL_Type, [TRANS DATE], TYPE, DOCUMENT, DESCRIPTION, [DOC REF], FLD, AMOUNT, Division )
SELECT ORIG_STATE_DL_QRY.Index, ORIG_STATE_DL_QRY.Fund, ORIG_STATE_DL_QRY.[Fund Code], ORIG_STATE_DL_QRY.Orgn, ORIG_STATE_DL_QRY.Unit_Code, ORIG_STATE_DL_QRY.Acct, ORIG_STATE_DL_QRY.GL_Type, ORIG_STATE_DL_QRY.[TRANS DATE], ORIG_STATE_DL_QRY.TYPE, ORIG_STATE_DL_QRY.DOCUMENT, ORIG_STATE_DL_QRY.DESCRIPTION, ORIG_STATE_DL_QRY.[DOC REF], ORIG_STATE_DL_QRY.FLD, ORIG_STATE_DL_QRY.AMOUNT, ORIG_STATE_DL_QRY.Division
FROM ORIG_STATE_DL_QRY;

[JoeM]

What you want to do is create an Unmatched Query, that is a query that identifies JUST the new records. You do this by comparing the new records with the records already in the final table.
Access has an "Unmatched Query Wizard" that you can use to get you started. The only caveat is that this only allows you to join on one field. Do not worry about that for now, just go through the Wizard and create the query to work/join just on one field.

After you complete this step, open up the Query you just created in Design View. You should see two objects, your data table and your final table, being joined on one field. Note how the join line has an arrow on it, pointing from one table to another. This is known as a Left Outer Join. What you want to do is create a whole bunch more Left Outer Joins between the other fields you want to match on.

You can do this by clicking on a field in one table, and dragging-and-dropping over to the matching field in the other table. This will create the join. Note however that there is no arrow on this line.
Double-click on that line and it will give you three options. Choose the appropriate one to create the arrow in the correct direction (if you choose the wrong one, you will see how the arrow goes in the opposite direction as your original join field - just click on the join line again and choose the other option).

When you are finished, you should have your 15 arrows, all pointing in the same direction. If you view your output, it should just show you the new records you are after.
Now, change this query type from a "SELECT" query to an "APPEND" query, and then run.

The good news is, if you use the same tables every time, you will not need to do these steps every time. Once you have set this up, you can re-use it simply by clicking on the Append Query to run each time.

[cmorten]

So I think I understood everything that you said I went into Query Wizard I chose Find Unmatched Query Wizard in the first step I chose my Query Table "ORIG_STATE_DL_QRY" clicked next chose the Table I want the Data to ultimately Append to "STATE RECON TABLE" next step, it asks what piece of information is in both tables and it gives me a list of fields in both tables. Now I am not sure if it matters but my "STATE RECON TABLE" is currently just a blank table with identical headings aside from that 16th field. When I choose what information is in both tables is this looking for unique identical records in one field that exists in each of the tables or is it just looking at two like fields? I only ask because in each field regardless of what field it is there is a lot of recurring data in each field for instance the Amount field could have 100 records that all have $5.00 in each record under Amount. but each table does have an Amount field but the "STATE RECON TABLE" is blank right now.

So I went ahead and chose the Amount field and clicked next and then it asked what fields I want to see in the query results and I selected all of the fields and then clicked next. It then asks what I would like to name my query I named it and hit finish. I then went into design view and it had an arrow going from the query to the table that I want to append to like you said. I followed your instructions and added arrows to all the fields except the 16th field and then changed the query to an append query after I changed the Query to an Append and it appended the records to that table which was great but I then ran it again with the assumption that it would append 0 records because I hadn't added any new records and it tried to append all the records again.

[JoeM]

I think things might make a lot more sense if you could post some data samples, i.e. show some example data in your existing table, show some of your new data, and explain which records should be added and which shouldn't.

[aytee111]

Another option is to come at it from the opposite direction. Find those that match and then do the unmatched query.

1 - make the Excel file a local table instead of linked - this method needs the import file to have an autonumber ID
2 - make a matching query - match on all the fields - need only the ID of the records that match
3 - make an unmatched query - all the ID's in the local table that don't exist in the query - this can be your append query

[cmorten]

APPEND QUERY1.zip

If I understand correctly when I do an append query to add only new records there has to be a unique identification field that the append query can key in on. All my fields are not unique, meaning they can all have duplicate data in each field. What makes my data set unique is one entire record as a whole. I was trying to look for other paths and kept seeing the words DISTINCT ROW used. Any ways I attached some sample data.

[JoeM]

OK. I won't be able to look at it until I get home tonight (as I cannot download files off the Web from work).

[cmorten]

No Worries. I really appreciate the help.

[JoeM]

Sorry, just getting a chance to look at this now.

I see the problem. Your INDEX and DOC REF fields are blank. Don't include any blank fields in your joins.
I tested it out, and once I removed those two joins, the Append Query worked.

[cmorten]

Sorry, just getting a chance to look at this now.

I see the problem. Your INDEX and DOC REF fields are blank. Don't include any blank fields in your joins.
I tested it out, and once I removed those two joins, the Append Query worked.

What if those fields sometimes contain data?

[JoeM]

Only you know the data and what should happen.
So, you need to determine if it is vital to have joins on those fields, or if it will work without it.
If you need joins on the those fields, you will need to replace the null values with some default value.

[cmorten]

Gotcha! So if I understand right, the fields cannot have any null values at all?

I ran thru the process with another download that is similar but this has no blanks in any field and it worked so my guess is that is the case. I will give it a shot and see if it works. Thanks again for all your help!!!

[JoeM]

Gotcha! So if I understand right, the fields cannot have any null values at all?

If you plan to join on them, then yes.