Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Append Query Help (2002)

    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
    Easy Access

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Query Help (2002)

    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.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append Query Help (2002)

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

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append Query Help (2002)

    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
    Easy Access

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Query Help (2002)

    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.

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append Query Help (2002)

    In my stripped down copy I have included the
    Attached Files Attached Files
    Easy Access

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Query Help (2002)

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

  8. #8
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append Query Help (2002)

    You are correct...sorry for the mix up
    Easy Access

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Query Help (2002)

    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.
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append Query Help (2002)

    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.
    Easy Access

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append Query Help (2002)

    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?

  12. #12
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append Query Help (2002)

    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.
    Easy Access

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •