Showing/combinding multiple DBs within one 1

[wlpsyp]

Hello all. I need some help here.

I am making a access DB that I have been attempting to put together for well over a year now. Up to this point I have hit some brick walls but over came them. I am up against another here. What I am trying to do is, I have four U.S. Army installations here in Alaska. I have created a DB that tracks workorders, man hours, dollar cost etc. Problem is I have the same one four times. What I would like to do is make a front screen or Switchboard? that is a drop down menu that they can pick the proper Army post and the DB then only populates with the posts information come up and is only available to them. How can I do this?

The databases are the exact same with the exception of a few things like employee names, Range names, vehicles etc. All four installations will use the same DB but with there unique info. So I guess the bottom line is how do I take four copies of the same DB, populate it with each installations info and when they click to open the DB it brings up a front screen for them to select there installation and then it only brings up there info?

Thanks for any and all advice.

Bill

It's not a problem...It's just a Blood Pressure Monitor

 

[M8KWR]

I think I get what you are saying. You have 4 copies of the db per installation. Are these going to be remote copies (i.e not attached to a network?)

You want a drop down on a switchboard that lets a person choice what section of the database they wish to see information and add information.

## This bit is easy, with the drop down, you would then set a filter on the form that opens up next with what ever in the db sections of each of the 4 areas.

I hope I am following with what you are trying to do.

Please let me know either way so I can try and help you.

 

[wlpsyp]

Thanks for the help M8KWR,

I should try and restate. I have one DB. There are four copies of the same DB. There is one for IRO, FRA, FWA, and DTA. Right now they are seperate stand alone DBs, a copy at each installation. What I am trying to do is

1. get them centeralized onto a server into basically one DB but broken down within the DB by installations info

2. Create a switchboard or? so when anyone clicks to open the db they are prompted by a menu or drop down (I really dont care how) to pick one of the four installations (FRA, FWA, DTA or IRO) and they are presented with only the info that pertains to that installation.

3. Figure out how to secure the DB so someone from say FRA can not access any of the other three unless they have permission

Thanks,

Bill

It's not a problem...It's just a Blood Pressure Monitor

 

[RickSpr]

If you want to secure it so someone from FRA can't access DTA's info, it doesn't make sense to have them select their installation from a dropdown/switchboard. You'd have to already know which installation they're at to verify their access anyway.


Your main problem, from what I can see, will be that you want to restrict rows retrieved from the common tables according to the installation.

Presumably, when you combine the tables you will add an Installation column which you will populate. Every combined table must necessarily have this column.

Create a table in the front end with a single column, Installation, which will have the same code in it. This table will have a single row containing the Installation code for the place where the front end is located. You can either pre-fill it before you deliver the front end, or you can set it at database startup based on user id or computer name or something like that.

All other queries in your application which contain common tables must join this front end table with the common tables. By "queries" I mean not only stored queries, but SQL statements in form and report RecordSource properties, list/combo box RowSource queries, and any built in VBA code. Don't forget DLookup and other aggregate functions, either--you need to add an "Installation=" criteria to them.

Presumably, you want to get all this into one database because somebody needs access to all installations at once. For that person/group, you need a front end with the new table containing one row for each installation. That way, the joins will select all records.

Caution: Access security is not strong enough for the U.S. government's most stringent needs. Are you sure it's strong enough for this? There are password crackers easily available (at a price) by doing a web search. It wouldn't be very hard for somebody to open the front end at one site, insert rows for other installations into the front end table, and have access to everybody's data. It certainly isn't espionage-proof.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[wlpsyp]

RickSpr, Thanks for the reply

I am not overly concerned about actual security as this is not on a outside accessable doamin (intranet)

What I invision here (I am no programmer and not good with Access by the way, I am a netadmin) is really for lack of better terms is the same db, same fields,tables, forms etc rolled up into one switchboard or front screen so when you select FRA it goes to FRA and when you select DTA it goes to DTA etc. If there is a better or easier way to do this please let me know. I am willing to change anything if I have to to make it work.

Bottom line here is I need a db that four seperate installations can access and track man hours, costs etc and have it report.

Thanks for any and all help.

Bill

It's not a problem...It's just a Blood Pressure Monitor

 

[RickSpr]

What's confusing is when you say "same db". Sometimes it seems like you want to keep the four databases discrete and just create some kind of front end on them to select which one to open. Other times it seems like you want to combine the data from all four into a single .mdb file, using the same tables, fields, and forms for all locations. The latter is what my last response assumed.

Note: I'm assuming that IRO, FRA, FWA, and DTA are the identities of the four Army installations, as opposed to 4 task groups within each installation--is that right?

