By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,182 Members | 1,466 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,182 IT Pros & Developers. It's quick & easy.

Compare two columns and place results in new table

P: n/a
Hi everyone,

I would like to know if there is a quick query someone can help me
write for the following scenario. I think I can do this with VBA but
since this is suppose to be a temp. solution, I really do not want to
spend too much time on it.

I have two tables, 1 an old one which has work done in it with all the
necessary columns correctly populated with the data and the second one
I need which has the same columns but need to be fulfilled with the
data. So this is basically what I need to do.

Table1
A B C D E F
x x x x x x (x = data)

Table 2
A B C D E F
x x x (x = data)

What I need here is compare columns Table1.B to Table 2.B and Table1.C
to Table2.C and if they match, place the data from Colmns D, E & F
from Table1 to Table2 D, E & F columns. The catch is Columns B & C
have to match simultaniously, in other words I cannot just compare
ColumnB first and then compare ColumnC.

So if Table1.B and Table1.C = Table2.B and Table.C, then copy Columns
Table1.D, E & F to Table2.D, E & F.

Little bit of info on the data:
1. Both of these tables will be imported from an excel file (.csv).
2. Columns B & C are a combination of numbers and characters (i.e.
s4uuie).
3. Column B will always have data but C may be null in a lot of
instances (dont know if this will create issues when comparing null
values).

Please let me know if I can provide any more information to help
understand the issue here.

Thanks,

Nilay

Oct 8 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On 8 Ott, 14:27, napate...@gmail.com wrote:
Hi everyone,

I would like to know if there is a quick query someone can help me
write for the following scenario. I think I can do this with VBA but
since this is suppose to be a temp. solution, I really do not want to
spend too much time on it.

I have two tables, 1 an old one which has work done in it with all the
necessary columns correctly populated with the data and the second one
I need which has the same columns but need to be fulfilled with the
data. So this is basically what I need to do.

Table1
A B C D E F
x x x x x x (x = data)

Table 2
A B C D E F
x x x (x = data)

What I need here is compare columns Table1.B to Table 2.B and Table1.C
to Table2.C and if they match, place the data from Colmns D, E & F
from Table1 to Table2 D, E & F columns. The catch is Columns B & C
have to match simultaniously, in other words I cannot just compare
ColumnB first and then compare ColumnC.

So if Table1.B and Table1.C = Table2.B and Table.C, then copy Columns
Table1.D, E & F to Table2.D, E & F.

Little bit of info on the data:
1. Both of these tables will be imported from an excel file (.csv).
2. Columns B & C are a combination of numbers and characters (i.e.
s4uuie).
3. Column B will always have data but C may be null in a lot of
instances (dont know if this will create issues when comparing null
values).

Please let me know if I can provide any more information to help
understand the issue here.

Thanks,

Nilay
Before seeing a possible way, let's make sure to understood the
question.

Let's see assume (watch for possible google line breaks):

----------------
Field A Field B Field C Field D Field E Field F
Any A1 Any code001 any C1 hello what 23
Any A2 Any code002 12 if boat
Any A3 Any code003 any C3 bye about love
Any A4 Any code004 any C4 car
Any A5 Any code005 any C5 flag near dear
----------------

and the second table:

----------------
Field A Field B Field C Field D Field E Field F
Any A1 Any code001 any C1
Any A2
Any A3bis Any code003
Any code004 any C4
Any A5bis Any code005 any C5
----------------
would the wanted result be:

----------------
Any A1 Any code001 any C1 hello what 23
Any code004 any C4 car
Any A5bis Any code005 any C5 flag near dear
----------------

?

-P


Oct 8 '07 #2

P: n/a
On Oct 8, 11:39 am, pamela fluente <pamelaflue...@libero.itwrote:
On 8 Ott, 14:27, napate...@gmail.com wrote:


Hi everyone,
I would like to know if there is a quick query someone can help me
write for the following scenario. I think I can do this with VBA but
since this is suppose to be a temp. solution, I really do not want to
spend too much time on it.
I have two tables, 1 an old one which has work done in it with all the
necessary columns correctly populated with the data and the second one
I need which has the same columns but need to be fulfilled with the
data. So this is basically what I need to do.
Table1
A B C D E F
x x x x x x (x = data)
Table 2
A B C D E F
x x x (x = data)
What I need here is compare columns Table1.B to Table 2.B and Table1.C
to Table2.C and if they match, place the data from Colmns D, E & F
from Table1 to Table2 D, E & F columns. The catch is Columns B & C
have to match simultaniously, in other words I cannot just compare
ColumnB first and then compare ColumnC.
So if Table1.B and Table1.C = Table2.B and Table.C, then copy Columns
Table1.D, E & F to Table2.D, E & F.
Little bit of info on the data:
1. Both of these tables will be imported from an excel file (.csv).
2. Columns B & C are a combination of numbers and characters (i.e.
s4uuie).
3. Column B will always have data but C may be null in a lot of
instances (dont know if this will create issues when comparing null
values).
Please let me know if I can provide any more information to help
understand the issue here.
Thanks,
Nilay

