Multiple tables search

[Power_User]

Hello My Friends ,


1-With help of Ben ( my best friend ) , he create an example and solve my problem asked in Post ( Need Help To Search ) . With many thanks from Ben , 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 , insert 0 on blank fields 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 , I should have two tables . Table5 with result of search and Table1 with records that does nott mathc in other Tables.

Thanks for your help

Jack

 

[JHB]

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

Not in a query, you can do it in a report.

 

[Power_User]

Hi JHB ,
I dont know which way I can use report to get my request . Can you please use sample database and create report that you said ? And can use report to create table ?
Thanks
Jack

 

[JHB]

Database attached, a control in a report has the property "Hide Duplicates" set it to "Yes" for the controls you wish.

 

[Power_User]

Hi JHB ,
Thanks for your sample database . It work very good and I found control in Company Name and Total Amount , All tab . I have some questions :
1- Can I use report to create Table ?
2- Possible use report output to excel file ?
3- Possible use report to insert 0 into empty fields (amount,code) as shown in my First Post ?
Best Regards
Jack

 

[JHB]

1- Can I use report to create Table ?

No. Use a make table query

2- Possible use report output to excel file ?

No. Use the DoCmd.TransferSpreadsheet

3- Possible use report to insert 0 into empty fields (amount,code) as shown in my First Post ?

No not the report itself, but the controls in the report can be set to show 0 - but it is easier to run an update query against your table
From your first post:

... create something like follow :

And that was what I did using a report.

 

[Power_User]

Hi JHB ,
Thank you for expend your expensive time for help me and solve my problem . Can you please complete sample database with use update query against table5 and the DoCmd.TransferSpreadsheet to convert table5 to excel file ?
Best Regards
Jack

 

[JHB]

Google:
update query+MS-Access
DoCmd.TransferSpreadsheet+MS-Access

There are plenty of examples how to do it.