Merging Data Dynamically

I have 4 Access databases, all with the same data model but different
data. I want to build a front end that brings all of the data in the
4 databases together into one.

Assuming my table of interest is called Removals, if I create linked
tables I end up with Removals1, Removals2, Removals3 and Removals4. I
could easily write a query to base a form or report upon to
concatenate all of these tables' data into one view. However, the
users take an empty database to use as a template for when a new
project is released so the view needs to be able to compensate for the
sudden addition of Removals5 and create a linked table for it.

Is this possible?

Regards

Karen

Re: Merging Data Dynamically

On Wed, 5 Dec 2007 06:14:49 -0800 (PST), karenjfrancis@hotmail.com
wrote:

This is a really bad idea. Rather you consolidate into one database,
that can handle multiple Projects.

-Tom.


>I have 4 Access databases, all with the same data model but different
>data. I want to build a front end that brings all of the data in the
>4 databases together into one.
>
>Assuming my table of interest is called Removals, if I create linked
>tables I end up with Removals1, Removals2, Removals3 and Removals4. I
>could easily write a query to base a form or report upon to
>concatenate all of these tables' data into one view. However, the
>users take an empty database to use as a template for when a new
>project is released so the view needs to be able to compensate for the
>sudden addition of Removals5 and create a linked table for it.
>
>Is this possible?
>
>Regards
>
>Karen

Re: Merging Data Dynamically

On 5 Dec, 14:35, Tom van Stiphout wrote:
> On Wed, 5 Dec 2007 06:14:49 -0800 (PST), karenjfran...@hotmail.com
> wrote:
>
> This is a really bad idea. Rather you consolidate into one database,
> that can handle multiple Projects.
>
> -Tom.
>
>
>
> >I have 4 Access databases, all with the same data model but different
> >data. I want to build a front end that brings all of the data in the
> >4 databases together into one.
>
> >Assuming my table of interest is called Removals, if I create linked
> >tables I end up with Removals1, Removals2, Removals3 and Removals4. I
> >could easily write a query to base a form or report upon to
> >concatenate all of these tables' data into one view. However, the
> >users take an empty database to use as a template for when a new
> >project is released so the view needs to be able to compensate for the
> >sudden addition of Removals5 and create a linked table for it.
>
> >Is this possible?
>
> >Regards
>
> >Karen- Hide quoted text -
>
> - Show quoted text -

Thanks for the reply. I'm used to using databases like Oracle where,
of course, capacity is not an issue. Unfortunately here I only have
Access to use and I think multiple projects will soon overstep the
boundaries of its storage capability.

Karen

Re: Merging Data Dynamically

On Wed, 5 Dec 2007 06:38:34 -0800 (PST), karenjfrancis@hotmail.com
wrote:

Access has a capacity of 2GB. Will you exceed that?
Note that before checking the size of the current databases, you
should compact them.

-Tom.


>On 5 Dec, 14:35, Tom van Stiphout wrote:
>> On Wed, 5 Dec 2007 06:14:49 -0800 (PST), karenjfran...@hotmail.com
>> wrote:
>>
>> This is a really bad idea. Rather you consolidate into one database,
>> that can handle multiple Projects.
>>
>> -Tom.
>>
>>
>>
>> >I have 4 Access databases, all with the same data model but different
>> >data. I want to build a front end that brings all of the data in the
>> >4 databases together into one.
>>
>> >Assuming my table of interest is called Removals, if I create linked
>> >tables I end up with Removals1, Removals2, Removals3 and Removals4. I
>> >could easily write a query to base a form or report upon to
>> >concatenate all of these tables' data into one view. However, the
>> >users take an empty database to use as a template for when a new
>> >project is released so the view needs to be able to compensate for the
>> >sudden addition of Removals5 and create a linked table for it.
>>
>> >Is this possible?
>>
>> >Regards
>>
>> >Karen- Hide quoted text -
>>
>> - Show quoted text -
>
>Thanks for the reply. I'm used to using databases like Oracle where,
>of course, capacity is not an issue. Unfortunately here I only have
>Access to use and I think multiple projects will soon overstep the
>boundaries of its storage capability.
>
>Karen

Re: Merging Data Dynamically

karenjfrancis@hotmail.com wrote in news:6970b4ad-b688-4648-969e-
f30f7fb0c0ea@w34g2000hsg.googlegroups.com:

> Unfortunately here I only have
> Access to use

This is unlikely to elicit a great deal of sympathy in comp.databases.ms-
access.

> and I think multiple projects will soon overstep the
> boundaries of its storage capability.

Perhaps, if you could tell us the size of these projects, we could comment
on the likelihood of that happening.

--
lyle fairfield

Re: Merging Data Dynamically