So, do you want to end up with a single .mdb file holding the data for all the Army installations?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[wlpsyp]

Sorry for the confusion but I think we are beginning to understand each other alittle bit.

My first thought was to do as you keep the four databases discrete and just create some kind of front end on them to select which one to open. What I would really like to do is the .mdb file but I do not know where to begin with that.

You are correct in your assumption.
FRA=Fort Richardson Alaska
FWA=Fort Wainwright Alaska
DTA=Donnelly Training Area
IRO=Installation Range Office

What this is for is for the range controls (Army firing ranges and training areas) that provide support to all military survices to fire there weapons etc. The three (FRA, FWA, DTA) are spread out over Alaska. IRO is our higher headquarters that these three range controls have to report to.

I need some way of having a db that is the same cosmetic and data apperance minus some nominal things such as range names, personnel names etc to be exactely the same for the three range controls with the IRO having to report as well but not as often. The end state if you will, will be the reporting of the information from this db either by each individual range or collectively by the IRO.

I know I am confusing this but this is a large scale task for me and I am running into a brick wall to be honest.

Thanks,

Bill



It's not a problem...It's just a Blood Pressure Monitor

 

[RickSpr]

One thing I'm not clear on. You said "collectively by the IRO". Does the IRO need to have its own data, separate from the others, or is it supposed to see the collective data for all ranges, or are both true at different times?

