473,574 Members | 3,011 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Compare two columns and place results in new table

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
6 7712
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
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
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
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.e u/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
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
On Oct 8, 8:10 pm, "Larry Linson" <boun...@localh ost.notwrote:
Nilay, Please take a look at our newsgroup's FAQ athttp://www.mvps.org/access/netiquette.htmf or 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3972
by: Bryan | last post by:
I have a results table that is 5 columns wide. the recordset is returned with 48 items. I have no problem displaying 5 per row until I hit the last row where i get a ADODB.Field error '80020009' Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. My question is how do I close...
2
3667
by: Rachel Curran | last post by:
Please can anybody help me with the following: I have two separate excel spreadsheets that I have imported into access, each sheet holds the same fields. Both spreadsheets hold all information for employees however, they are from two different dates so by comparing both sheets I should to be able to check which records DO NOT match. From...
5
10857
by: Megan | last post by:
Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the new database. For instance, her old database has a table with Band Info in it. Her new database also has a table with Band Info in it but slightly...
1
6431
by: dave_wheels | last post by:
hi there, new to this database mularkey and needing some advice. I have two tables that I want to compare. If the results of each are the same I award a mark. Can someone give me the general SQL statement that I would need to achieve this. any help here would be great. relevent info?
7
1694
by: vivekian | last post by:
Hi , I need to place the results of two different queries in the same result table parallel to each other. So if the result of the first query is 1 12 2 34 3 45
11
35387
by: inpuarg | last post by:
I have 2 datatables. They are identical. I want to compare them by cell's content. They are all same. But dt1 == dt2 or dt1.GetHashCode() == dt2.GetHashCode() doesn 't work. There are big amount of rows in theese datatables . So i don 't want to enumerate each rows. This is not efficient and unacceptable for my current application.
5
2655
by: Edd E | last post by:
Hi, I have a database to store my analyses (Access 2002, WinXp), the basic structure is: TABLE 1 = Sample Info TABLE 2 = Analysis type 1 TABLE 3 = Analysis type 2 TABLE 4 = Analysis type 3 ....where each table stores results of a different type of analysis
0
6237
by: frostbb | last post by:
Ok, stumped one more time, I'm trying to learn how to use a DataGridView in place of the old DataGrid control. QUESTION: How do I map the columns returned from a RunTime sql query to the columns defined (defined columns collection) in a DataGridView?? i.e. sql_query.display_name = dataGridView1.dg1vc_display_name...
3
9250
by: mcolson | last post by:
I am trying to compare the last two values in the same column of a table. First of all, I have a column titled Row_Index that uses an index which starts at 1 and increments by 1. What I am trying to do is compare the values in the column 'Shift_Date' for the maximum value of Row_Index and the (maximum value - 1) of Row_Index. I've been...
0
7805
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8052
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8234
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7815
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8098
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6452
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5300
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1339
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1060
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.