Need Help To Search

[Power_User]

Hello My Friends ,
Excuse me about my bad english . I have 4 Databases . Database no 1 has fields like: id - company name - total amount . Other 3 databases has fields like: company name - code - date - amount . I shall use company name in first record in database no 1 as search string and search all other 3 databases and find total records which contains company name . I shall do this for all records in database no 1 and search other databases for each of them . result of search shall contain All fields in database no 1 and all fields in other databases . I nedd to save result of search on a new database .
Please help me to do this . A sample database which contain example and code , will more helpfull .
Thanks

 

[boerbende]

5 databases sounds chaotic. Are you sure you want to do this?

But a shot for the bow
Create database "5" with linked tables to the other 4 databases
create a "create table" query. Add the 4 linked tables to this query and join them on customer name
Make very sure that the names are identical. It is not possible to work with only one ID?

Ben

 

[Power_User]

Hi Ben ,
Thanks for your Response . You are right . The databases really are chaotic . Because they came from 4 places . I should use only company name in database no 1 to search inside all other 3 databases . I am new to access and in my office we have ms access 2010 . Can you please send me an example about your above recommend .
Thanks

Jack

 

[boerbende]

You can’t expect to get a demo with 5 databases. But some instructions might do

Start a blank database
Click from the menu:
External data,
Access database
Browse to the first database you want to link
Select LINK to the data source
Click OK
Now you see all the tables in the other database. Select here the table with the customer
Do this for the other three databases
Now you have a new database with 4 linked tables (so actually without data)

Start a new query by create, query design. Start first slowly by adding only one of the tables (preferably the one with the most records) and try out.
Add then the second table and link them on customer and try out again. I guess you have to do a lot on your data to get the names matched between the tables
A normal join in a query between tables gives only result when both tables contains at least one record of customer. So it might also that you have to change the join between the tables to right or left join to force the query to have all the records from the one table and only those from the other when there is a match

If you have a query which works, then you simply change the query to a Make Table query
When working with the query, Click on design
Click on Make table

 

[Power_User]

Hi Ben ,
Thanks for your recommendation . I will try this and hope work for me .

Jack

 

[boerbende]

One thing. Databases are often created because users have a need to store data, but it does not mean the database they are using is sacred
If you can give them a better solution which also works for you, you might be able to create a better solution for you all. My suggestion is to first look into that kind of improvements. In the end it will save you time

 

[Power_User]

Hi Ben ,
Because i am new in access and specially not understand link method , is it possible you send me a sample that contain above your comment as following database description :

Database 1 :
Fields : ID - Company Name - Total Amount

Database 2 - 3 - 4 :
Fields : Company Name - Code - Date - Amount

Database 5 :
Database1.ID - Database1.Company Name - Database1.Total Amount - Database2-3-4.Code - Database2-3-4.Date - Database2-3-4.Amount

Two Things :
1 - I need a query which search all databases 2-3-4 for field company name in first record of database 1 and if found records match to company name , add to database 5 and if not found any match go to next record in database 1 and do search again . I need find a way program automatically do it and find any result from all 3 other databases .
2 - All 4 databases are inside an .accdb database .

Again too much thanks for your help
Jack

 

[jdraw]

cross posted at http://www.accessforums.net/showthread.php?t=63552

 

[boerbende]

I think I understood this wrong. First some definitions.
A database is the .accdb file. A database can contain one or (usually) multiple tables
I guess what you call here a database is actually a table in the database?
A table has one-multiple columns
a line / data in a table is called a record

I have an example
You have to run qry_MakeTable5

 

[Power_User]

Hi Ben ,
Too much tanks for your help . You right . I confuse and mistake definition about table and database . Please excuse me . My purpose was 5 tables in a Database . I will test your example and tell you about result .
Best Regards
Jack

 

[Power_User]

Hi jdraw ,
I send you message on that forum . Please look at this message .
Thanks for your attention .
Jack

 

[Power_User]

Hi Ben ,
Your example was very good and helpfull . I add some record to two databases 1 and 2 .
View attachment Database11-Modified.accdb
After that I run the query qry_MakeTable5 . The result is as follow :

If possible to make some change on query or use a temp table to store result and after finish query remove repeated items and create something like follow :

Again thanks for your expensive help .
Best Regards
Jack

 

[boerbende]

I am not really familiar with that. I think it might be possibly with a group option in a report. Maybe somebody else has an idea here?

My favorite combination is to have data and functionality in Access, but to report in Excel. My quick fix for this would be to copy the information to Excel and rework the information with an iif( statement).
But to be honest, I will be without computer for some days. I hope somebody else can help you here

 

[Power_User]

Hi Ben ,
Thank you for expend your expensive time for help me and solve my problem . As you said , if you permit me to share your example with others , maybe somebody can help me to do that . I wish good days and success in the future for you .
Best regard
Jack

 

[Power_User]

Hello My Friends ,


1-With help of Ben ( my best friend ) , he create an example . With many thanks from his help , I put modified example here :
View attachment Database.accdb
After run qry_MakeTable5 , Result as follows :

If possible, I need to make some change on query or use any way after finish query remove repeated items and create something like follow :


2- Is it possible when program find matchs , mark finded records in Table1 for delete . Because in the end of program , we should have two tables . Table5 with result of search and Table1 with records that doesn,t mathc in other Tables.

Thanks for your help

Jack