Need Help To Search

[Power_User]

Hello My Friends ,



Because I am new in access and will explain you my problem. I have four databases as follows:

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

Tables 2, 3, 4: Fields: Company Name - Code - Date - Amount

Table 5 ( Search Result ): Table1.ID - Table1.Company Name - Table1.Total Amount - Table2,3,4.Code - Table2,3,4.Date - Table2,3,4.Amount

1- I need a query which search all Tables2,3,4 for field company name in first record of Table1 and if found records match to company name, add to Table5 and if not found any match, returned 0 as value to Table5 and then go to the next record in Table1 and do search all other 3 Tables again. I need to find a way to program automatically to do it and find any results from all three other Tables. Note: All Tables are inside an .accdb database.
2- Is it possible when program find matchs , mark finded records in Table1 for delete . Because in the end of program , we should have to tables . Table5 with result of search and Table1 with records that doesn,t math in other Tables.



Too much thanks for your help
Jack

[orange]

Are you confusing database with table?

cross posted at http://www.access-programmers.co.uk/...d.php?t=291162


Jack,

I recognize that this is your first post, but there is a way to cross post to get answers.
Please see this info.

[alansidman]

In addition to Jack's question, why do you have three tables with the same type of data. Suggest you read this link about design.

http://r937.com/relational.html

[Power_User]

Hi Orange .
Thanks for your response and attention . You right . My purpose was tables in the .accdb database . And again you right . I post my problem in some other access forum because as i said , i am new to access and this is my first serious work in new office that just start working . this is important for me to do this in best way and get correct result to continue work . For that i ask from my friends in all around the world to help me . If you think i mistake ask my question in your forum , OK I leave here . But if you want to help me and other people that may have this problem , it was better dont notice here and on other forum . Just send me private message and help me with an example to solve my problem .

Best Regards

Jack

[orange]

There is no mistake with your question. As I said previously, there is an accepted way to cross post.
When you cross post, just tell people you have done so - and post the related link(s).

Welcome to the forum.

[Power_User]

Hi Orange .
Thanks again for your response . One of my best friend in other forum try to help me and solve my problem . If solve the problem , sure I notice here and insert the solved problem sample here . If is possible to you , may you follow that forum and say your idea or help me with an example ?
Thanks
Jack

[orange]

Yes, that's a good idea. Whether you cross post or not, it is good for others when you tell us what your solution is.
Too many times users find an answer to their immediate problem and just leave.

I encourage you to tell us your solution. In fact, if your solution has broader application you might consider putting a sample in the forum Code Repository or Tutorials or Sample Databases.

[Power_User]

Hello My Friends ,


1-With help of one of my best friend in other forum . he create an example . With many thanks from his help , I put solution here :

Database.zip


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

[Power_User]

Hi Orange .
I have a question . In other forum I close my Poset as solved because I received my question on that post and start new post for complete example received in that post by help of experts . I dont know in this forum can I continue in this post or create new post ? Please answer what I do ? Now is important complete the example for use other people that may need .
Thanks
Jack

[orange]

I do not understand the issue you are facing. In your solution what do the records/rows with no company name represent?

Since your tables have same format, you could bring them into a temp table and work with a normalized structure.

You may also get some ideas from these video tutorials.

Intro to Database
The Relational Model
Data Modelling and the ER Model

[Power_User]

Hi orange ,
My mean is if possible the repeated company name only one time show in table . because whith the query method , for each match find by query , the company name repeated . I think maybe possible after finish query , use code to find repeated company name in the fields Company Name , Total amount and delete them . Maybe I should use Temp Table or report to get my ideal solution . I ask you as an access expert , tell me a best way you think with apply in sample database .
Thanks
Jack