6 seperate databases to be joined

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

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"
 

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.
 

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.
 

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.
 

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.
 

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.
 

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?
 

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.
 

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?
 

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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top