adding database to another

[Brad_A]

I have 7 different databases with the same exact fields from different
facilities. What is the best way to create a masterdatabase of all of them?
Right now it is set up so each facility has their own form that feeds into
their specific database.

If I need to do a special query, please define the code (logic) that must be
entered.

 

[Brett Collings [429338]]

I have 7 different databases with the same exact fields from different
facilities. What is the best way to create a masterdatabase of all of them?
Right now it is set up so each facility has their own form that feeds into
their specific database.

If I need to do a special query, please define the code (logic) that must be
entered.

Brad, all of your databases should be in the same table with just a
single field which identifies the different facilities. You would add
a tblFacilities table which just has the list of the facilities for
the users to select via a combo box on a form.

This is Best Practice data design. It allows you to cross query
between facilities, you can run reports that compare facilities and
you can group facilities on reports.

It also simplifies your forms down to one of each type. You can write
a little bit of code (with our help of course) that asks the person
opening the form which facility they want. When the form opens, the
only information they get to work with is for that facility.


Cheers,
Brett

 

[Brad_A]

Brett,

The problem with it being one database is that information from one facility
would be available to another. That is why I was trying to have a table that
each can access and update, but to send the information to that "one
database" that you were referring to so that the charts can be run.

I am just learning coding, VB.NET, so I would need help with coding to set
up a database like this.

Thanks!

 

[Lynn Trapp]

Brad,
If you follow Bret's suggestion and, also, you only give people access to
the data via forms that have queries as their recordsource, then you can
limit what people see to the specific facility they are in.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

 

[Brett Collings [429338]]

Brad, Access is a lot simpler in many ways than VB.Net but the
principles are similar so some of what you do in Access will look very
familiar.

What we're talking about here is not very complex and it is really the
basis of any robust database design.
- All Forms and Reports should be based on Queries (not an absolute,
but a good habit to get into)
- All similar data should be together in one table (for exactly the
reason you have discovered - you can't analyse and report on it
otherwise)
- Use lots of little relational tables (like tblFacilityName) to
provide you with the means to organise and filter your data
- Maybe in this case, if you absolutely don't want people to see other
facility data you could use a Form to filter the data and only send
out a FrontEnd to each facility with "their" form in it

The code that opnes your form would look like this

