Linking tables..

[Fherrera]

Hi, I understand you can link tables from other databases into the current one...

Is it possible to link information from another table in a different database to the table within the current database.

ie. 3 separate databases contain exact table columns except rows are for different dates (cannot join them as it exceeds max limit of access file)

I want to make another database that just links to the 3 databases already created, except all as one table instead of 3 differnet tables....

Anyone have any idears?

Thx

Frank

 

[sillysod]

import the tables then create a query?

 

[willir]

Frank

Yes, you can link multiple tables from different databases.

But you can not "append" the contents of one table to antoher table just by linking them.

However, you can use the Union SQL command to present a view that makes it appear that the data is in one table provided there is compatability between tables.

I am currious about your comment

(cannot join them as it exceeds max limit of access file)

Are you saying the number of records (you use the term rows) exceeds limits set by Access?

Or are you referring to columns where Access has a limit of 255?

If you have the former problem, you may want to consider upsizing to MS*SQL, Oracle or other backend database. "We" tend to run into other limits in Access before we start to reach the hypothetical limits.

If you have the latter problem, from experience, I suspect you may need to "normalize" your databse.

Richard

 

[Fherrera]

Thanks for the reply.

Richard, interesting ideas.

I've worked with an Oracle database before but i've never had to design my own and it's been awhile

I'm going to have to look into "normalizing" can you suggest somewhere to go?

As far as Access i'm a total newbie...the query wizards and design views annoy me as I want to do the custom SQL right away Which I just found awhile ago so I'm happy

Anyway, recordset I guess is the term not row.. (Too much excel lately) I only have 3 columns of information. It is roughly 1.6GB of raw CSV files. (50 x 30-60MB files) Which i imported into 3 different Access databases... I read 1 GB was the maximum size of an Access database for Office 97... The date format I had in csv could not be converted to date in Access on the import, so I left it as a text. I updated each date with a String(Cdate([Date])) and now I want to convert the column to a Date/Time format. But, I don't have enouph disk space/memory to complete that. So i'm going to try and split the tables down and maybe join them after

So much hassle. But as far as accessing all the tables as one.... Union is a good call. (I actually had to use it to get the csv files themselves as the historic data was not easily accesssed, 3 days just to do that)

Anyway, thanks for the help and hopefully you guys have more insights for me

Frank

 

[willir]

Frank

You obviously have some serious data to crunch. Access 2K supports up to 2 GB.

To crudely guesstimate the size of your data, and a review of Access limitaitons, you may get an idea from this post...

http://www.tek-tips.com/viewthread.cfm?SQID=850556&SPID=702&newpid=702&page=1

As far as normalization, Paul Litwin - Fundamentals of Relational Database Design. Documentation available from

JeremyNYC site (Word)

http://www.abcdataworks.com/fundamentals.htm

Rudy Limeback site (html)

http://r937.com/relational.html

Other good sites at
Microsoft AccessSolutions...

http://www.microsoft-accesssolutions.co.uk/table-of-contents.htm

Dev Ashish & Arvin Meyer...

http://www.mvps.org/access/toc.htm

I am not sure what your data project type is like, but other options may be SAS or SPSS statistical packages. I have not used these apps in years and I don't know their size limitations, but I used to handle tons of data way back when on smaller older systems.

Richard

 

[Fherrera]

Interesting. I don't have much of a budget and all this historical data will probably only be used once.. ie. to create the report with specific statistic information and possibly trend graphs for my boss.

I'll go through these links and check everything out!

Thanks Richard