Before seeing a possible way, let's make sure to understood the
question.

Let's see assume (watch for possible google line breaks):

----------------
Field A Field B Field C Field D Field E Field F
Any A1 Any code001 any C1 hello what 23
Any A2 Any code002 12 if boat
Any A3 Any code003 any C3 bye about love
Any A4 Any code004 any C4 car
Any A5 Any code005 any C5 flag near dear
----------------

and the second table:

----------------
Field A Field B Field C Field D Field E Field F
Any A1 Any code001 any C1
Any A2
Any A3bis Any code003
Any code004 any C4
Any A5bis Any code005 any C5
----------------

would the wanted result be:

----------------
Any A1 Any code001 any C1 hello what 23
Any code004 any C4 car
Any A5bis Any code005 any C5 flag near dear
----------------

?

-P- Hide quoted text -

- Show quoted text -
Hi pamlea,

The result is exactly what i'm looking for. Is there an sql statement
i can run which will help me do this or a short vba code that may help
me? I would rather run a sql statement if possible since this will be
a 1 time job for a few days.

Thanks,

Oct 8 '07 #3

P: n/a
On 8 Ott, 19:00, napate...@gmail.com wrote:
On Oct 8, 11:39 am, pamela fluente <pamelaflue...@libero.itwrote:


On 8 Ott, 14:27, napate...@gmail.com wrote:
Hi everyone,
I would like to know if there is a quick query someone can help me
write for the following scenario. I think I can do this with VBA but
since this is suppose to be a temp. solution, I really do not want to
spend too much time on it.
I have two tables, 1 an old one which has work done in it with all the
necessary columns correctly populated with the data and the second one
I need which has the same columns but need to be fulfilled with the
data. So this is basically what I need to do.
Table1
A B C D E F
x x x x x x (x = data)
Table 2
A B C D E F
x x x (x = data)
What I need here is compare columns Table1.B to Table 2.B and Table1.C
to Table2.C and if they match, place the data from Colmns D, E & F
from Table1 to Table2 D, E & F columns. The catch is Columns B & C
have to match simultaniously, in other words I cannot just compare
ColumnB first and then compare ColumnC.
So if Table1.B and Table1.C = Table2.B and Table.C, then copy Columns
Table1.D, E & F to Table2.D, E & F.
Little bit of info on the data:
1. Both of these tables will be imported from an excel file (.csv).
2. Columns B & C are a combination of numbers and characters (i.e.
s4uuie).
3. Column B will always have data but C may be null in a lot of
instances (dont know if this will create issues when comparing null
values).
Please let me know if I can provide any more information to help
understand the issue here.
Thanks,
Nilay
Before seeing a possible way, let's make sure to understood the
question.
Let's see assume (watch for possible google line breaks):
----------------
Field A Field B Field C Field D Field E Field F
Any A1 Any code001 any C1 hello what 23
Any A2 Any code002 12 if boat
Any A3 Any code003 any C3 bye about love
Any A4 Any code004 any C4 car
Any A5 Any code005 any C5 flag near dear
----------------
and the second table:
----------------
Field A Field B Field C Field D Field E Field F
Any A1 Any code001 any C1
Any A2
Any A3bis Any code003
Any code004 any C4
Any A5bis Any code005 any C5
----------------
would the wanted result be:
----------------
Any A1 Any code001 any C1 hello what 23
Any code004 any C4 car
Any A5bis Any code005 any C5 flag near dear
----------------
?
-P- Hide quoted text -
- Show quoted text -

Hi pamlea,

The result is exactly what i'm looking for. Is there an sql statement
i can run which will help me do this or a short vba code that may help
me? I would rather run a sql statement if possible since this will be
a 1 time job for a few days.

Thanks,- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -
To obtain the above I used this query:

SELECT
S1.[Field A] AS [Field A],
S1.[Field B] AS [Field B],
S1.[Field C] AS [Field C],
S.[Field D] AS [Field D],
S.[Field E] AS [Field E],
S.[Field F] AS [Field F]
FROM
[Sheet1$] S
INNER JOIN
[Sheet2$] S1
ON S.[Field B] = S1.[Field B]
AND S.[Field C] = S1.[Field C];
If it's not sensitive data, you can send the Excel file to me and
I will send you back the access table (for free, clearly).

[indicate field (re)mapping if not trivial]

-P
---------------------------------------------------------------------------*------------------
Providing Access Users with the world's best Reporting Solution
http://www.datatime.eu/download.aspx
Oct 8 '07 #4

