Multiple similar table into 1 query

[cw3k]

I'm doing a database for a friend and the challenge I have is there will be a lot of attachments going into this database, which will hit the 2gb file size limit in no time.

My solution is have each year of attachment in a separate databases and link them to the main database. The attachment are tax documents.

They table layout is all the same. It will be like this:

TaxYear, ClientID, 1040, 1040NR, 1040X, etc.....

TaxYear will be whatever year the database is designated and ClientID is the client for that particular year. The same client can shows up in multiple years.

I have thought of inserting each table in a form, but when a new year come, the form will need to be adjusted. And it will has empty field if a client skip a year or two.

I am not even sure if I am going the right direction or this is something doable.

Any help or suggestion will be greatly appreciated

Thanks,
Cw

 

[plog]

Databases generally shouldn't be used as file systems. They are for storing data, not documents.

My suggestion is to not save the documents in the database, but in a designated directory. Then in the database, you save the path of that document. That will save you tons of space and eliminate the need for multiple versions of the same database for each year you work with.

 

[cw3k]

I did think about using link, but it would be a nightmare when the data is moved or archived.

 

[Galaxiom]

I did think about using link, but it would be a nightmare when the data is moved or archived.

No it wouldn't. Only the fielname is held in the individual records. The common path to the folder is stored separately.

 

[spikepl]

Do not use hyperlink datatype for this. Just record the path in a string, but save the location of the main repository (suppository, if you're an Aussie) and for each doc just the relative path. In this way you only need to change the main path if docs are moved.

 

[cw3k]

Is there any resource I can look up on how to do it? I thought hyperlink is the only linking type.

Thanks

 

[cw3k]

Do not use hyperlink datatype for this. Just record the path in a string, but save the location of the main repository (suppository, if you're an Aussie) and for each doc just the relative path. In this way you only need to change the main path if docs are moved.

Hi spikepl,

Really need help on this. Can you point me to the right direction?