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

cskehan
cskehan used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Which database product are you using?
cskehanCFO

Author

Commented:
Access 2007

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

cskehanCFO

Author

Commented:
I get a method or data member not found error at (.bof and .eof) any ideas?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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

Commented:
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
MIS Liason
Most Valuable Expert 2012
Commented:
oh, and here is the "report"
;-)

Jeff
Access-EEQ-27731238-ConcatenateL.mdb
cskehanCFO

Author

Commented:
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 CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Lets back up a bit.

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

Author

Commented:
Jeff

yes it did!!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Then please post an *exact* graphic of what you now want, based on my sample report,...your description above is unclear.
cskehanCFO

Author

Commented:
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 CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial