1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

6 seperate databases to be joined

Discussion in 'Business Applications' started by Devan, Feb 7, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Devan

    Devan Thread Starter

    Joined:
    Feb 10, 2005
    Messages:
    24
    I am using MS Access 2003.
    I need to join up 6 db's which all have the fields, File Name, File Number and Department. These in turn link to another fee db, which records the fees and times received per department and person working on the case.
    I need to set up a report that pulls information from each of the tables and gives me a total fee received for each department.
    An example of multiple tables which filters all the duplicates out would be great as my report currently crashes as it pulls too many records. I set up the query using the department field to link the separate tables.
    Thanks
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you post a zipped copy of the database that contains an empty copy of one or more of the tables that you wish to join and a copy of the query that you have created.
    It sounds like your Table "Joins" are not correct, giving you multiple instances of each record.
    One way to check this is to just use 2 tables in the Query and see how many records you get.
    The other possible fix is to set the Query's "Unique Values" Property to "Yes"
     
  3. Devan

    Devan Thread Starter

    Joined:
    Feb 10, 2005
    Messages:
    24
    I have uploaded 2 small db files they link through the File Number field. I have taken all data out and all other tables and queries to make the db files smaller. Basically I have another 5 db's like this one with 5 matching time db's. I need to add them all to 1 query so I can do a total by department.
    thanks for any help you can offer.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Sorry, they do not appear to have "Uploaded", they have to be "Zipped" to put them on the forum.
     
  5. Devan

    Devan Thread Starter

    Joined:
    Feb 10, 2005
    Messages:
    24
    attached are another is a db file with its corresponding time db which would be linked to the first db I attached.
     
  6. Devan

    Devan Thread Starter

    Joined:
    Feb 10, 2005
    Messages:
    24
    I now realise they have not uploaded as they are too big, the smallest is 485KB when zipped it only has 1 empty table in it, the other also only has 1 empty table but is 1109 KB when zipped. I do not know how to get them any smaller. I have removed everything apart from the tables.
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Devan, do you know how to do a Compact & Repair, that will make them much smaller.
    Open Access and on the main menu click on Tools>Database Utilities>Compact & Repair
    Browse to a database and click on it and then the Compact button. Access will prompt you to save it, you can either overwrite the old one or create a new one.
     
  8. Devan

    Devan Thread Starter

    Joined:
    Feb 10, 2005
    Messages:
    24
    thanks, that has reduced the size tremendously.

    I have attached the two db's and two corresponding time db's.
     

    Attached Files:

  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Devan, the first question that I should have asked you is why are there 6 Databases?

    What connects (links) your databases to each other>
    The "File Number" Field?
    Have "Relationships" been set up between the various Tables in the different databases?

    There doesn't seem to be any "Index" on that field and it also allows "Duplicates".

    Can you post a copy of the Query that you have created?
     
  10. Devan

    Devan Thread Starter

    Joined:
    Feb 10, 2005
    Messages:
    24
    There are separate databases because they are used by different departments and they have hundreds of different queries. The file number relates the time database to the Cases database but this would not link it to the other databases as they will have different or the same file numbers. The only field I can identify to link the tables would be the department field which occurs in each of the tables. I am not sure how to create a relationship between then if it does not already exist.

    The Query I wrote is on the live db, but basically I liked the cases tables together using Department, I then liked case 1 to time 1 using the File Number field and the same for the other table. The query I wrote was very simple just to pull in the file number and department for each case, obviously I have 6 cases tables and 6 corresponding time tables.
    Basically I was going to start it off simple then add the fields I wanted and do the calculations on the end report.

    I recently inherited these db's but they do have a primary key called ID which is an automated number, I deleted these when sending to you.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Devan, this sounds as if you have inherited someone elses "MESS". There is no reason to have 6 different databases for 6 different Departments.
    But that doesn't help you resolve this.
    I can't really help with any Hands on stuff because I am sure that your Company would not grant me access to the actual Databases.
    So this is what I suggest that you do, assuming the the tables that you are interested in all have the same fields and field name (it doesn't really matter if they don't it just makes it easier).
    Import a table from one of the databases, remove all of the records and then compact and repair the database to reset the Autonumber count to zero.
    Now create a Select Query for each of the 6 tables, delete the "autonumber" field from the queries. When you are happy that you are getting the correct data convert the queries to "Append" queries that append the data to your "new, empty" table.
    Run the queries and you will have all of the data in one table. You can now interrogate that table on it's own.
    If you do not know how to convert a Select table in to an Append table you do it like this -
    with the Query in Design Mode on the Access main menu click on the "Query", this will open the Query menu.
    find the "Append Query" and click that, Access will convert your query and then ask you which Table you want to Append the data to, from the drop down list select your new table.
    The Access wizard will match up the field names in the "Append To" row.

    I can probaly create an Example append query if you give me a complet copy of one of the donor databases Tables (less the data of course).
    Have I already got one?
     
  12. Devan

    Devan Thread Starter

    Joined:
    Feb 10, 2005
    Messages:
    24
    Thank you so much, I do know how to do an append query. I just couldn't think how to do this but this will resolve my problem. Thank you for all your efforts.
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Let me know if you need any more help with the Append query and then the query to get the data out the way that you want.
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/541982

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice