Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How Do I? (Access 2000)

    I have created multiple copies of a database. I have them all named the same but in different folders on my hard drive. Is there a way that I can query all the databases for information at the same time? I mean for example in database A I have john Smith and in Database B I have Reginald Arnold but both customers have activated their service I want to create a query or report to look in both Database A and B to find anyone who activated their service. If anyone can help I have a blank copy of the database I can send zipped it is 196K Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How Do I? (Access 2000)

    Why do you have multiple copies of the same database with the same name in different folders, and what exactly is it that you're trying to do? If you've put segments of your data in different databases, you're asking for trouble, because it will rapidly become impossible to maintain. What was the idea behind this?
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How Do I? (Access 2000)

    are you saying that john smith is database a
    but not in database b

  4. #4
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How Do I? (Access 2000)

    Yes the databases are for different offices so the information collected is the same but all custoers are different. The person in charge of each office did not want their information mixed with the information of any other office.

  5. #5
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How Do I? (Access 2000)

    Each of the databases are used for different offices that I have. Each database keeps track of the same information but for different customers from different markets. The person in charge of each office did not want their data combined with any other offices data. So what I am trying to do is find a way to query all of them from one main database or something like that.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How Do I? (Access 2000)

    You have built yourself a nightmare. Data from one office doesn't contaminate another, no matter what the offices in question might think, and it isn't necessary for everyone to see all the records if the database is structure properly. What you should have done was to look into partial replication which would have allowed each office to see its own data but would still sync that data back to a single data store that you could use for reporting and other purposes. As it is, it is going to be very slow and awkward to create union queries for the data from all those separate databases, assuming that it will work at all.
    Charlotte

  7. #7
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How Do I? (Access 2000)

    How woudl I do what you are proposing as far as using a main database but without allowing access to other offices information? I am new to this I have built databases and such but not jumped deeply into the power that access has to offer. Would it be easier to see the database I was talking about? If so I can email you a blank copy.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How Do I? (Access 2000)

    It isn't necessary to see the database. There are several methods, but they depend on what kind of network arrangement you have. Any method would require that each record be identified by Office as well as any other information, because you would need that in order to filter the recordset for a particular office.

    One way is to use Access security and assign each office to a separate user group. When the users log in, you would check the user group they belonged to and filter records for that office. Another way is to use partial replication, which is more complicated but can even be used on machines like laptops that are not permanently connected to a network. Check the Microsoft site for information on partial replication.

    None of the methods are simple but neither is trying to manipulate a bunch of separate databases to make them behave like a single coherent database.
    Charlotte

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How Do I? (Access 2000)

    Charlotte is absolutely right here. We have spent the last 18 months fixing databases because our client insisted early on that they didn't want any data from different departments comingled. Then they discovered that they had to maintain addresses in multiple databases, the right hand never knew what the left hand was up to, etc.

    You indicate that all of the databases are on your hard drive - if that is always the case, replication would work pretty well. On the other hand if they are normally located on the user's hard drive and the users are some distance apart so modems would be involved, replication can take quite a while. It also tends to bloat the database significantly. I would probably look at the idea of filtering data so users only see their own if it can be implemented without a huge effort.
    Wendell

  10. #10
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How Do I? (Access 2000)

    If you do not want to use security or replication, a "cheap" way to accomplish this is to create a new, central, database that has only pointers to the tables on the other databases. Link to each database's tables and give the local link name a different name for each (it is required that they be different anyway, but you could name them so it makes sense to you).

    For example, if each office database has an Address table, link Address1 to office number 1's Address table, link Address2 to office number 2, Address table, etc. Next, create a UNION query that combines all Address tables together:

    SELECT FirstName, LastName, Address, City, State, Zip FROM Address1

    UNION SELECT FirstName, LastName, Address, City, State, Zip FROM Address2

    UNION SELECT FirstName, LastName, Address, City, State, Zip FROM Address3;

    Call this the Address query. Now, any other queries, reports, forms, etc. that were used to refer to the Address table (in the office databases) will now refer to the compendium of Address tables in all offices in this composite database! It will run slower than the other two ways (security and replication), but it will do the job.

  11. #11
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How Do I? (Access 2000)

    Thank you all for your help!

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How Do I? (Access 2000)

    I thought I would put my 2 cents in ...

    I also agree with Charlotte, however, I have wore the shoes "the boss wants it". After learning the hard way, I found it better to sit the boss down and explain why (sometimes) their idea isn't the most practical and efficient not to mention logical. If you can explain this on their terms, you will save yourself a lot of headaches.

    At any rate, I would make copies of the 4 db's and in your spare time, clean them up as Charlotte mentioned. In the intrim combine the tables, assuming the tables have the same name, fields have the same name, and the structure is the same. Move the tables to a front end DB and link the tables to each office. Trust me they won't know the difference.

    Good Luck,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •