How best to do this...

B

Bonnie

Hello there. Using A02 on XP. Not a programmer but
learning to use event procedures and loving it. Need to
accomplish something and not sure which path to take.

I am importing a number of tables that contain Funds and
their results for various time periods. I have 3 fund
names coming in duplicated and need to distinguish them
from each other. They do have distintive Effective Dates.

[FundName] [Effec] [FundName] should read:
Growth 05/01/1998 Eagle Growth
Growth 03/31/1999 Fidelity Growth
GrthInc 06/01/2000 Janus Growth Income
GrthInc 10/01/1999 PIMCO Growth Income
TotalRet 06/01/2002 Janus Total Return
TotalRet 10/01/1997 MFS Total Return

Trying to plan how to update those. Don't want 30, 60, or
90 queries. What would be the most efficient, automatic
method to use in effecting these updates? I have 10 "Find
Duplicates" queries (1 per table). Must I layer 3 queries
per table? (1 to find dupes, 1 to create a 'switch' field
and 1 to update the switch results.)

Would really appreciate a nudge in the correct direction.
Thanks in advance for any help or advice! Luv U Guys!
 
G

George Nicholson

I would create a table that contains your "raw" Names/EffDates and your
"real" names (or maybe just add 2 fields to an existing Funds table?).
Names/EffDates combined should have a unique index.

Import the data, update the names (using that table) and append.

One reason for the update as a separate step is that it would allow you to
validate the imported data (i.e., check for new or renamed funds that aren't
in your table before you append).
 
D

Dirk Goldgar

Bonnie said:
Hello there. Using A02 on XP. Not a programmer but
learning to use event procedures and loving it. Need to
accomplish something and not sure which path to take.

I am importing a number of tables that contain Funds and
their results for various time periods. I have 3 fund
names coming in duplicated and need to distinguish them
from each other. They do have distintive Effective Dates.

[FundName] [Effec] [FundName] should read:
Growth 05/01/1998 Eagle Growth
Growth 03/31/1999 Fidelity Growth
GrthInc 06/01/2000 Janus Growth Income
GrthInc 10/01/1999 PIMCO Growth Income
TotalRet 06/01/2002 Janus Total Return
TotalRet 10/01/1997 MFS Total Return

Trying to plan how to update those. Don't want 30, 60, or
90 queries. What would be the most efficient, automatic
method to use in effecting these updates? I have 10 "Find
Duplicates" queries (1 per table). Must I layer 3 queries
per table? (1 to find dupes, 1 to create a 'switch' field
and 1 to update the switch results.)

Would really appreciate a nudge in the correct direction.
Thanks in advance for any help or advice! Luv U Guys!
Click to expand...

You need to create a table, FundNameTranslation, for the purpose of
identifying what new name should go with each combination of (imported)
FundName and Effec. The table might look like this:

FundNameTranslation
FundName (text -- fund name as imported)
Effec (date/time -- effective date)
TranslatedName (text -- the fund name you want to translate to)

Now you can write an update query to update any imported table like
this:

UPDATE
ImportedTable
INNER JOIN
FundNameTranslation
ON ImportedTable.FundName = FundNameTranslation.FundName
AND ImportedTable.Effec = FundNameTranslation.Effec
SET ImportedTable.FundName = FundNameTranslation.TranslatedName;
 
B

Bonnie

Dirk, thank you VERY much. When I first read your reply, I
got nervous but tried it out. Took a few tries for correct
names but it works! Now I can create update queries that
can be run in the background. Thanks again!
-----Original Message-----
Hello there. Using A02 on XP. Not a programmer but
learning to use event procedures and loving it. Need to
accomplish something and not sure which path to take.

I am importing a number of tables that contain Funds and
their results for various time periods. I have 3 fund
names coming in duplicated and need to distinguish them
from each other. They do have distintive Effective Dates.

[FundName] [Effec] [FundName] should read:
Growth 05/01/1998 Eagle Growth
Growth 03/31/1999 Fidelity Growth
GrthInc 06/01/2000 Janus Growth Income
GrthInc 10/01/1999 PIMCO Growth Income
TotalRet 06/01/2002 Janus Total Return
TotalRet 10/01/1997 MFS Total Return

Trying to plan how to update those. Don't want 30, 60, or
90 queries. What would be the most efficient, automatic
method to use in effecting these updates? I have 10 "Find
Duplicates" queries (1 per table). Must I layer 3 queries
per table? (1 to find dupes, 1 to create a 'switch' field
and 1 to update the switch results.)

Would really appreciate a nudge in the correct direction.
Thanks in advance for any help or advice! Luv U Guys!
Click to expand...

You need to create a table, FundNameTranslation, for the purpose of
identifying what new name should go with each combination of (imported)
FundName and Effec. The table might look like this:

FundNameTranslation
FundName (text -- fund name as imported)
Effec (date/time -- effective date)
TranslatedName (text -- the fund name you want to translate to)

Now you can write an update query to update any imported table like
this:

UPDATE
ImportedTable
INNER JOIN
FundNameTranslation
ON ImportedTable.FundName = FundNameTranslation.FundName
AND ImportedTable.Effec = FundNameTranslation.Effec
SET ImportedTable.FundName = FundNameTranslation.TranslatedName;

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Top