Help! I need to undo a mistake

[Dimitris]

I will try to explain my problem in english as better as I can.
We have 2 tables with exactly the same design. One table is called July and
the other May. The table July has the data of May plus much more. "May" was
supposed to be deleted but for some reason it wasn't and a user accidently
entered more data in that table(May) instead of the one he should (July).
All of the records of "May" are also in "July" with the same ID numbers. So
I need to transfer the data from "May" to "June" for those records only. I
know I must use the ID number somehow which is the same and unique for every
record but how? Also can I transfer the data from particular columns of the
table "May"? For example I need to transfer only the data of column1 column2
and column3 of "May" to "July". Is it possible?
Can someone help me ? Thank You.
Dimitris

 

[Kevin @ 3NF]

You are going to need to use a LEFT join query between the two table to
identify records that are not in the July table. I ran a quick test on this
and used simple two column tables. Autonumber for the first column and text
for the second.

INSERT INTO July ( [text] )
SELECT May.text
FROM May LEFT JOIN July ON (May.text = July.text) AND (May.ID = July.id)
WHERE (((July.text) Is Null));

Notice that I have two fields in my join, as the autonumber started at 1 for
each record. I made the assumption that you are taking a tables contents
and appending to a new month for archiving purposes. In either event, the
above syntax should get your started. Please test thouroughly on a backup
copy only before running in production.

HTH,

 

[Jeff Boyce]

Dimitris

Having separate tables for each month is a bit unusual. How will you handle
different years, but the same months? For example, May of 2003 and May of
2004...

If the tables have "exactly the same design", you may be able to further
normalize your data structure, making the queries you are trying to run
either much easier, or perhaps not even necessary.

If you want more ideas on this, post back to the 'group with a description
of what you are storing in the tables...

Good luck

Jeff Boyce
<Access MVP>

 

[Dimitris]

Thank you Kevin.
Can you please tell me how to start. I cant seem to find my way with the
left join querie. What are the first steps?
My knowledge in Access is limited since I am new and I dont know where to
start.
Thank you
Dimitris

You are going to need to use a LEFT join query between the two table to
identify records that are not in the July table. I ran a quick test on this
and used simple two column tables. Autonumber for the first column and text
for the second.

INSERT INTO July ( [text] )
SELECT May.text
FROM May LEFT JOIN July ON (May.text = July.text) AND (May.ID = July.id)
WHERE (((July.text) Is Null));

Notice that I have two fields in my join, as the autonumber started at 1 for
each record. I made the assumption that you are taking a tables contents
and appending to a new month for archiving purposes. In either event, the
above syntax should get your started. Please test thouroughly on a backup
copy only before running in production.

HTH,

--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm

I will try to explain my problem in english as better as I can.
We have 2 tables with exactly the same design. One table is called July and
the other May. The table July has the data of May plus much more. "May" was
supposed to be deleted but for some reason it wasn't and a user accidently
entered more data in that table(May) instead of the one he should (July).
All of the records of "May" are also in "July" with the same ID numbers. So
I need to transfer the data from "May" to "June" for those records only. I
know I must use the ID number somehow which is the same and unique for every
record but how? Also can I transfer the data from particular columns of the
table "May"? For example I need to transfer only the data of column1 column2
and column3 of "May" to "July". Is it possible?
Can someone help me ? Thank You.
Dimitris

 

[Kevin @ 3NF]

Is my assumption correct that you are copying all the records into a new
table every month?

--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm

Thank you Kevin.
Can you please tell me how to start. I cant seem to find my way with the
left join querie. What are the first steps?
My knowledge in Access is limited since I am new and I dont know where to
start.
Thank you
Dimitris

You are going to need to use a LEFT join query between the two table to
identify records that are not in the July table. I ran a quick test on this
and used simple two column tables. Autonumber for the first column and text
for the second.

INSERT INTO July ( [text] )
SELECT May.text
FROM May LEFT JOIN July ON (May.text = July.text) AND (May.ID = July.id)
WHERE (((July.text) Is Null));

Notice that I have two fields in my join, as the autonumber started at 1 for
each record. I made the assumption that you are taking a tables contents
and appending to a new month for archiving purposes. In either event, the
above syntax should get your started. Please test thouroughly on a backup
copy only before running in production.

HTH,

--
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm

I will try to explain my problem in english as better as I can.
We have 2 tables with exactly the same design. One table is called

July
and

the other May. The table July has the data of May plus much more.

"May"
was

supposed to be deleted but for some reason it wasn't and a user accidently
entered more data in that table(May) instead of the one he should (July).
All of the records of "May" are also in "July" with the same ID

numbers.
So

I need to transfer the data from "May" to "June" for those records

only.

I of
the