|
|
Lets say I have 2 text files with 2 rows of data each. The first file
contains the "Occupation", the 2nd file contains the "Salary" and the 3rd
file contains the "Hire Date". Each file contains a "Name column" which
would be the "key" of sorts for each entry.
File 1 contains 15,000 entries
File 2 contains 15,000 entries
File 3 contains 15,000 entries
What is the fastest way, through VBA code only (no database table joins,
etc) to join the 3 lists together into either an excel file or
multidimentional array that could be thrown into an excel file.
|
|
I guess I'm particularly confused as to why you're saying you don't want to
do any database table joins here?
Do you really want avoid using the great built in database engine to
accomplish your task? Why?
You also have to further explain what you mean by a multidimensional table
for your results.
For example:
[Quoted Text] > File 1 contains 15,000 entries
Is the above 15,000 unique and separate names, or do the names overlap? In
other words will you have a name repeated several times if the person has
more than one occupation?. This is a significant detail needed and it will
decide very much how we approach this problem.
I really can't see much of a practical solution here in which we avoid using
at least a table and some SQL joins.
Keep in mind that what we could perhaps come up with a hundred percent
coding "in memory" solution here, but the problem is then you'll have to
write code to take the data out of that arrary and write the data out to a
table suitable for its port to excel.
So, the first detail we need is in each of the three files is the name value
repeated several times? And, if so then when a person has several
occupations what is the resulting file structure to look like?
As a general rule, it would seem to me that if the person has three possible
occupations, then we would need two tables to store the resulting imported
data (Or else we'll have to come up with some kind of naming convention for
the additional columns).
If the name is NOT repeated more then once in any of the files, then you
could simply import all three text files and then build a sql join which
would then allow you to export the resulting table as an excell sheet (And
this would result in very little if any coding on your part).
so, if name data is to be repeated, then I suggest building one table with
unique index on the name, and import to that the one text file that has this
list of names . (for example, what happens if the second file does not have
a name that was found in the first file, can this occur also?).
For all realistic purposes, I would suggest that you use some SQL and joins
here, otherwise you're gonna have to write a lot of messy code, and I see
absolutely no reason to do that.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal[ at ]msn.com
|
|
Dalt wrote:
[Quoted Text] >Lets say I have 2 text files with 2 rows of data each. The first file
>contains the "Occupation", the 2nd file contains the "Salary" and the 3rd
>file contains the "Hire Date". Each file contains a "Name column" which
>would be the "key" of sorts for each entry.
>
>File 1 contains 15,000 entries
>File 2 contains 15,000 entries
>File 3 contains 15,000 entries
>
>What is the fastest way, through VBA code only (no database table joins,
>etc) to join the 3 lists together into either an excel file or
>multidimentional array that could be thrown into an excel file.
Assuming the names are the same in each list, use the
strengths of a database to do it with a quick SQL statement:
First, use the File - Get External Data menu item to link to
the three files as if they were tables. Then create a query
like:
SELECT f1.name, f1.Occupation, f2.Salary, f3.[Hire Date]
FROM (f1 INNER JOIN f2 ON f1.name = f2.name)
INNER JOIN f3 ON f1.name = f3.name
Then use the File - Export menu item to send the query to
Excel
--
Marsh
MVP [MS Access]
|
|
It is extremely difficult to believe that in a list of 15000 names you would
not have duplicates that actually belong to different people. In this case,
unless the records are in the exact same sequence in each of the 3 files
(don't know why you start out stating you have 2 text files), I'm not sure
how you would accomplish your goal. That is why most database developers
come up with a numeric value (surrogate key) to identify individual people,
and perpetuate that value throughout their applications, rather than using
peoples names.
--
HTH
Dale
email address is invalid
Please reply to newsgroup only.
"Dalt" wrote:
[Quoted Text] > Lets say I have 2 text files with 2 rows of data each. The first file
> contains the "Occupation", the 2nd file contains the "Salary" and the 3rd
> file contains the "Hire Date". Each file contains a "Name column" which
> would be the "key" of sorts for each entry.
>
> File 1 contains 15,000 entries
> File 2 contains 15,000 entries
> File 3 contains 15,000 entries
>
> What is the fastest way, through VBA code only (no database table joins,
> etc) to join the 3 lists together into either an excel file or
> multidimentional array that could be thrown into an excel file.
|
|
It's not that I don't want to do table joins or don't know how, i'm just
trying to learn a good method of combining lists through sheer code. In
other words, what coding principals are at work when several tables are
joined together?
For one, I guess it's much faster to combine lists when they're sorted
alphabetically so you can do a binary search for items within an array
instead of standard for loop. So I'm guessing a good start to manually
joining 3 "tables" would be to sort them first.
Again, I realize a table join solution is the best when it's available
(which is usually) but i'm thinking beyond access at this point. Forgive me,
maybe this wasn't the right place to post this.
"Albert D. Kallal" wrote:
[Quoted Text] > I guess I'm particularly confused as to why you're saying you don't want to
> do any database table joins here?
>
> Do you really want avoid using the great built in database engine to
> accomplish your task? Why?
>
> You also have to further explain what you mean by a multidimensional table
> for your results.
>
> For example:
>
> > File 1 contains 15,000 entries
>
> Is the above 15,000 unique and separate names, or do the names overlap? In
> other words will you have a name repeated several times if the person has
> more than one occupation?. This is a significant detail needed and it will
> decide very much how we approach this problem.
>
> I really can't see much of a practical solution here in which we avoid using
> at least a table and some SQL joins.
>
> Keep in mind that what we could perhaps come up with a hundred percent
> coding "in memory" solution here, but the problem is then you'll have to
> write code to take the data out of that arrary and write the data out to a
> table suitable for its port to excel.
>
> So, the first detail we need is in each of the three files is the name value
> repeated several times? And, if so then when a person has several
> occupations what is the resulting file structure to look like?
>
> As a general rule, it would seem to me that if the person has three possible
> occupations, then we would need two tables to store the resulting imported
> data (Or else we'll have to come up with some kind of naming convention for
> the additional columns).
>
> If the name is NOT repeated more then once in any of the files, then you
> could simply import all three text files and then build a sql join which
> would then allow you to export the resulting table as an excell sheet (And
> this would result in very little if any coding on your part).
>
> so, if name data is to be repeated, then I suggest building one table with
> unique index on the name, and import to that the one text file that has this
> list of names . (for example, what happens if the second file does not have
> a name that was found in the first file, can this occur also?).
>
> For all realistic purposes, I would suggest that you use some SQL and joins
> here, otherwise you're gonna have to write a lot of messy code, and I see
> absolutely no reason to do that.
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal[ at ]msn.com
>
>
>
|
|
|