Let me talk about combining everything into one .mdb file. You're going to have to make a list of differences among the databases you have now. It would be best to start with differences in table names, field names, field data types and sizes, etc. Once you have that list, I can tell you what you'd need to do to combine the tables. (I'm hoping this list is small, and that most of the differences are purely cosmetic--like slightly different field names, for instance, rather than different data types.)

To begin, you would create a new, empty database.

You may have some tables that contain the same data in all the locations, and aren't updated by them. Lookup tables are an example of this. Those will be easy to consolidate--just import any one of them into the new database.

For tables that contain location-specific data, the goal is to create a table in the new database that contains all the columns, plus an additional column that identifies the location to which the data belongs. This is the column I was calling "Installation" in an earlier post. I'll have to have more information about table differences before I can get more specific about this, however.

So please let me know when you have a complete list of table differences. If there's not too much, go ahead and list them here.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[wlpsyp]

The IRO will need to do its own data as well as it does the contractings, and some things that the other three do as well. It does also need to see the collective data for all ranges including the IRO. So, both are true at different times.

Let me clarify now... I have ONE database that I have made. All I have done is just make a copy of the db and just renamed them appropriately. So this is where I came up with "four" db's. ALL fields will be the same across the board. Only difference I see is the label names and names that are populated in the drop down menus that will need to be changed. There will be a couple unique things, I am just not sure what yet.

I can send you a copy of the db if you would like to see it, it is only 2mb's.

I greatly appreciate your time and help.

Bill



It's not a problem...It's just a Blood Pressure Monitor

 

[RickSpr]

For the sake of communication, let's say you have "one database structure" and "four database files", if it should come up again.

We'll worry about the labels, drop down lists, etc. later.

While I'd like to get a copy of the database very much, I don't ever share my email address via the web, so we'll have to make do.

To begin, make a new database file--let's name it Combined.mdb--which will become the common back end on the network server. Import into this database all the tables that contain range data, from any of the 4 existing databases. In the Import dialog, specify structure only. We don't want to import any data yet.

Next, for each of the imported tables, add a column called Location. (If that name already exists, use something else, just tell me.) This will represent the installation to which each row belongs. Since IRO needs to know this information when they look at across the board data, you might want to make it a 3-character text field, so you can use "IRO", "FWA", etc. as the value. But it could be a 1-character field, or even numeric. Let me know what you decide for the size and the codes you'll use for the individual installations.

When you've got that done and have let me know the name and type of the Location column, I'll show you how to import data from all the installations to this one database. After that, we can start looking at what we have to do to make a front-end that works for everybody.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[wlpsyp]

Rick, thank you so much. You are saving my butt here bigtime.

Got it, I have one database structure with four files.

I understand about the email thing, no worry and thanks.

There is NO data what so ever in this database structure. I put fake data in just to play and see if the fields do what they need to do, but there is no "real" data in this structure at all. Also, the current four db files are the exact same just named different meaning all the drop downs etc have the exacte same info as I have not yet populated the particular file with the appropriate info for each range just so you know.

I will do this when I get to work first thing in the a.m. Here is what I am understanding to do....

1. make a new db file and call in Combined.mdb
2. Import the structure only of each of the four files into the Combined.mdb
3. For each imported table (there is approx 35 tables, some are just lookups) add a column named INSTALLATION. I will make it a three character text field. (Thats exactely what I want to use is IRO, FRA, FWA, DTA)

Bill

It's not a problem...It's just a Blood Pressure Monitor

 

[RickSpr]

One correction: Lookup tables are a special case. In general, tables that are accessed a lot but changed rarely should be kept in the front end. The tradeoff is:
- In the front end, lookups don't require network activity, but any changes require replacing the front end database.
- In the back end, you can easily update them any time, but lookups cause network data transfer.
Use your judgment about which lookup tables should go where. Any table that contains active range data should definitely go in the back end, though, so IRO can access it.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[wlpsyp]

I am willing to get rid of lookups if it will make life easier. The lookups I have are things like Firing range names, training area names, airspace names, facility names. They do not change or get deleted, but can be added to. My goal with this entire db besides getting all the data collected that needs to be collected is to make it as user friendly as possible, meaning the less fat fingering the better. The more I can use drop downs, combo boxes etc the better. There will be alot of different people entering the data so the less chance for mistakes the better. Also, not all tables have been created yet meaning this is a build as I go type db but the majority of the structure is here just some tweaking.

My concern here is that with each Installation having its own unique firing range names I will have 4 long tables which is no problem I do not think. My thought here is to have if possible one table with four coulmns named IRO, FRA, DTA, FWA and list below those columns the firing range name. My concern here is one, can they be be seen seperately meaning if FRA picks a menu it only brings in the column of FRA firing range names? or am I heading in the wrong direction here. second, the firing range names are alph-numeric and FWA may have 55 firing range names and FRA have 95 and DTA of 30 and IRO have 5. I know I probably just confused the heck out of you.

The network activitey I do not see as a large issue, but then as this DB grows the bandwith may become a issue.

just for my education here, We have a FRA.mdb, FWA.mdb, DTA.mdb, IRO.mdb and Combined.mdb. The front end would be the four files and the back end would be the Combined.mdb correct?

It's not a problem...It's just a Blood Pressure Monitor

 

[RickSpr]

I didn't make my point clear about lookup tables. I don't want you to get rid of them at all. Let's just forget about that discussion and go with putting them in the back end for now.

I'm not looking to build separate FRA.mdb, FWA.mdb, etc. I'm expecting a single front end--I'll leave it to you to name it, and tell me--that each installation will have a copy of. Combined.mdb will be on the network server.

Your range names table sounds like a good one to demonstrate what I'm planning on doing to combine everything. I'm assuming it has the same columns in each location. Please give me the table name, and its column names and data types and size. Indicate the primary key.

I'll keep monitoring this thread closely for the rest of the day. We have a lot of work to do.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[wlpsyp]

Okay, ran into another brick wall. I made the Combined.mdb and imported the tables from the FRA.mdb. When I went to import the other tables which are the exacte same it screwed it all up by over righting the previous tabled that where imported into the Combined.mdb

So right now I sit with a combined.mdb that has the tables pulled from the FRA.mdb. I have done nothing with the other .mdbs.

The range name table is called FRA_AREA, FWA_AREA and DTA_AREA. All that is in this table for each is one column. The column name is TRA_AREA for each. Under this one and only column is the listing of range names, one in each row. they row length is not the same for each of the installations as there are different amounts of ranges per installation. The data type for this column and size is Text and 25 with no primary key.

So, now I am going to add one column for all the tables in the Combined.mdb that is going to be called Installation.

In the combined.mdb right now sits 24 tables.



It's not a problem...It's just a Blood Pressure Monitor

 

[RickSpr]

I's ok that you couldn't import all of the tables, because you were only supposed to be importing the structure and the structures are all the same. If you've imported the data, delete all the rows now.

I didn't expect the range name tables to have different names. You're only going to need one table for all the installations. Let's rename it to tblRangeNames, then add an Installation column to it. Create a non-unique index on Installation.

What follows has a lot of steps. You might want to print this out so you can check them off as you go.

Next we'll populate tblRangeNames. First, link the FRA_AREA table into Combined.mdb. Then create a new query, switch to SQL View, and enter the following SQL statement:
INSERT INTO tblRangeNames (Installation, TRA_AREA)
SELECT "FRA", TRA_AREA FROM FRA_AREA
If you like, switch back to Design View to see how this can be set up in the query grid. You'll notice that it's an Append query, and that "FRA" becomes Expression1: "FRA" in the grid.

Now run the query. It will copy all the FRA range names into our tblRangeNames table, and put "FRA" in the Installation column for each one. Check it out, then delete the link to FRA_AREA.

Next, link in the FWA_AREA table, then go back to the query (in SQL View) and change "FRA" to "FWA" in 2 places. Run it again. Do the same for DTA_AREA.

When you're done, open tblRangeTable and inspect it for correctness. We have combined this one lookup table so it holds the data for all installations.

Now I'll show you how we're going to make it so that an installation only sees its own range names. This is only a demonstration of the technique, not how it's actually going to work, so don't try to think too far ahead. First, if you haven't already done so, create a table and give it one column Installation Text(3). Save it as tblInstallation and switch to Datasheet View. Type in "FRA" and then close the table.

Next, create a query and add tblInstallation and tblRangeNames to it. Notice that Access automatically adds a "join line" between the tables in the diagram. Pull the "*" from tblRangeNames to the grid, and switch to Datasheet View. You'll only see the range names for FRA. It should look exactly like FRA_AREA. Now switch the query back to Design View, change the value in tblInstallation to "FWA", save it, and switch the query to Datasheet View again. It should look like FWA_AREA. You see? tblInstallation now controls what data can be seen in the query.

You'll create tblInstallation and a query like this in the front end database. Wherever your application refers to FRA_AREA (or FWA_AREA or DTA_AREA) you're going to substitute the name of this query. You'll do something similar for every other table.

I think I've given you enough to go on for now. You need to create Installation columns for all the tables. For any tables that have a primary key, add Installation to the key; for other tables, create a non-unique index on Installation.

After that, you need to do the triple-import/append process to combine all the data from the three installations, filling in the Installation field for each one. Let me know when that's done. I'll be monitoring for questions.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[wlpsyp]

Rick,

Thanks so much. I need to catch up here. I have been spending all morning trying to get the damn tables right.

Here is where I stand as of now....

1. I have 5 database files now, IRO, FRA, FWA, DTA and Combined.mdb. each one of the four have there tables with there own range names. The combined.mdb all I did was import the FRA.mdb structure only, but some of the tables have rows that contain range names, etc. I did not import any structure or data from the FWA, DTA or IRO.

2. There is a table (this is where I spent most of my day trying to figure out as I did not realize I already had a table called Location. So to clarify now, there is a table in each of the .mdb's that is called LOCATION. Within this location table is the range names, The column name is TRA_AREA which has various numbers of rows that contain the range names for use as a drop down menu. Also within this location table is a column named TRA_AREAID. TRA_AREAID is a autonumber and the primary key. TRA_AREA is Text and 255 in size. I still need to add the INSTALLATION coulumn to this table and all other tables within the Combined.mdb correct? (FRA,DTA,FWA,IRO)

Do I really want to delete the rows in the Location table as it contains all of the names of the ranges, which the largest one is 235 rows? I hope not.

I just want to make sure that I am up to speed here so I can continue to follow your guidance.

I am going to take this home tonight and try and publish it to a web page if you want to look at my mess. Your call there.



It's not a problem...It's just a Blood Pressure Monitor

 

[RickSpr]

1. This is fine, except you said: "all I did was import the FRA.mdb structure only, but some of the tables have rows that contain range names". If you only imported the structure, how can there be any rows? I'm confused.

What I want is for you to have Combined.mdb have empty tables matching the structures of the tables in FRA.mdb (or any of the 4, since they're all the same). After you have the empty tables and have added the Installation column, the rest of my instructions tell you how to combine the data from the 4 front end databases into Combined.mdb.

2. LOCATION is the table I was calling tblRangeNames. Now that you've told me it has TRA_AREAID as its primary key (before, you said it didn't have a key), I need to revise my instructions. Change TRA_AREAID to data type Number (field size Long Integer). Add the Installation field to it, and also add Installation to its primary key.

Yes, I do want you to delete the rows from Location. Further down in the instructions you will import the Location data again, from each of the front ends. (DON'T delete the rows from the front ends, only from Combined.mdb.)

I'll look forward to seeing your web page and database.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[wlpsyp]

Rick,

Thanks for your time and paitence here.

I have uploaded a copy of the FRA.mdb and the Combined.mdb. I have really done nothing to both as I have been trying to play catch up and get these to a stable point to where I hope I can catch up to where you have me at now.

Just for clarity sake I will do nothing more to this db until you have a chance to see where we are because I know I have confused you on some verbage etc. So I will give you a chance to look through before I move on so I do not worsen the process.

Once you reply back I will reply with the web address and I will leave it up for you to download the two files then I will remove the files. Let me know a time, I will be checking back constantly

Thanks,

Bill

It's not a problem...It's just a Blood Pressure Monitor

 

[RickSpr]

I'm ready and waiting. Currently 22:34.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein