Link to home
Start Free TrialLog in
Avatar of Connie Jerdet-Skehan
Connie Jerdet-SkehanFlag 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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Which database product are you using?
Avatar of Connie Jerdet-Skehan

ASKER

Access 2007
Avatar of trbaze
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
I get a method or data member not found error at (.bof and .eof) any ideas?
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
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
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Lets back up a bit.

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

yes it did!!
Then please post an *exact* graphic of what you now want, based on my sample report,...your description above is unclear.
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
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...