Avatar of Connie Jerdet-Skehan
Connie Jerdet-Skehan
Flag for United States of America asked on

Create a report that is based on a matching field found in all tables

I have several tables in a database.
New tables are added each day.
Each table has a "Name" Field.

I need to know if there is a way to go through the list of names in each table and report back which tables they are in. I need to do this without naming the tables in coding as new tables are added to the database everyday.

Example  John doe is found in table 1 table 2 table 14 and table 15
Microsoft SQL ServerMicrosoft Access

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Patrick Matthews

Which database product are you using?
Connie Jerdet-Skehan

ASKER
Access 2007
trbaze

Here's one way to do it:

1.  Create a table called "TempTable" or what ever you want to call it.
2.  Create two text fields named "TableName" and "Data" in that table.
3.  Create a report based off of that table.
4.  Run this code to populate the table:
Dim db as DAO.Database
Dim td as DAO.TableDef
Dim Tempdb as DAO.Database
Dim Z as Integer

'Clear out the temp table
Docmd.RunSQL("DELETE * FROM TempTable")

'Open current database into a recordset and loop through each table
Set db=Currentdb
For Each td in db.TableDefs
     If Not (td.Name Like "MSYS*") then	'skips system tables
          'Open table into a recordset
          Set Tempdb=Currentdb.OpenRecordset(td.Name, dbOpenDynaset)
          With Tempdb
               If Not (.BOF And .EOF) then	'records found
                    .MoveLast
                    .MoveFirst
                    'loop through each record and write to TempTable
                    For Z = 1 To .RecordCount
                         Docmd.RunSQL("INSERT INTO TempTable(TableName, Data) SELECT '" & _
                         ![td.Name] & "' AS Expr1, '" & ![Name] & "' AS Expr2;"
                    Next Z     'next record
               End If
          End With
          Tempdb.Close
          Set Tempdb=Nothing
     End If
Next           'Next table
db.close
Set db=nothing

Open in new window

5. Open up the report
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Connie Jerdet-Skehan

ASKER
I get a method or data member not found error at (.bof and .eof) any ideas?
Jeffrey Coachman

It took me a while, but try this...
;-)

It should adapt automatically as you add tables every day, as long as you name your tables consistently ("table 1", "table 2", "table 100")
(The text "table", followed by a space, then the number)

Notes:
1. Mary Jones and Lisa Smith are in more than one table.
2. Please do not name your field "Name", this causes issues because "Name" is a reserved word.  I have used the field name "TheName" in my sample and code.
Give your field a more descriptive name like: CustomerName, EmployeeName, ..etc
3. Finally, consider splitting the name into two separate fields.

Study it, test it, examine it, ...and I am sure you will be able to adapt this to work in your database.

Have fun!

;-)

JeffCoachman
Access-EEQ-27731238-ConcatenateL.mdb
trbaze

Sorry, I mistyped.  Tempdb needs to be dedclared as a DAO.recordset, not database, so:

Dim Tempdb as DAO.Recordset  'this is correct

Not-
Dim Tempdb As DAO.Database    'this is wrong
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Connie Jerdet-Skehan

ASKER
Jeff,

The table names are designed as such relative_finder_Firstname_Middle_Lastname_20120525 for the purposes of a prettier report how would I parse the table name to just show firstname_middle_Lastname in the report.
Jeffrey Coachman

Lets back up a bit.

My sample seemed to have answered your original question, (as posted), fully and completely?  ...Correct?
Connie Jerdet-Skehan

ASKER
Jeff

yes it did!!
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jeffrey Coachman

Then please post an *exact* graphic of what you now want, based on my sample report,...your description above is unclear.
Connie Jerdet-Skehan

ASKER
Table names come in like this

Relative_finder_John_P_Consumer_20120525
Relative_finder_Joe_Blow_20120525
Relative_finder_Nancy_Consumer_20120525

In the report I would like it to show like this


DnaName             Found In
nancy consumer   joe_blow,   john_P_consusumer

Thank You in advance
Jeffrey Coachman

Still don't understand..

This is radically different from what you originally asked for.

It is not clear what data is in what column...

Pleas post a sample *database* that has 3 small tables, with a few sample records.
Then post a graphic representation of the *Exact* resulting output you are seeking...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.