Query from 2nd table multiple

[mrteacher]

I am the distint "pleasure" of helping my wife with a task that is way beyond me...

There are 2 separate files, from EMP_DB.XLS I need to pull the data in two columns (REF # and SPF) to two columns in PAY_DB.XLS.

In both tables I have LNAME, FNAME and SHIFT that match. Therefore I will use the following example:

Table 1 - PAY_DB:
LNAME FNAME SHIFT REF # SPF
Doe John 1 ?? ??
Boe Jane 2 ?? ??
Smith Will 2 ?? ??
Donner Bob 1 ?? ??

Table 2 - EMP_DB.XLS:
Doe John 1 R34590 2234
Doe John 2 R34591 2234
Boe Jane 1 R23456 5672
Boe Jane 2 R23457 5672
Smith Will 1 R98012 2098
Smith Will 2 R98013 2098
Donner Bob 1 R67843 9112
Donner Bob 2 R67842 9112

(Each shift and each employee has their own REF # therefore I need to have the search go through and look for match on LNAME, FNAME and then SHIFT and return the correct results.) As you can see the program needs to go through to see if person was on 1st or 2nd shift and then return the right REF # back - since each is assign different numbers.

I thought I had part of it working but my data table (EMP_DB) goes to row 295 and therefore my return stops at that point on the PAY_DB table at row 295 -- ALL subsequent rows have "VALUE" error. Here is the formula I was using:

=VLOOKUP(L295,'C:\Documents and Settings\me\Desktop\PAY_DB.xls'!KN_DB,4,'C:\Documents and Settings\me\Desktop\[PAY_DB.xls]LIVE'!$D$2:$D$295) -- "LIVE" is a table range.

I thought I could use QUERY WIZARD but that does not seem to help.

Anyone have a solution - my SQL is very very limited if someone is thinking in that direction.

I find this daunting and out of my skill level ALL HELP would be appreciated.

Steve

 

[SkipVought]

Hi,

"I thought I could use QUERY WIZARD but that does not seem to help."

Using MS Query to get data from Excel faq68-5829

1. query EMP_DB.XLS to return the two columns to sheet1

2. query PAY_DB.XLS to return the two columns to sheet2.

3. on sheet3 query new workbook to join Sheet1$ to Sheet2$ on FNAME, LNAME, & SHIFT.




Skip,
[sub]
[red][/red]
[/sub]

 

[JVFriederick]

Skip's method (as always) is the way to go for large amounts of data. If there are less than a few thousand records, AND you have complete ownership of the files, the following will work.

Build a "makeshift" index to the left of row data in TABLE 2 (EMP_DB.XLS) with the following formula
+B2&C2&D2 (starts with column B since you will need to insert a column). This will add the key values together to construct a unique index of the data. You do not need to worry about slamming everything together, because you aren't going to worry about it in the next step either!

Move the sheet with this table into PAY_DB.XLS to make it easier to handle. Name the new sheet EMP

Now add another index to left of table 1. You can write a formula (preferred) but it will be easier to follow without it for now)

Now to the right of row data in table 1, write the following formulas :

REF# column
=vlookup($A2,EMP!$A2:$D9999,5,false)

SPF column
=vlookup($A2,EMP!$A2:$D9999,6,false)

$A2 is the first record in table 1 that contains the makeshift index, and it looks for a matching record in table 2 on sheet EMP. Copy these 2 formulas down for as many records as necessary in table 1

Give this a try.

I realize that the formally educated may cringe at this method, but the simplicity to build a common index is sometimes hard to beat!

Skip's FAQ post on how to query data from another XL file is very useful, and much appreciated.

 

[mrteacher]

Thank you to both of you for this input - I will try each one and see which offers the best simplicity for my wife. Since I need to make it easy for her - I will try both.

It has been years since I have done any programming - therefore things like SQL and VB are not things I have the opportunity to jump easily back into at this time.

Nevertheless - I would welcome some other thoughts too...

The response that I have always received from Tek-Tips is the exact reason why I recommmend this site to soooo many people!!!

Again - THANK YOU!!!

 

[SkipVought]

Not only if it's LARGE but even if the amount of data is SMALL, but if it RECURRING, then you need a non-manal process.

Skip,
[sub]
[red][/red]
[/sub]

 

[JVFriederick]

Absolutely agreed.