Autoupdate of Imported Excel Data?

[ebadar]

Hello All,

I have a question that I'm still trying to fully formulate, since I can't exactly wrap my mind around what I need to do to fix it. For reference, my database deals primarily with mutual funds, retirement plans, and the reporting associated with them.

The database incorporates data about the mutual funds from an automatically imported Excel file into a table, which is then used in several queries and reports.

Currently, I use the ticker for the mutual funds as the primary key, since all funds SHOULD have one. Unfortunately, since several of the funds are privately managed, they don't have a ticker.

I'm wondering if it is possible for me to keep a table/query in the database that assigns these privately-managed funds a made up "ticker", and then automatically associates this ticker with the ticker-less fund when data is imported through the Excel file. I'm still kinda getting my feet wet in Access, so the only idea I've had so far is to use some sort of DLOOKUP() in a query.

Again, this question/idea only kind of makes sense in my head, so if you need clarification just let me know.

Any and all help is greatly appreciated!

Thanks,
Elliot

 

[Steve C]

What's a ticker?

 

[Bryan]

What's a ticker?

The symbol for the security . . . AAPL = Apple, AMZN = Amazon, etc

 

[Bryan]

If you're thinking that the ticker-less funds need a symbol because you're going to use that field for the primary key, I don't think I'd go in that direction.

I'd use an autonumber for the PK so that it would get populated during import. The ticker can be stored in a separate field with some rows initially having that field NULL for those records that don't have a ticker. If you still want the ticker field populated with something, I'd do it in a separate step after the import with an update query.

 

[vbaInet]

Create your own mapping table against the ticker, import your data into a staging table, join your staging table to your mapping table and insert into your main master table.

 

[ebadar]

I think I'm going to try vbaInet's idea, and if I can't figure that out I'll go the AutoNumber route.

Thanks guys!

 

[vbaInet]

Let us know how you get on.