Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    I have a user that would like to be able to search through all existing tables for a certain text string and for the results to show the data source table name. For example if they searched for the text “ABCCustomer” then they would like the results to show all the tables that have that text string like below:



    Table:FY10Customers

    Table:FY11Customers

    Table:CustomerAddresses

    Is there a way to do this?
    TIA
    MOState

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    You could run a select statement on each table and collect all the results in a temporary table that you query. Not a good method if you want to search often.

    cheers, Paul

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You could also use a UNION query with the field to Search and the Name of the Table as a calculated column.
    Then when found the term would show correct table names

    The Search Query would be something like

    Code:
    SELECT [SearchFieldFromTable1], "Table1Name" As TblName FROM Table1Name
    UNION
    SELECT [SearchFieldFromTable2], "Table2Name" As TblName FROM Table2Name
    UNION
    SELECT [SearchFieldFromTable3], "Table3Name" As TblName FROM Table3Name
    ORDER BY [SearchFieldFromTable1]
    The Column Headings come from the Field names in the First Select Statement
    As to speed of Search, that depends upon the size of the tables
    Andrew

  4. #4
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Thanks so much for the replies. The database has 70 tables so I was hoping for something a little quicker to create.

    Thanks!!

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Looks like you'll need to use VBA.
    Or you could have a VBA routine that builds the Search Table say once each day.
    With 70 tables I cannot think of an easy route.

    Why so many tables?
    Andrew

  6. #6
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    I was thinking about going the VBA route. I'm not sure why they have so many tables, I haven't actually seen the database.

    Thanks for your helpful suggestions

    Have a nice day!!

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Are you looking for a results set or just the names of the tables where records were found?
    Richard

  8. #8
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Just the names of the tables where the records are found.
    Thanks

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by MOSTATE View Post
    Thanks so much for the replies. The database has 70 tables so I was hoping for something a little quicker to create.
    With that many different tables containing the customer name, it would seem you have serious normalization issues. You don't indicate how many records a typical table has, but if they are large, you have a major issue trying to do what you suggest.
    Wendell

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You could build a for loop that would loop through the tables collection. Inside the loop you chould have a SQL statement that would look for the requested value and if found update a text string. When the loop completed the text string would be the list of tables containing the search value.

    Maybe not the best solution, but with so many tables it would work. Also, you would not need to update the code if tables were added.


    Hope this helps.
    Richard

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by MOSTATE View Post
    I have a user that would like to be able to search through all existing tables for a certain text string and for the results to show the data source table name. For example if they searched for the text “ABCCustomer” then they would like the results to show all the tables that have that text string like below:



    Table:FY10Customers

    Table:FY11Customers

    Table:CustomerAddresses

    Is there a way to do this?
    TIA
    MOState
    Give thisdatabase a try, I developed this a while back. It won't work searching for single or double quotes.
    Attached Files Attached Files

  12. #12
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The obvious thing (alluded to in previous replies) seems to be poor database design. A separate table per year and a separate table for the addresses too? Should all be one table with customerDate or customerYear as a field. Too bad you are stuck with it.

    Anyway, just thought I'd add my two cents about another way to approach the problem. You can do this without VBA (although I'd probably use it anyway). Create 70 queries (yuk!) that are something like:

    Select distinct "FY10Customers" as TableName, CustomerName from FY10Customers;"

    Then create a union query for these (one would hope for fewer) 70 tables. You should be able to do a query on this query to get table name and customer name.

  13. #13
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I think that was my possible solution from earlier in the thread.
    But if I had to do it, and it did not have to be immediately live data, I think I'd go for the BIG
    temp table built with a bit of VBA or Big MakeTable Union Query.
    Then it would sit in one table to extract the data.

    Glad I haven't go the job.
    Andrew

  14. #14
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AKW View Post
    I think that was my possible solution from earlier in the thread.
    I should have read more closely! I agree tho, not a nice problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •