create one table using multiple linked tables

[klkropf]

I have multiple linked tables in my db from multiple db's. I would like all
of those linked tables in one 'master' table. The linked tables all have the
exact same structure. Right now I am manually creating one 'master' table
with all the data, but anytime the data in the linked table is updated I have
to re-create the 'master' table.

 

[Jeff Boyce]

"... all have the exact same structure ..."

That doesn't sound like a relational database, that sounds like a
spreadsheet-based 'application'.

If you'll describe a bit more about your underlying data (remember, in a
relational database, it all starts with the data), folks here may be able to
offer alternatives.

Regards

Jeff Boyce
Microsoft Access MVP

 

[klkropf]

I have 10 databases that store date for the last 10 years. For example every
db has a table called tblFY(place year here)_Hires (for 2009 it was
tblFY09Hires). I have another db that links to the 10 db's Hire tables and
have created a master Hire table with all years. If the data changes for
FY09 then my master Hire table is not updated. I am wondering if there is a
way to automatically create one 'master' table from all the linked tables?

 

[John W. Vinson]

I have 10 databases that store date for the last 10 years. For example every
db has a table called tblFY(place year here)_Hires (for 2009 it was
tblFY09Hires). I have another db that links to the 10 db's Hire tables and
have created a master Hire table with all years. If the data changes for
FY09 then my master Hire table is not updated. I am wondering if there is a
way to automatically create one 'master' table from all the linked tables?

Sorry, but that design is SIMPLY WRONG.

Unless these databases are pushing the 2GByte limit, you would do *much*
better to have one single Hires table, with a date field indicating the date
of hire. It would be trivially easy to create a query selecting records for a
particular fiscal year.

As you have it, you would need to have a frontend database linked to all ten
(or eleven, next year... or twelve...) backends, with a UNION query stringing
the data together. This will be *slow*. It's either that, or recreate your
master table every time data changes, because - of course - the master table
*is a table of stored data*, and it will not and cannot automagically update
itself when data is changed in some OTHER database.

 

[Jeff Boyce]

I'm with John. Your design is about all you could do with a spreadsheet ...
but you're posting in a newsgroup dedicated to using MS Access, a relational
database. If you feed it 'sheet data, you and Access will both have to work
overtime to come up with work-arounds.

Pay now (put the data in a well-normalized structure) or pay later
(work-arounds, maintenance each year to update your Master, ...).

Regards

Jeff Boyce
Microsoft Access MVP

 

[klkropf]

The union query worked, and the query ran pretty fast. Thank you, I had
never used a union query before.

Each database is close to the 2GByte, that is why I had to set it up this
way. I was just giving a simple example using the hires table.

 

[Armen Stein]

The union query worked, and the query ran pretty fast. Thank you, I had
never used a union query before.

Each database is close to the 2GByte, that is why I had to set it up this
way. I was just giving a simple example using the hires table.

2GB is huge for this kind of data. Do you really have that much data?
Are you storing images or other documents inside the database? Do
you compact and repair? Are your databases properly split?

If you really do have that much data, then it deserves to be stored in
SQL Server, with a proper table structure that doesn't require a new
table to be created each year.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com