Update a database daily & 2nd job to return a symbol and all of its dates and prices

  • Thread starter Thread starter Jon Macmichael
  • Start date Start date
J

Jon Macmichael

Hello

With very limited Access experience I wish to have a database that is
made from text files each named with a date eg; 040520.txt and
containing a stock symbol a associated integer, comma delimited, eg;

BHP,2045
NAB,1995
RIO,1361
CBA,1335
WMR,1254
MBL,1162
ANZ,1150
WBC,1087
TLS,1076
AMP,926
NCM,838
NCP,815
AMC,714
BSL,587
LEI,582
PRK,573
WPL,568
OXR,534
WES,522
AWC,506

With a new 'End Of Day' file each day to be updated (of about 1000
records per above), and about 1000 symbols with increasing date
history and associated date values (the integer), how would I build
the database of;
Symbol - Date - Value
ONE to MANY/MANY
?
so as to be able to return (from the database) at any one time a
Symbol and it's history of 'date-value' 's

Thanks
Jon
 
1. Create a table with the following fields:
StockSymbol (Primary Key)
StockDate (default value of Date())
StockValue (long)

2. Create a procedure to a) delete the previous day's imported table, b)
import the .txt file for the day, and c) run an append query to append the
new imported table's records into the table created in number 1 above.
 
Lynn,

Thanks for responding. Being so green with Access, before I attempt
what you have suggested; How do I go about making a 'procedure'? Can I
do it via a 'record macro' as in Excel? This I could do. It it means
writing in VB, then I've never done it before in Access, only alittle
in Excel.

Also, in your "1. Create a table..."; Should this be one for ever
symbol? I'm thinking your answer is going to be 'No'. But I just
cannot get my head around how the table would be layed out, with maybe
800 or more StockSymbols and date and value history for each?

Sorry it has taken so long to followup on your help. Up until this
morning (DownUnder) Goggle was only showing me threads till 27 May !
Looking forward to getting going on this. You'vre given me a way.

Thanks
Jon
 
Lynn

Thanks for your help.

Made a DeleteTable Sub as a procedure and that works well except it
can only run it by opening in Design View? How can it be attached to a
macro, button or the likes?

Then made a macro to import the daily update tables, via a
'Import/Export Specification' I found on this group. Managed to push
this across to the Module window, where I could use some VB to acquire
the file name from a MsgBox Input. But again how can I attach it to
something to run it easily? Just not the same as Excel.

On trying to make the append query, I can get the main table and a
daily update table on to grid and drag fields around as per the tiny
bit I learnt at a Tech college. But not much more.

I cannot see how to run it so as to get the date for all values added
to the main table from the title of the append 'from' table (which
holds the date as its title)?

The tables are linked by 'StockSymbol' in the grid which is a start I
guess. Should the fields of both tables be dragged to the grid, and as
I have the StockSymbol holding the Primary Key in the main table only,
do I need any other fields in either table indexed?

Thanks
Jon
 
Back
Top