On 5 Dec, 14:42, Tom van Stiphout wrote:
> On Wed, 5 Dec 2007 06:38:34 -0800 (PST), karenjfran...@hotmail.com
> wrote:
>
> Access has a capacity of 2GB. Will you exceed that?
> Note that before checking the size of the current databases, you
> should compact them.
>
> -Tom.
>
>
>
> >On 5 Dec, 14:35, Tom van Stiphout wrote:
> >> On Wed, 5 Dec 2007 06:14:49 -0800 (PST), karenjfran...@hotmail.com
> >> wrote:
>
> >> This is a really bad idea. Rather you consolidate into one database,
> >> that can handle multiple Projects.
>
> >> -Tom.
>
> >> >I have 4 Access databases, all with the same data model but different
> >> >data. I want to build a front end that brings all of the data in the
> >> >4 databases together into one.
>
> >> >Assuming my table of interest is called Removals, if I create linked
> >> >tables I end up with Removals1, Removals2, Removals3 and Removals4. I
> >> >could easily write a query to base a form or report upon to
> >> >concatenate all of these tables' data into one view. However, the
> >> >users take an empty database to use as a template for when a new
> >> >project is released so the view needs to be able to compensate for the
> >> >sudden addition of Removals5 and create a linked table for it.
>
> >> >Is this possible?
>
> >> >Regards
>
> >> >Karen- Hide quoted text -
>
> >> - Show quoted text -
>
> >Thanks for the reply. I'm used to using databases like Oracle where,
> >of course, capacity is not an issue. Unfortunately here I only have
> >Access to use and I think multiple projects will soon overstep the
> >boundaries of its storage capability.
>
> >Karen- Hide quoted text -
>
> - Show quoted text -

They have a number of new, larger projects kicking off which I am led
to believe will generate more data than current projects. They want
something that is scaleable which is why I posted the question. I
guess I could split the database into 2, one for current rolling 12
month data and the other for historic data. I would still need to
bring them both together for overall reporting but could manage that
more easily.

Thanks for your input. If there is no way to do this sensibly I'll go
down the route of creating the two databases for historic reporting
purposes

Re: Merging Data Dynamically

Hate to disagree with an expert, but I run 4 or 5 back end DBs with the same
front end. Largely different sailing Clubs, so members, boats and moorings
and parking spaces used, but also use the same front end for friends, and
Rotary Club. Each DB has it's own menu so that irrelevent forms and reports
don't appear. All DBs have identical sets of tables and relationships, but
of course say the Rotary Club tables have no records in the boat and spaces
table. I have a form which you use to select which set of data you want,
then loop through all the tables to detach them from 1 back end database
then loop through all the tables to attach them for the new front end
database.
Works a treat.

Phil

"Tom van Stiphout" wrote in message
news:smddl31k8to7v9eqeh5rhlb5ncrfbo4ds1@4ax.com...
> On Wed, 5 Dec 2007 06:14:49 -0800 (PST), karenjfrancis@hotmail.com
> wrote:
>
> This is a really bad idea. Rather you consolidate into one database,
> that can handle multiple Projects.
>
> -Tom.
>
>
>>I have 4 Access databases, all with the same data model but different
>>data. I want to build a front end that brings all of the data in the
>>4 databases together into one.
>>
>>Assuming my table of interest is called Removals, if I create linked
>>tables I end up with Removals1, Removals2, Removals3 and Removals4. I
>>could easily write a query to base a form or report upon to
>>concatenate all of these tables' data into one view. However, the
>>users take an empty database to use as a template for when a new
>>project is released so the view needs to be able to compensate for the
>>sudden addition of Removals5 and create a linked table for it.
>>
>>Is this possible?
>>
>>Regards
>>
>>Karen

Re: Merging Data Dynamically

On Wed, 5 Dec 2007 06:50:49 -0800 (PST), karenjfrancis@hotmail.com
wrote:
>They have a number of new, larger projects kicking off which I am led
>to believe will generate more data than current projects. They want
>something that is scaleable which is why I posted the question. I
>guess I could split the database into 2, one for current rolling 12
>month data and the other for historic data. I would still need to
>bring them both together for overall reporting but could manage that
>more easily.

Build the database on a few "small" projects in Access using a split
database arraignment (Data in one file, code and all your forms,
queries, etc. in another). When the data file grows too large, upsize
the data mdb file to SQL Server Express, or real SQL Server.

