Updateable Append Query.

  • Thread starter Thread starter doyapore
  • Start date Start date
D

doyapore

Dear All,
I am stuck with a problem which I hope can be easily solved by some of you
:)

I have two tables:
1. Temp_01
2. Cumulative_01

Both these tables have identical fields:
1. Transdate [meaning Transaction Date]
2. Code [meaning employee code]
3. Earnings [meaning the weekly wages earned]

In both these tables, the Code is unique.

My problem is:
1. I wish to append data from Temp_01 to Cumulative_01
2. I do not wish to add duplicate data. In other words, if I find that
Transdate+Code exists in Cumulative_01, I will not append the data provided,
however, that the Earnings in both the tables are identical for the given
combination of Transdate+Code.
3. I also wish to know how many records have been appended. {As I recall,
there used to be a method in FoxPro which used to log the number of records
processed through any method, which was _TALLY. Do we have something to use
such as this in MS-Access?}

Can someone please help me with the problem using Query. I shall be grateful
if you could please help me with Query Design concept instead of the actual
SQL syntax. In any case, either of the methods would help my situation :)

Thanks in advance.
 
I think that you will have to do this with two queries.
One - Append the records that don't exist and
Two - Update the records that do exist and have a different earnings amount.

Here are two UNTESTED SQL statements that MAY work for you. TEST on a COPY of
your data to see if they do what you want them to do.

INSERT Into Cumulative_01 (Transdate,Code,Earnings)
SELECT T.TransDate, T.Code, T.Earnings
FROM Temp_01 AS T LEFT JOIN Cumulative_01
ON T.TransDate = Cumulative_01.TransDate AND
T.Code = Cumulative_01.Code
WHERE Cumulative_01.TransDate is Null


You did not indicate what you wanted to do with those records that existed, but
had different earnings. Perhaps update them?

UPDATE Cumulative_01 AS C INNER JOIN Temp_01 AS T
ON C.TransDate = T.TransDate AND
C.Code = T.Code
SET C.Earnings = T.Earnings
WHERE C.Earnings <> T.Earnings Or C.Earnings Is Null
 
Actually, you can use a single query to both insert new records and update
existing ones.

Take a look at my November 2003 "Access Answer" column for Pinnacle
Publication's "Smart Access". You can download the article and its
accompanying database at
http://members.rogers.com/douglas.j.steele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



John Spencer (MVP) said:
I think that you will have to do this with two queries.
One - Append the records that don't exist and
Two - Update the records that do exist and have a different earnings amount.

Here are two UNTESTED SQL statements that MAY work for you. TEST on a COPY of
your data to see if they do what you want them to do.

INSERT Into Cumulative_01 (Transdate,Code,Earnings)
SELECT T.TransDate, T.Code, T.Earnings
FROM Temp_01 AS T LEFT JOIN Cumulative_01
ON T.TransDate = Cumulative_01.TransDate AND
T.Code = Cumulative_01.Code
WHERE Cumulative_01.TransDate is Null


You did not indicate what you wanted to do with those records that existed, but
had different earnings. Perhaps update them?

UPDATE Cumulative_01 AS C INNER JOIN Temp_01 AS T
ON C.TransDate = T.TransDate AND
C.Code = T.Code
SET C.Earnings = T.Earnings
WHERE C.Earnings <> T.Earnings Or C.Earnings Is Null

Dear All,
I am stuck with a problem which I hope can be easily solved by some of you
:)

I have two tables:
1. Temp_01
2. Cumulative_01

Both these tables have identical fields:
1. Transdate [meaning Transaction Date]
2. Code [meaning employee code]
3. Earnings [meaning the weekly wages earned]

In both these tables, the Code is unique.

My problem is:
1. I wish to append data from Temp_01 to Cumulative_01
2. I do not wish to add duplicate data. In other words, if I find that
Transdate+Code exists in Cumulative_01, I will not append the data provided,
however, that the Earnings in both the tables are identical for the given
combination of Transdate+Code.
3. I also wish to know how many records have been appended. {As I recall,
there used to be a method in FoxPro which used to log the number of records
processed through any method, which was _TALLY. Do we have something to use
such as this in MS-Access?}

Can someone please help me with the problem using Query. I shall be grateful
if you could please help me with Query Design concept instead of the actual
SQL syntax. In any case, either of the methods would help my situation :)

Thanks in advance.
Click to expand...
Click to expand...
 
Thank you for your help.
Microsoft's Northwind.MDB gave me a hint to proceed in solving this problem,
and it worked. Here is what I did (for the sake of convenience, I have
copied and pasted the SQL syntax:

UPDATE CumulativeTransaction01 RIGHT JOIN Temp01_APDaily ON
(CumulativeTransaction01.CARD_SLNO = Temp01_APDaily.CARD_SLNO) AND
(CumulativeTransaction01.transdate = Temp01_APDaily.transdate) SET
CumulativeTransaction01.transdate = Temp01_APDaily.transdate,
CumulativeTransaction01.code = Temp01_APDaily.code,
CumulativeTransaction01.payvalue = Temp01_APDaily.payvalue;

For counting the records, I used the Dcount function before and after the
operation to ascertain the exact number of records which have been added.

Thank you once again for your help.



Douglas J. Steele said:
Actually, you can use a single query to both insert new records and update
existing ones.

Take a look at my November 2003 "Access Answer" column for Pinnacle
Publication's "Smart Access". You can download the article and its
accompanying database at
http://members.rogers.com/douglas.j.steele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



John Spencer (MVP) said:
I think that you will have to do this with two queries.
One - Append the records that don't exist and
Two - Update the records that do exist and have a different earnings amount.

Here are two UNTESTED SQL statements that MAY work for you. TEST on a COPY of
your data to see if they do what you want them to do.

INSERT Into Cumulative_01 (Transdate,Code,Earnings)
SELECT T.TransDate, T.Code, T.Earnings
FROM Temp_01 AS T LEFT JOIN Cumulative_01
ON T.TransDate = Cumulative_01.TransDate AND
T.Code = Cumulative_01.Code
WHERE Cumulative_01.TransDate is Null


You did not indicate what you wanted to do with those records that existed, but
had different earnings. Perhaps update them?

UPDATE Cumulative_01 AS C INNER JOIN Temp_01 AS T
ON C.TransDate = T.TransDate AND
C.Code = T.Code
SET C.Earnings = T.Earnings
WHERE C.Earnings <> T.Earnings Or C.Earnings Is Null

Dear All,
I am stuck with a problem which I hope can be easily solved by some of you
:)

I have two tables:
1. Temp_01
2. Cumulative_01

Both these tables have identical fields:
1. Transdate [meaning Transaction Date]
2. Code [meaning employee code]
3. Earnings [meaning the weekly wages earned]

In both these tables, the Code is unique.

My problem is:
1. I wish to append data from Temp_01 to Cumulative_01
2. I do not wish to add duplicate data. In other words, if I find that
Transdate+Code exists in Cumulative_01, I will not append the data provided,
however, that the Earnings in both the tables are identical for the given
combination of Transdate+Code.
3. I also wish to know how many records have been appended. {As I recall,
there used to be a method in FoxPro which used to log the number of records
processed through any method, which was _TALLY. Do we have something
Click to expand...
Click to expand...
to
use
Click to expand...
Click to expand...
 
Back
Top