Re: check while appending data

Thanks Sir, I got the idea,

Thanks

Ramesh

"Ron2006" <ronnemec@xxxxxxxxxxx> wrote in message
news:b126aff0-d843-45c2-a6e0-41f76d6eaccd@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
If this states you unique key:

Querry will generate unique payment ID for eachemployee(combination
of
EmployeeID&monthID&yeardID)

Then make the following change to your "Append" Query.
In the design view. instead of haveing just the new data table there
add the table you are attempting to update and join the two via the
unique payment ID. However change the join logic to read ALL of the
new data and only those of the old data that match. (usually a left
join).

Change the query to an update query instead of an append query.

Be sure to update each of the fields with the corresponding
information.

This new query will update all that match and append any new ones.

Below is the sql for a tiny test set of tables that I created to check
it out.

UPDATE Table4 LEFT JOIN Table4Add ON Table4.Key1 = Table4Add.Key1 SET
Table4Add.Key1 = [Table4]![Key1], Table4Add.data = [Table4]![data];


Ron




.

Relevant Pages

  • Re: Membership database updates
    ... When you open any Query in Design View, click on the toolbar just next to ... black down-arrow where you can change your query to an Append or Update ... If you have any current members in your Import list, ... You mention Update Queries and Append Queries but I can't find how to ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Assign macro on After update event
    ... I don't see why you can't append the current record next scheduled date to ... it then try using an update query to update the dates in the new record. ... "Anna" wrote: ... maybe you can help me with that: all this macro is ...
    (microsoft.public.access.forms)
  • Re: duplicates query help & strategy for update queries with SetWarnings = False
    ... I have many many queries that check for orpaned data (for example, if there's a billing record with no matching custID in the customer table, I'm putting up a form with the unmatched data, and a quick way to update all unmatched to one archive type customer, etc. ... I was surprised to see duplicate entries in some of the static tables for dropdown selections, so I have to solve this one now. ... Once the user has handled all the problems, you enable the final command button at the bottom of the form, which executes an append query to add the data to the real table. ...
    (comp.databases.ms-access)
  • Re: Append Query Date Criteria Problem.
    ... Why can't I append some records? ... Choose Parameters on the Query menu. ... Access opens the paramters dialog. ... > End Sub ...
    (microsoft.public.access.queries)
  • RE: How to use Append Queries to add new records
    ... I sort of knew how the append query would work, ... > destination field name that you want to append those values to. ... > out new action queries. ...
    (microsoft.public.access.queries)