BTW, there are quite a number of "project management" programs already
out there (including Microsoft Project. You may want to take a quick
look at those before starting your own.
I half-remember some one telling me that Open Office has a project
management tool as well, but I can't say as I've ever looked.

---
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

Re: Merging Data Dynamically

On Wed, 5 Dec 2007 22:39:21 -0000, "Phil Stanton"
wrote:

The way I look at it is this: if the data is REALLY separate, I would
not mind multiple back-ends. That seems to be the case in your
situation. But if you had told me your Sailing Clubs are all owned by
one company, which would sometimes want to run reports across the
whole enterprise, or send out a mailing that should not have dups (and
people can be member of several clubs), or lower all prices by 5%, you
can see where multiple back-ends would quickly become unmanageable
whereas an integrated one would be soo nice.
Also, the OP hinted at writing a union query to get the records for
Removals1, Removals2, Removals3 and Removals4, and then later come
back and add a new Removals5 to the query. That's where I say "really
bad idea".

-Tom.


>Hate to disagree with an expert, but I run 4 or 5 back end DBs with the same
>front end. Largely different sailing Clubs, so members, boats and moorings
>and parking spaces used, but also use the same front end for friends, and
>Rotary Club. Each DB has it's own menu so that irrelevent forms and reports
>don't appear. All DBs have identical sets of tables and relationships, but
>of course say the Rotary Club tables have no records in the boat and spaces
>table. I have a form which you use to select which set of data you want,
>then loop through all the tables to detach them from 1 back end database
>then loop through all the tables to attach them for the new front end
>database.
>Works a treat.
>
>Phil
>
>"Tom van Stiphout" wrote in message
>news:smddl31k8to7v9eqeh5rhlb5ncrfbo4ds1@4ax.com...
>> On Wed, 5 Dec 2007 06:14:49 -0800 (PST), karenjfrancis@hotmail.com
>> wrote:
>>
>> This is a really bad idea. Rather you consolidate into one database,
>> that can handle multiple Projects.
>>
>> -Tom.
>>
>>
>>>I have 4 Access databases, all with the same data model but different
>>>data. I want to build a front end that brings all of the data in the
>>>4 databases together into one.
>>>
>>>Assuming my table of interest is called Removals, if I create linked
>>>tables I end up with Removals1, Removals2, Removals3 and Removals4. I
>>>could easily write a query to base a form or report upon to
>>>concatenate all of these tables' data into one view. However, the
>>>users take an empty database to use as a template for when a new
>>>project is released so the view needs to be able to compensate for the
>>>sudden addition of Removals5 and create a linked table for it.
>>>
>>>Is this possible?
>>>
>>>Regards
>>>
>>>Karen
>

Re: Merging Data Dynamically

You'r right, Tom

All information for the various organisations is completely separate. The
only tables in the FE are paths to where the data is held for each Club /
Organisation. Also I have worked a bit of a crafty move by having a table
that shows or hided certain fields on forms that are not relevant to that
organisation. So for example, I show the wedding anniversary field on my
friend's DB but not on the Yacht Club DB

Phil


"Tom van Stiphout" wrote in message
news:a2lel3tna6349sicoigva295siv1jisidq@4ax.com...
> On Wed, 5 Dec 2007 22:39:21 -0000, "Phil Stanton"
> wrote:
>
> The way I look at it is this: if the data is REALLY separate, I would
> not mind multiple back-ends. That seems to be the case in your
> situation. But if you had told me your Sailing Clubs are all owned by
> one company, which would sometimes want to run reports across the
> whole enterprise, or send out a mailing that should not have dups (and
> people can be member of several clubs), or lower all prices by 5%, you
> can see where multiple back-ends would quickly become unmanageable
> whereas an integrated one would be soo nice.
> Also, the OP hinted at writing a union query to get the records for
> Removals1, Removals2, Removals3 and Removals4, and then later come
> back and add a new Removals5 to the query. That's where I say "really
> bad idea".
>
> -Tom.
>
>
>>Hate to disagree with an expert, but I run 4 or 5 back end DBs with the
>>same
>>front end. Largely different sailing Clubs, so members, boats and moorings
>>and parking spaces used, but also use the same front end for friends, and
>>Rotary Club. Each DB has it's own menu so that irrelevent forms and
>>reports
>>don't appear. All DBs have identical sets of tables and relationships, but
>>of course say the Rotary Club tables have no records in the boat and
>>spaces
>>table. I have a form which you use to select which set of data you want,
>>then loop through all the tables to detach them from 1 back end database
>>then loop through all the tables to attach them for the new front end
>>database.
>>Works a treat.
>>
>>Phil
>>
>>"Tom van Stiphout" wrote in message
>>news:smddl31k8to7v9eqeh5rhlb5ncrfbo4ds1@4ax.com...
>>> On Wed, 5 Dec 2007 06:14:49 -0800 (PST), karenjfrancis@hotmail.com
>>> wrote:
>>>
>>> This is a really bad idea. Rather you consolidate into one database,
>>> that can handle multiple Projects.
>>>
>>> -Tom.
>>>
>>>
>>>>I have 4 Access databases, all with the same data model but different
>>>>data. I want to build a front end that brings all of the data in the
>>>>4 databases together into one.
>>>>
>>>>Assuming my table of interest is called Removals, if I create linked
>>>>tables I end up with Removals1, Removals2, Removals3 and Removals4. I
>>>>could easily write a query to base a form or report upon to
>>>>concatenate all of these tables' data into one view. However, the
>>>>users take an empty database to use as a template for when a new
>>>>project is released so the view needs to be able to compensate for the
>>>>sudden addition of Removals5 and create a linked table for it.
>>>>
>>>>Is this possible?
>>>>
>>>>Regards
>>>>
>>>>Karen
>>