vlookup vs access

[memiles]

Table 1
Part number, Data, Data1, Data2.....
(Part Numer in this table is duplicated multiple times)

Table 2
Part Number, Data 3......
(Part Number in this table is not duplicated)

I would like to create a new table

Table 3
Part Number, Data, Data1, Data2, Data3

I can do this in excel with a vlookup however would like to do this in access

Mike

[ajalwaysus]

As long it is as you say, it should simply be this:

INSERT INTO [Table 3] ([Part Number], [Data 1], [Data 2], [Data 3])
SELECT [Table 1].[Part Number]
            , [Table 1].[Data]
            , [Table 1].[Data 1]
            , [Table 1].[Data 2]
            , [Table 2].[Data 3]
FROM [Table 1], [Table 2]
WHERE [Table 1].[Part Number] = [Table 2].[Part Number]

-AJ

[memiles]

Would this be VBA? I looks like it might work. little lost.

[ajalwaysus]

@memiles
This is just a query, you could store it as such, or you could put it in VBA, your choice. Maybe you should state how you wish to use this code, as well as if the data you have is stored in Access tables, and we can help you figure out the best place to put this query.

-AJ

[memiles]

Ok... Table 3 will be a new table based on tables 1 and 2, created from tables one and two. Tables one and two are access tables imported from excel workbooks.

When everything is done I want to send the excel workbook to who ever using db queries.

Does this make sense?

Mike

Mike. If you need I can send data so you can understand.

[ajalwaysus]

@memiles
Ok what does this mean, are you going to be the one who imports the data and then run the necessary code to get the Table3 data? Or are you going to be handing this off to someone else after you import this data. I don't think I have a problem understanding the data as you have put it, but you need to explain better the flow of what you intend on doing.

Also, if you have already created Table 1, Table 2, and Table 3, then you should just be able to run the query i provided you as is.

-AJ

[ajalwaysus]

I am getting off work, but if no one responds before I do, I will try and respond in the next hour or so. Sorry.

-AJ

[memiles]

This is tens of thousands of lines of data.

[memiles]

It will be going to the FAA so must be absolutly accurate.

Mike

[topher23]

Okay, so you're trying to take two tables that have been imported from an Excel workbook, merge them,and do... what? Port it back to Excel? The SQL aj gave you:

INSERT INTO [Table 3] ([Part Number], [Data 1], [Data 2], [Data 3]) 
SELECT [Table 1].[Part Number] 
            , [Table 1].[Data] 
            , [Table 1].[Data 1] 
            , [Table 1].[Data 2] 
            , [Table 2].[Data 3] 
FROM [Table 1], [Table 2] 
WHERE [Table 1].[Part Number] = [Table 2].[Part Number] 
 

will work if you paste it into SQL view in Query Designer and "Run" it to create the third table you were asking about. But exactly what do you plan to do with the third table? It may be completely unnecessary to go this route, depending on what you're trying to do.

[NeoPa]

I'd actually use a modified version of the SQL provided. JOINs work at a different level from linking things via the WHERE clause. While AJ's SQL would work (bar omitting the [Data] field from the output), it would probably struggle (go slowly) when dealing with large numbers of records.

INSERT INTO [Table 3] (
       [Part Number],
       [Data],
       [Data 1],
       [Data 2],
       [Data 3])
 
SELECT t1.[Part Number],
       t1.[Data],
       t1.[Data 1],
       t1.[Data 2],
       t2.[Data 3]
 
FROM   [Table 1] AS t1 INNER JOIN
       [Table 2] AS t2
  ON   t1.[Part Number]=t2.[Part Number]

[memiles]

After playing around with this at work today I think this will work very slick. Thank you all for your replies.

Mike