Add Field Query

[yamy]

How can I best process this task list in Access (with Excel Help I suspect)

Data Source 1 = text file (4MBzip and growing daily)
Data Source 2 = excel spreadsheet that duplicates many fields from DS1 but adds others that I need.

from DS1, I extract about one thrid of the records
to which I must add four additional fields to accomodate DS2 input.
The data in DS1 is always update query.
The data in DS2 is almost always an append query

For the reporting and query needs, I will automate the process through the switchboard and macros and using linked files. All of which seem to work ok with sample data that i plugged in to test design.

Now the import process has me stuck and I could sure use your help.

appreciate all comments - have, of course, hot project on desk, desired yesterday, promised nothing but try for tomorrow
thanks
yamy

 

[Darrylles]

Hi yam,

I could tell you, but your instructions are too vague to make this a repeatable process (as you seem to want).

e.g. The data in DS2 is ALMOST always an append query.

Also, you have said that you are stuck on the import process, but given no details as to where you are 'stuck'.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[yamy]

so sorry to be vague - and now the topic title isn't very accurate -

I made some progress since that post and i will try to better explain my current dilemma
-
daily I get a huge text file that I "Get External Data/Link Tables" to, overwriting the previous day's link as it is an update. (DS1)

I first query this file to extract the data I am concerned with.

I then export the query results to an excel file where I add additional fields.
At this stage I can also add addtional records if desired. (DS2)

going back to access I link to the modified file.

then I have the switchboard manager control access to reports and data entry forms.

HERE IS THE PROBLEM

the process works fine with test data of 50+ records.
the real live daily file contains 250,000 records and the part I extract is about 100,000 of those records.

Excel limits its records to approx. 65,000. So I am stuck at the "export the extract query to excel" and haven't even gotten to the "add additional fields" and "New Link To modified table".

Not sure which direction to turn in from here....smaller extracts? but then what? multiple databases?

all comments welcome

thanks
amy

 

[Frostrated]

Is it essential that you export the query results into Excel?

Why don't you export to another table within the same databse (use a make-table query) then add your fields and additional records to the new table

 

[yamy]

YEs. thank you!