If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
adding database to another
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. |
#2
|
|||
|
|||
On Wed, 10 Nov 2004 10:43:06 -0800, Brad_A
wrote: 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 |
#3
|
|||
|
|||
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! "Brett Collings [429338]" wrote: On Wed, 10 Nov 2004 10:43:06 -0800, Brad_A wrote: 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 |
#4
|
|||
|
|||
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 "Brad_A" wrote in message ... 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! "Brett Collings [429338]" wrote: On Wed, 10 Nov 2004 10:43:06 -0800, Brad_A wrote: 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 |
#5
|
|||
|
|||
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" wrote: 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 "Brad_A" wrote in message ... 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! "Brett Collings [429338]" wrote: On Wed, 10 Nov 2004 10:43:06 -0800, Brad_A wrote: 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 ---------------------------------------------- Be adventurous, be bold, be careful, be a star ---------------------------------------------- Brett Collings Business & Systems Analyst Management Information Systems Developer |
#6
|
|||
|
|||
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 "Brett Collings [429338]" wrote: 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" wrote: 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 "Brad_A" wrote in message ... 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! "Brett Collings [429338]" wrote: On Wed, 10 Nov 2004 10:43:06 -0800, Brad_A wrote: 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 ---------------------------------------------- Be adventurous, be bold, be careful, be a star ---------------------------------------------- Brett Collings Business & Systems Analyst Management Information Systems Developer |
#7
|
|||
|
|||
Brad, I'll answer inline with your comments and questions
On Fri, 12 Nov 2004 13:36:36 -0800, Brad_A wrote: 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 "Brett Collings [429338]" wrote: 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" wrote: 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 "Brad_A" wrote in message ... 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! "Brett Collings [429338]" wrote: On Wed, 10 Nov 2004 10:43:06 -0800, Brad_A wrote: 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 ---------------------------------------------- Be adventurous, be bold, be careful, be a star ---------------------------------------------- Brett Collings Business & Systems Analyst Management Information Systems Developer Cheers, Brett ---------------------------------------------- Be adventurous, be bold, be careful, be a star ---------------------------------------------- Brett Collings Business & Systems Analyst Management Information Systems Developer |
#8
|
|||
|
|||
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 "Brett Collings [429338]" wrote: Brad, I'll answer inline with your comments and questions On Fri, 12 Nov 2004 13:36:36 -0800, Brad_A wrote: 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 "Brett Collings [429338]" wrote: 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" wrote: 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 "Brad_A" wrote in message ... 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! "Brett Collings [429338]" wrote: On Wed, 10 Nov 2004 10:43:06 -0800, Brad_A wrote: 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 ---------------------------------------------- Be adventurous, be bold, be careful, be a star ---------------------------------------------- Brett Collings Business & Systems Analyst Management Information Systems Developer Cheers, Brett ---------------------------------------------- Be adventurous, be bold, be careful, be a star ---------------------------------------------- Brett Collings Business & Systems Analyst Management Information Systems Developer |
#9
|
|||
|
|||
"Brett Collings [429338]" wrote ...
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. -- |
#10
|
|||
|
|||
Brett,
Did I lose you? "Brad_A" wrote: 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 "Brett Collings [429338]" wrote: Brad, I'll answer inline with your comments and questions On Fri, 12 Nov 2004 13:36:36 -0800, Brad_A wrote: 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 "Brett Collings [429338]" wrote: 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" wrote: 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 "Brad_A" wrote in message ... 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! "Brett Collings [429338]" wrote: On Wed, 10 Nov 2004 10:43:06 -0800, Brad_A wrote: 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 ---------------------------------------------- Be adventurous, be bold, be careful, be a star ---------------------------------------------- Brett Collings Business & Systems Analyst Management Information Systems Developer Cheers, Brett ---------------------------------------------- Be adventurous, be bold, be careful, be a star ---------------------------------------------- Brett Collings Business & Systems Analyst Management Information Systems Developer |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
Template Wizard Problem - Database Structure | setoFairfax | General Discussion | 1 | September 17th, 2004 09:26 AM |
Adding staff photographs to my database | KK | New Users | 2 | September 3rd, 2004 07:41 AM |
Database Window Gone | DaveB | General Discussion | 2 | July 29th, 2004 12:24 AM |
Split Database with Synchronization | Kevin McBrearty | Database Design | 1 | June 28th, 2004 11:20 PM |