check while appending data

[Ramesh]

Dear Friends

While appending data can we find out wether that record is unique ? If
similar record is pre exists can we overwrite the record ?.
I have monthly update table, everymonth set of fresh records will be
appended to table. where I want to know wether table is updated with new
records for the running month one time. If updated once then instead of
appending data it should update the data,
While checking uniqueness of data 2 fields in the table viz EmpmdID
,PaidDate -one payment to one employee in a particular month

Please guide

Ramesh Shetty

 

[Ramesh]

Dear Sir

I have to maintain a simple pay histrory record in a seperate table. We are
making payment on monthly basis. Since payment will be almost same for every
month, I have created paymaster where in master pay data is stored.
Everymonth we run a querry which works out month pay based on attendance.

Now I want to store the result of query to pay history table everymonth.
Querry will generate unique payment ID for eachemployee(combination of
EmployeeID&monthID&yeardID) like 25062008.

Structure of Querry is as follows
Payment ID (UniqueID)
PaymentDate
PayAmount

Please guide

Ramesh

 

[Ron2006]

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

 

[Ramesh]

Thanks Sir, I got the idea,

Thanks

Ramesh