DoCmd.OpenForm "frmFacilityInfo", , , "[FacilityID]=" & Me![FacilityID
(watch the word wrap, should all be one line)

Have fun
Brett

Brad,
If you follow Bret's suggestion and, also, you only give people access to
the data via forms that have queries as their recordsource, then you can
limit what people see to the specific facility they are in.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Cheers,
Brett

 

[Brad_A]

I still do not follow. This is my first database to develop so I am trying
to understand what you are telling me.

Right now, I have 7 relational tables. For ease, use Facility1 through
Facility7.

I have a relational database with each of the 7 Facility names called
Locations.

Then I have a main database called Main_DB.

So, I have the forms send the information to the Main_DB. I am unclear on
how to use the relational table to send each facility data into their own
database. Why would a form come from a query? I am sending them the form to
put the information into the database in an easy manner, not to put it in a
query.

What are the three commas in the code? Can you type the actual code using
the facility names above?

Thanks a lot for your help. Again, I am new to VB.Net, just now taking a
class in that. I am not sure I fully understand your design, but i figure it
shouldn't be hard.

Regards,
Brad

Brad, Access is a lot simpler in many ways than VB.Net but the
principles are similar so some of what you do in Access will look very
familiar.

What we're talking about here is not very complex and it is really the
basis of any robust database design.
- All Forms and Reports should be based on Queries (not an absolute,
but a good habit to get into)
- All similar data should be together in one table (for exactly the
reason you have discovered - you can't analyse and report on it
otherwise)
- Use lots of little relational tables (like tblFacilityName) to
provide you with the means to organise and filter your data
- Maybe in this case, if you absolutely don't want people to see other
facility data you could use a Form to filter the data and only send
out a FrontEnd to each facility with "their" form in it

The code that opnes your form would look like this

DoCmd.OpenForm "frmFacilityInfo", , , "[FacilityID]=" & Me![FacilityID
(watch the word wrap, should all be one line)

Have fun
Brett

Brad,
If you follow Bret's suggestion and, also, you only give people access to
the data via forms that have queries as their recordsource, then you can
limit what people see to the specific facility they are in.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Cheers,
Brett

 

[Brett Collings [429338]]

Brad, I'll answer inline with your comments and questions

Right now, I have 7 relational tables. For ease, use Facility1 through
Facility7.

Ok, these are the ones we are strongly suggesting you forget about, it
may become clear why and how a little later.

I have a relational database with each of the 7 Facility names called
Locations.

Excellent! Exactly right.

Then I have a main database called Main_DB.

OK, now we might need some more info on that. I'm going to need you
to confirm or otherwise each of the following.
- Main_DB has exactly the same fields and information in it as the 7
facility tables?
- It is Main_DB which receives ALL the information by the users input?
If not, what information is input where by whom

So, I have the forms send the information to the Main_DB.

More questions for answer
- How many forms?
- What do they do?
- What are their names (for the code you wanted below)

I am unclear on how to use the relational table to send each facility data into their own
database.

You shouldn't as far as we can see. Don't close us out right now,
keep an open mind, this is leading somewhere I think you will like ...

Why would a form come from a query? I am sending them the form to
put the information into the database in an easy manner, not to put it in a
query.

Ahhh, yes. You've been caught by a trap set for and fallen into by
many new developers. Queries are a mystery, they don't have them in
Excel so they don't make sense. Queries are great! Queries are the
power engine of any database, huge or small. You can't develop
without Queries.

A Query is just a gathering of fields from one or more tables into one
place. Data viewed through and written to a Query is just the same as
data written directly to a table. You can't filter a table, you need
a query to do that. You can't dynamically group a table, you need a
query to do that. You can't do calculations in a table, a Query can.
In fact 99% of the work in a database is done using a query of one
type or another and that's how we can achieve what you want to do
without 7 Facility tables.

What are the three commas in the code? Can you type the actual code using
the facility names above?

The commas are for parameters in the OpenForm command that we didn't
use. After the FormName, we only used the one called the
"WhereCondition" which filtered all the records on the form to those
equal to the LocationID we set. Look up "OpenForm Method" in Help,
all of the parameters are explained there in great detail

Thanks a lot for your help.

Well I haven't helped at all yet. All I have done is told you what
you *shouldn't* do. The good bit comes when I get the answers to the
above questions. I and the others will then give you the code you
need to do all that you want without the 7 facility tables.

Brett
Business & Systems Analyst
Management Information Systems Developer

Again, I am new to VB.Net, just now taking a
class in that. I am not sure I fully understand your design, but i figure it
shouldn't be hard.

Regards,
Brad

Brad, Access is a lot simpler in many ways than VB.Net but the
principles are similar so some of what you do in Access will look very
familiar.

What we're talking about here is not very complex and it is really the
basis of any robust database design.
- All Forms and Reports should be based on Queries (not an absolute,
but a good habit to get into)
- All similar data should be together in one table (for exactly the
reason you have discovered - you can't analyse and report on it
otherwise)
- Use lots of little relational tables (like tblFacilityName) to
provide you with the means to organise and filter your data
- Maybe in this case, if you absolutely don't want people to see other
facility data you could use a Form to filter the data and only send
out a FrontEnd to each facility with "their" form in it

The code that opnes your form would look like this

DoCmd.OpenForm "frmFacilityInfo", , , "[FacilityID]=" & Me![FacilityID
(watch the word wrap, should all be one line)

Have fun
Brett

Brad,
If you follow Bret's suggestion and, also, you only give people access to
the data via forms that have queries as their recordsource, then you can
limit what people see to the specific facility they are in.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Brett,

The problem with it being one database is that information from one
facility
would be available to another. That is why I was trying to have a table
that
each can access and update, but to send the information to that "one
database" that you were referring to so that the charts can be run.

I am just learning coding, VB.NET, so I would need help with coding to set
up a database like this.

Thanks!

:

On Wed, 10 Nov 2004 10:43:06 -0800, Brad_A

I have 7 different databases with the same exact fields from different
facilities. What is the best way to create a masterdatabase of all of
them?
Right now it is set up so each facility has their own form that feeds
into
their specific database.

If I need to do a special query, please define the code (logic) that
must be
entered.


Brad, all of your databases should be in the same table with just a
single field which identifies the different facilities. You would add
a tblFacilities table which just has the list of the facilities for
the users to select via a combo box on a form.

This is Best Practice data design. It allows you to cross query
between facilities, you can run reports that compare facilities and
you can group facilities on reports.

It also simplifies your forms down to one of each type. You can write
a little bit of code (with our help of course) that asks the person
opening the form which facility they want. When the form opens, the
only information they get to work with is for that facility.


Cheers,
Brett

Cheers,
Brett

Cheers,
Brett

 

[Brad_A]

Brett,

Yes, the Main_DB has the same information as each table that I had
originally set up for each facility.

Right now, I have the same number of forms as facilities, with each
dedicated to go to the seperate database. The forms are simple (facilities
will otherwise reject it), and it is enters the same fields as in the
databases.

For simplicity and security use, please use the names Facility1, Facility2,
etc. and I will change the names. My entry forms are named Facility1 Entry
Form right now. Thank you very much for the help!

Regards,
Brad

Brad, I'll answer inline with your comments and questions

Right now, I have 7 relational tables. For ease, use Facility1 through
Facility7.

Ok, these are the ones we are strongly suggesting you forget about, it
may become clear why and how a little later.

I have a relational database with each of the 7 Facility names called
Locations.

Excellent! Exactly right.

Then I have a main database called Main_DB.

OK, now we might need some more info on that. I'm going to need you
to confirm or otherwise each of the following.
- Main_DB has exactly the same fields and information in it as the 7
facility tables?
- It is Main_DB which receives ALL the information by the users input?
If not, what information is input where by whom

So, I have the forms send the information to the Main_DB.

More questions for answer
- How many forms?
- What do they do?
- What are their names (for the code you wanted below)

I am unclear on how to use the relational table to send each facility data into their own
database.

You shouldn't as far as we can see. Don't close us out right now,
keep an open mind, this is leading somewhere I think you will like ...

Why would a form come from a query? I am sending them the form to
put the information into the database in an easy manner, not to put it in a
query.

Ahhh, yes. You've been caught by a trap set for and fallen into by
many new developers. Queries are a mystery, they don't have them in
Excel so they don't make sense. Queries are great! Queries are the
power engine of any database, huge or small. You can't develop
without Queries.

A Query is just a gathering of fields from one or more tables into one
place. Data viewed through and written to a Query is just the same as
data written directly to a table. You can't filter a table, you need
a query to do that. You can't dynamically group a table, you need a
query to do that. You can't do calculations in a table, a Query can.
In fact 99% of the work in a database is done using a query of one
type or another and that's how we can achieve what you want to do
without 7 Facility tables.

What are the three commas in the code? Can you type the actual code using
the facility names above?

The commas are for parameters in the OpenForm command that we didn't
use. After the FormName, we only used the one called the
"WhereCondition" which filtered all the records on the form to those
equal to the LocationID we set. Look up "OpenForm Method" in Help,
all of the parameters are explained there in great detail

Thanks a lot for your help.

Well I haven't helped at all yet. All I have done is told you what
you *shouldn't* do. The good bit comes when I get the answers to the
above questions. I and the others will then give you the code you
need to do all that you want without the 7 facility tables.

Brett
Business & Systems Analyst
Management Information Systems Developer

Again, I am new to VB.Net, just now taking a
class in that. I am not sure I fully understand your design, but i figure it
shouldn't be hard.

Regards,
Brad

Brad, Access is a lot simpler in many ways than VB.Net but the
principles are similar so some of what you do in Access will look very
familiar.

What we're talking about here is not very complex and it is really the
basis of any robust database design.
- All Forms and Reports should be based on Queries (not an absolute,
but a good habit to get into)
- All similar data should be together in one table (for exactly the
reason you have discovered - you can't analyse and report on it
otherwise)
- Use lots of little relational tables (like tblFacilityName) to
provide you with the means to organise and filter your data
- Maybe in this case, if you absolutely don't want people to see other
facility data you could use a Form to filter the data and only send
out a FrontEnd to each facility with "their" form in it

The code that opnes your form would look like this

DoCmd.OpenForm "frmFacilityInfo", , , "[FacilityID]=" & Me![FacilityID
(watch the word wrap, should all be one line)

Have fun
Brett

On Fri, 12 Nov 2004 10:13:26 -0600, "Lynn Trapp"

Brad,
If you follow Bret's suggestion and, also, you only give people access to
the data via forms that have queries as their recordsource, then you can
limit what people see to the specific facility they are in.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Brett,

The problem with it being one database is that information from one
facility
would be available to another. That is why I was trying to have a table
that
each can access and update, but to send the information to that "one
database" that you were referring to so that the charts can be run.

I am just learning coding, VB.NET, so I would need help with coding to set
up a database like this.

Thanks!

:

On Wed, 10 Nov 2004 10:43:06 -0800, Brad_A

I have 7 different databases with the same exact fields from different
facilities. What is the best way to create a masterdatabase of all of
them?
Right now it is set up so each facility has their own form that feeds
into
their specific database.

If I need to do a special query, please define the code (logic) that
must be
entered.


Brad, all of your databases should be in the same table with just a
single field which identifies the different facilities. You would add
a tblFacilities table which just has the list of the facilities for
the users to select via a combo box on a form.

This is Best Practice data design. It allows you to cross query
between facilities, you can run reports that compare facilities and
you can group facilities on reports.

It also simplifies your forms down to one of each type. You can write
a little bit of code (with our help of course) that asks the person
opening the form which facility they want. When the form opens, the
only information they get to work with is for that facility.


Cheers,
Brett

----------------------------------------------
Be adventurous, be bold, be careful, be a star
----------------------------------------------
Brett Collings
Business & Systems Analyst
Management Information Systems Developer



Cheers,
Brett

Cheers,
Brett

 

[Jamie Collins]

You would add
a tblFacilities table which just has the list of the facilities

Use lots of little relational tables (like tblFacilityName) to
provide you with the means to organise and filter your data

FacilityName would presumably be an attribute of a Facility and
therefore be modelled using a column in the Facilities table.
Modelling as an entity in its own right with a FacilityNames table
would be committing the design flaw known as 'entity splitting'.

Jamie.

--

 

[Brad_A]

Brett,

Did I lose you?

Brett,

Yes, the Main_DB has the same information as each table that I had
originally set up for each facility.

Right now, I have the same number of forms as facilities, with each
dedicated to go to the seperate database. The forms are simple (facilities
will otherwise reject it), and it is enters the same fields as in the
databases.

For simplicity and security use, please use the names Facility1, Facility2,
etc. and I will change the names. My entry forms are named Facility1 Entry
Form right now. Thank you very much for the help!

Regards,
Brad

Brad, I'll answer inline with your comments and questions

Right now, I have 7 relational tables. For ease, use Facility1 through
Facility7.

Ok, these are the ones we are strongly suggesting you forget about, it
may become clear why and how a little later.

I have a relational database with each of the 7 Facility names called
Locations.

Excellent! Exactly right.

Then I have a main database called Main_DB.

OK, now we might need some more info on that. I'm going to need you
to confirm or otherwise each of the following.
- Main_DB has exactly the same fields and information in it as the 7
facility tables?
- It is Main_DB which receives ALL the information by the users input?
If not, what information is input where by whom

So, I have the forms send the information to the Main_DB.

More questions for answer
- How many forms?
- What do they do?
- What are their names (for the code you wanted below)

I am unclear on how to use the relational table to send each facility data into their own
database.

You shouldn't as far as we can see. Don't close us out right now,
keep an open mind, this is leading somewhere I think you will like ...

Why would a form come from a query? I am sending them the form to
put the information into the database in an easy manner, not to put it in a
query.

Ahhh, yes. You've been caught by a trap set for and fallen into by
many new developers. Queries are a mystery, they don't have them in
Excel so they don't make sense. Queries are great! Queries are the
power engine of any database, huge or small. You can't develop
without Queries.

A Query is just a gathering of fields from one or more tables into one
place. Data viewed through and written to a Query is just the same as
data written directly to a table. You can't filter a table, you need
a query to do that. You can't dynamically group a table, you need a
query to do that. You can't do calculations in a table, a Query can.
In fact 99% of the work in a database is done using a query of one
type or another and that's how we can achieve what you want to do
without 7 Facility tables.

What are the three commas in the code? Can you type the actual code using
the facility names above?

The commas are for parameters in the OpenForm command that we didn't
use. After the FormName, we only used the one called the
"WhereCondition" which filtered all the records on the form to those
equal to the LocationID we set. Look up "OpenForm Method" in Help,
all of the parameters are explained there in great detail

Thanks a lot for your help.

Well I haven't helped at all yet. All I have done is told you what
you *shouldn't* do. The good bit comes when I get the answers to the
above questions. I and the others will then give you the code you
need to do all that you want without the 7 facility tables.

Brett
Business & Systems Analyst
Management Information Systems Developer

Again, I am new to VB.Net, just now taking a
class in that. I am not sure I fully understand your design, but i figure it
shouldn't be hard.

Regards,
Brad

:


Brad, Access is a lot simpler in many ways than VB.Net but the
principles are similar so some of what you do in Access will look very
familiar.

What we're talking about here is not very complex and it is really the
basis of any robust database design.
- All Forms and Reports should be based on Queries (not an absolute,
but a good habit to get into)
- All similar data should be together in one table (for exactly the
reason you have discovered - you can't analyse and report on it
otherwise)
- Use lots of little relational tables (like tblFacilityName) to
provide you with the means to organise and filter your data
- Maybe in this case, if you absolutely don't want people to see other
facility data you could use a Form to filter the data and only send
out a FrontEnd to each facility with "their" form in it

The code that opnes your form would look like this

DoCmd.OpenForm "frmFacilityInfo", , , "[FacilityID]=" & Me![FacilityID
(watch the word wrap, should all be one line)

Have fun
Brett

On Fri, 12 Nov 2004 10:13:26 -0600, "Lynn Trapp"

Brad,
If you follow Bret's suggestion and, also, you only give people access to
the data via forms that have queries as their recordsource, then you can
limit what people see to the specific facility they are in.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Brett,

The problem with it being one database is that information from one
facility
would be available to another. That is why I was trying to have a table
that
each can access and update, but to send the information to that "one
database" that you were referring to so that the charts can be run.

I am just learning coding, VB.NET, so I would need help with coding to set
up a database like this.

Thanks!

:

On Wed, 10 Nov 2004 10:43:06 -0800, Brad_A

I have 7 different databases with the same exact fields from different
facilities. What is the best way to create a masterdatabase of all of
them?
Right now it is set up so each facility has their own form that feeds
into
their specific database.

If I need to do a special query, please define the code (logic) that
must be
entered.


Brad, all of your databases should be in the same table with just a
single field which identifies the different facilities. You would add
a tblFacilities table which just has the list of the facilities for
the users to select via a combo box on a form.

This is Best Practice data design. It allows you to cross query
between facilities, you can run reports that compare facilities and
you can group facilities on reports.

It also simplifies your forms down to one of each type. You can write
a little bit of code (with our help of course) that asks the person
opening the form which facility they want. When the form opens, the
only information they get to work with is for that facility.


Cheers,
Brett

----------------------------------------------
Be adventurous, be bold, be careful, be a star
----------------------------------------------
Brett Collings
Business & Systems Analyst
Management Information Systems Developer



Cheers,
Brett

Cheers,
Brett