Append Query Help (2002)

[easyaccess]

I have data that I am exporting to an excel spreadsheet and the enclosed data is linked as a table in an access database.

I would like to run an append query to add new records from the spreadsheet based on the field datReferral which contains a date value. I run the append query with parameters to import only certain date ranges.

Some of the data is postdated when entered, which means I have to

[HansV]

Step 1:

Create a query based on the Access table.
Add strMemberNo and chkComplete.
Enter False in the criteria for chkComplete.
Save this query as (for example) qryIncomplete.

Step 2:

Create a query based on the Excel table and on the query you just saved.
Join them on the strMemberNo field.
Double click the join line and select the option to include ALL records from the Excel table.
Add the fields from the Excel table to the query grid.
Also add the strMemberNo from the query.
Enter Is Null in the Criteria line for the strMemberNo field from the query.
Select Query | Append query and specify the Access table as target.
Access will probably select the Append to fields automatically; check this and correct if necessary.
Important: clear the Append to line for the field from the query.
Switch to datasheet view to check that the correct records are selected for being appended.
Switch back to design view, then select Query | Run to execute the query.

[easyaccess]

As always Hans, your solutions are sterling and quicker than thought. My appreciation knows no bounds! Thank you.

[easyaccess]

This is a continuation of this thread to Hans' answer. The query works fine, but there is an additional field I would like to use to rule out unwanted records.

My previous statement should have been worded with the text in quotation marks.

The query should only allow a duplicate record to be added for the same member only when the previous record(s) with the member number being appended has been completed

[HansV]

This is too complicated to visualize without seeing the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

[easyaccess]

In my stripped down copy I have included the

[HansV]

In condition 1., don't you mean strFamilyNumber vs FAMMEM?

[easyaccess]

You are correct...sorry for the mix up

[HansV]

Try the query qryAdd in the attached version. It may not be the most efficient method, but it should do what you want, I think.

[easyaccess]

I have tried the proposed solution you included in qryAdd. It is still appending more records than required.
If if makes the solution easier, you can disregard the need to include whether chkComplete is true or false.
The primary function this query should do is add a new record when the field strFamilyNumber is not previously listed. The only exception is if the field datReferrral contains a different date.

[HansV]

I fear I no longer understand what you want. The query qryAdd satisfies the conditions you mentioned higher up in this thread (<post:=640,561>post 640,561</post:>). Could you attach a database with more sample records, and explain exactly which records should be appended and which ones shouldn't, and why?

[easyaccess]

Hans,

I stand corrected. I had add parameters and neglected to include the same parameters on the 2nd line of criteria. Thanks again for your help.