P: n/a
On Oct 8, 1:42 pm, pamela fluente <pamelaflue...@libero.itwrote:
On 8 Ott, 19:00, napate...@gmail.com wrote:


On Oct 8, 11:39 am, pamela fluente <pamelaflue...@libero.itwrote:
On 8 Ott, 14:27, napate...@gmail.com wrote:
Hi everyone,
I would like to know if there is a quick query someone can help me
write for the following scenario. I think I can do this with VBA but
since this is suppose to be a temp. solution, I really do not want to
spend too much time on it.
I have two tables, 1 an old one which has work done in it with all the
necessary columns correctly populated with the data and the second one
I need which has the same columns but need to be fulfilled with the
data. So this is basically what I need to do.
Table1
A B C D E F
x x x x x x (x = data)
Table 2
A B C D E F
x x x (x = data)
What I need here is compare columns Table1.B to Table 2.B and Table1.C
to Table2.C and if they match, place the data from Colmns D, E & F
from Table1 to Table2 D, E & F columns. The catch is Columns B & C
have to match simultaniously, in other words I cannot just compare
ColumnB first and then compare ColumnC.
So if Table1.B and Table1.C = Table2.B and Table.C, then copy Columns
Table1.D, E & F to Table2.D, E & F.
Little bit of info on the data:
1. Both of these tables will be imported from an excel file (.csv).
2. Columns B & C are a combination of numbers and characters (i.e.
s4uuie).
3. Column B will always have data but C may be null in a lot of
instances (dont know if this will create issues when comparing null
values).
Please let me know if I can provide any more information to help
understand the issue here.
Thanks,
Nilay
Before seeing a possible way, let's make sure to understood the
question.
Let's see assume (watch for possible google line breaks):
----------------
Field A Field B Field C Field D Field E Field F
Any A1 Any code001 any C1 hello what 23
Any A2 Any code002 12 if boat
Any A3 Any code003 any C3 bye about love
Any A4 Any code004 any C4 car
Any A5 Any code005 any C5 flag near dear
----------------
and the second table:
----------------
Field A Field B Field C Field D Field E Field F
Any A1 Any code001 any C1
Any A2
Any A3bis Any code003
Any code004 any C4
Any A5bis Any code005 any C5
----------------
would the wanted result be:
----------------
Any A1 Any code001 any C1 hello what 23
Any code004 any C4 car
Any A5bis Any code005 any C5 flag near dear
----------------
?
-P- Hide quoted text -
- Show quoted text -
Hi pamlea,
The result is exactly what i'm looking for. Is there an sql statement
i can run which will help me do this or a short vba code that may help
me? I would rather run a sql statement if possible since this will be
a 1 time job for a few days.
Thanks,- Nascondi testo tra virgolette -
- Mostra testo tra virgolette -

To obtain the above I used this query:

SELECT
S1.[Field A] AS [Field A],
S1.[Field B] AS [Field B],
S1.[Field C] AS [Field C],
S.[Field D] AS [Field D],
S.[Field E] AS [Field E],
S.[Field F] AS [Field F]
FROM
[Sheet1$] S
INNER JOIN
[Sheet2$] S1
ON S.[Field B] = S1.[Field B]
AND S.[Field C] = S1.[Field C];

If it's not sensitive data, you can send the Excel file to me and
I will send you back the access table (for free, clearly).

[indicate field (re)mapping if not trivial]

-P
---------------------------------------------------------------------------**------------------
Providing Access Users with the world's best Reporting Solutionhttp://www..datatime.eu/download.aspx- Hide quoted text -

- Show quoted text -
Greatly appreciate your help Pamela. The data is highly sensitive so
unfortunately I would not be able to send it to you. I will work with
the query you provided and if I run into any issues, I will def. post
it up here.

Thanks again,

Nilay

Oct 8 '07 #5

P: n/a
Nilay, Please take a look at our newsgroup's FAQ at
http://www.mvps.org/access/netiquette.htm for good suggestions on effective
use of newsgroups, INCLUDING the part about "selective quoting," not just
adding short comments to a long, long, long quote. Quote just enough to
establish the context of your response. Thanks for your consideration.

Larry Linson
Micrsoft Access MVP
Oct 9 '07 #6

P: n/a
On Oct 8, 8:10 pm, "Larry Linson" <boun...@localhost.notwrote:
Nilay, Please take a look at our newsgroup's FAQ athttp://www.mvps.org/access/netiquette.htmfor good suggestions on effective
use of newsgroups, INCLUDING the part about "selective quoting," not just
adding short comments to a long, long, long quote. Quote just enough to
establish the context of your response. Thanks for your consideration.

Larry Linson
Micrsoft Access MVP
Hi Larry,

I read the article in your link. Could you explain what I need to
improve on w/ examples if possible besides the Subject?

Thanks,

Nilay

Oct 9 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.