Retrieve names of fields common to multiple tables

[cj-1289]

I have multiple tables, most of which share fields like 'Job number', 'Company', and 'Client'. I want to select a few tables and have all these identically named fields listed in a combobox.

So let's say I select the tables 'Table1', 'Table2', and 'Table3', which all happen to three fields sharing the same name: 'Job number', 'Company', and 'Client'. I want these displayed in the box.

I would need to compare every field in all of the tables and .additem to the combobox whenever I get a match. I'd be most grateful for any thoughts on an efficient way to do this, thanks in advance

(Also, thanks to the_doc_man and sos for their contributions to my last query.)

 

[Atomic Shrimp]

Sounds like a nightmare. Is this to enable the number of similar tables to continue to grow?

 

[cj-1289]

No. I need to transfer the names of the shared fields into another table, called 'Modifiers'. Each record of Modifiers contains the names of the tables to be updated (taken from a manual selection) and the fields that these tables have in common (taken automatically from comparing the tables when saving a record to Modifiers).

The fieldnames like 'Job Number' and 'Company', are saved to fields 'Field1', 'Field2' etc in Modifiers, ready to be retrieved as a 'Field' & counter in a loop elsewhere.

(I was wanting to .additem to a combobox first so the user could review the fields before submitting them to Modifiers' record, but the issue is obviously with establishing the matches.

 

[Atomic Shrimp]

Crikey.. I'm still not sure I understand where you're taking it - but to gain access to the table and field names in your database, the code looks something like this (untested air code):

Dim db As Database
Dim tdfTemp As TableDef
Dim intloop As Integer
Set db = CurrentDb
For Each tdfTemp In db.TableDefs
'table name - do something here with tdfTemp.Name
'number of fields - do something here with tdfTemp.Fields.Count
For intloop = 0 To tdfTemp.Fields.Count - 1
'field names - do something here with tdfTemp.Fields(intloop).Name
Next intloop
Next tdfTemp

 

[SOS]

By the way, this statement:

I have multiple tables, most of which share fields like 'Job number', 'Company', and 'Client'.

makes one believe that this database is not normalized. What is the purpose of having redundant data stored? Do these come from different data sources or is it designed this way. If designed this way then I would say that the design could be flawed (90% chance of this).

If you are not familiar with normalization you should read this.

 

[Atomic Shrimp]

I'm clutching at the faint hope that the OP's project is an attempt to create a user interface to help normalize a very large number of duplicate tables, or something like that.

 

[SOS]

I'm clutching at the faint hope that the OP's project is an attempt to create a user interface to help normalize a very large number of duplicate tables, or something like that.

Yeah, I thought I'd ask and at least throw that in as we don't necessarily get all the information up front without poking and prodding.

 

[cj-1289]

It's not designed this way, it's pulling multiple data sources together. I'm trying to create a program to harmonise this company's disjointed databases.

The tables have different purposes but share certain generic information. One of the purposes of what I'm doing is to have the generic information entered once into a form, and to have it replicated a certain amount of times into certain fields in certain tables in certain databases across the company.

Each of these uncertainties is a variable depending on an individual user's requirement, so each different 'requirement scenario' (I call it a 'modifier') becomes a single record in the table Modifiers, looking like this:

Modifier name Table1 Parameter1 Table2 Parameter2 ....
Field1 Field2 Field3 Field4...

(A parameter is a letter which references a number or other variable inputted by the user which determines how often the data is replicated per table, ie 8 records with the same generic information.)

Load a 'modifier' and use a form to fill in data for the fields specified in the modifier, and it'll replicate in accordance with the modifier.

SO I just wanted a way to look at the tables listed in a modifier, and returning all the names of fields which were in common between them to fill up the modifier.

Alternatively I can have the user manually look at both tables and write in which fields they share. But that would hardly be what you'd call automation...

I hope there is no more confusion as to what I'm doing! I'm not a twit who's arbitrarily using the same data over and over again...

 

[SOS]

If you are looking for similar data, you can use a Union query to pull it all together in one place. All you need is the same number of fields pulled in each part of the Union query.

 

[cj-1289]

If you are looking for similar data, you can use a Union query to pull it all together in one place. All you need is the same number of fields pulled in each part of the Union query.

Do you mean that if there were 2 tables in the queries, they should both have eg 50 fields?

I may have misunderstood what you meant by that, but if not then there is no harmony in the number of fields between tables.

 

[SOS]

Do you mean that if there were 2 tables in the queries, they should both have eg 50 fields?

I may have misunderstood what you meant by that, but if not then there is no harmony in the number of fields between tables.

If you are checking for say, an ID field, a Name, and a Description then you would pull all 3 of those in each query. If you are trying to merge everything then you have your work ahead of you, but you can still do a Union query by creating NULL fields for missing fields that are not in the other table(s).

So, for example if I had two fields in Table1 (ID and Description) and I had three fields in Table2 (ID, Description, and UserName) then I could union them by using:

SELECT ID, DESCRIPTION, NULL AS UserName FROM Table1
UNION
SELECT ID, Description, UserName FROM Table2;

Does that make sense?

 

[cj-1289]

Yes, thank you, though that can't apply to this situation. I do not know which fields the tables will have in common and thus I can't run a specific search for description etc. Each table is filled with roughly the maximum number of fields: I just want to return the names of the fields they have in common.

 

[HiTechCoach]

It is possible to read the field names.

Here is code code to help get you started:

DAO Programming Code Examples

I searched here at AWF and found this:

creating a Data Dictionary

Hope this helps ...

 

[Atomic Shrimp]

If these are linked tables from a bunch of diverse and disparate in-house mini-apps, I think it's going to be easier to hand-build a selection of union queries, as SOS suggests - you can even include a synthetic 'source' field, so supposing you have... I dunno... tblKeyHolders from an application that looks after the details of people entrusted to unlock the building and tblSignatories from a database that stores the details of people entrusted to sign instructions on the company's bank accounts, you could pull them together with a union query such as:

SELECT tblKeyholders.KeyholderID AS PersonID, 
tblKeyholders.Forename, tblKeyholders.Surname, 
tblKeyholders.Phone, tblKeyholders.Address, 
tblKeyholders.Email, "tblkeyHolders" AS recSource
FROM tblKeyholders

UNION

SELECT tblSignatories.signatoryID AS PersonID, 
tblSignatories.Forename, tblSignatories.Surname, 
tblSignatories.Phone, tblSignatories.Address, 
tblSignatories.Email, "tblSignatories" AS recSource
FROM tblSignatories;

-That way, you'll get all the records in one place, with a field identifying which table they came from.

The only reason I can see that you might want to be able to pull it all together dynamically by interrogating the field names, etc, is if you have an unknown or variable/increasing number of tables in the database - and interrogating them for their field names is only going to help if you can be certain that similarly-named fields are going to contain similar kinds of data (and that's most likely a big, unsafe assumption).

 

[cj-1289]

Thank you everyone for your replies. I'm still left unanswered to a point though.

HiTechCoach gave a useful way of collecting the names of the fields in a table.

I could collect it into a data dictionary as suggested. Though with multiple tables, I would need multiple data dictionaries. This seems cumbersome, so I may wish instead to write the field names to a single new table, where each field of this table contains all the field names of the table it came from. How then would I cross examine these?

Assume that this is collected in my tblFieldNames, from my tbls1, 2 and 3:


tblFieldNames
tbl1_____________tbl2___________tbl3 __________
Hair Colour_______ Hair Colour_____Age
Eye Colour_______ Running speed_ Years employed
Age___________ Health status___ Years until retirement
Height___________ Age__________ Average hours per month
Health status____________________Health status

Clearly, all of these tables have two fields in common: Age and Health Status. I did not know the names of these common fields before. I want this to be listed in my table Modifiers next.

tblModifiers
Field1 | Field2 | Field3
Age | Health Status |_______


It's not a problem to transfer data. The problem is just searching and comparing without knowing what you're searching for.

(The union query would be nice, except I have no idea what I'm looking for. So in Mike's example, I can't just search for the text of 'Name' fields in 2 different tables and bring them together to display names of people that the united tables have in common.)

 

[gemma-the-husky]

once you HAVE the common fields, are you trying to handle them automatically? this won't be easy either.


here's some code to check for common fields between two tables. the code is awkward, as testing tabledefs collections generates run time errors, so i always end up with spaghetti and gotos to handle this - there may well be a more elegant way

in my example the common fields are added to an array.

took a while as i was forgetting to add the .name qualifier at the start.

Sub main()
'call this with two tables in your database
Call comparefields("invoiceheader", "invoicelines")
End Sub


Sub comparefields(table1 As String, table2 As String)
Dim commonfields(50) As String 'the common fields array
Dim common As Long 'the number of common fields
Dim x As Long

Dim fldcount As Long 'the field being examined
Dim fldname As String 'the name of the field being examined
Dim foundcommon As Boolean

Dim dbs As Database
Dim tdf1 As TableDef
Dim tdf2 As TableDef

Dim s As String

Set dbs = CurrentDb

Set tdf1 = dbs.TableDefs(table1)
Set tdf2 = dbs.TableDefs(table2)

common = 0
fldcount = 0

nextfield:
    'ok - get the next field name
    'if no more fields then it generates a runtime error - is there a more elegant way?
    
    On Error GoTo finish
    fldname = tdf1.Fields(fldcount).Name

    'ok - now test to see if this field is in the second table
    'bit of spaghetti here to carry on correctly if a field is found, and abort if a field isnt found
    'tricky because it generates a runtime error - is there a more elegant way?
    
    On Error GoTo notcommon
    foundcommon = (tdf2.Fields(fldname).Name = fldname)
    
    common = common + 1
    commonfields(common) = fldname
    GoTo incfield

notcommon:
    Resume incfield

incfield:
    fldcount = fldcount + 1
    GoTo nextfield
    
results:
'no more fields
    If common = 0 Then
        Call MsgBox("No common fields between " & table1 & " and " & table2)
    Else
        s = ""
        For x = 1 To common
            s = s & commonfields(x) & vbCrLf
        Next
        Call MsgBox("common fields between " & table1 & " and " & table2 & vbCrLf & vbCrLf & s)
    End If
    Exit Sub
    
finish:
    Resume results
End Sub

 

[Atomic Shrimp]

I could collect it into a data dictionary as suggested. Though with multiple tables, I would need multiple data dictionaries. This seems cumbersome, so I may wish instead to write the field names to a single new table, where each field of this table contains all the field names of the table it came from. How then would I cross examine these?

Include a column for the table name (maybe another for the data type) - you could then use the same table twice in a query (once as an alias), join on fieldname and you'll get a list of the matches.

So if you've built a data dictionary table that looks a bit like this:

[B]fieldname,tablename,datatype[/B]
firstname,contacts,text
surname,contacts,text
phone,contacts,text
firstname,keyholders,text
surname,keyholders,text
name,phonelist,text
phone,phonelist,text

You can run this query:
SELECT datadictionary.fieldname, datadictionary.tablename, datadictionary.datatype, datadictionary_1.tablename, datadictionary_1.datatype
FROM datadictionary INNER JOIN datadictionary AS datadictionary_1 ON datadictionary.fieldname = datadictionary_1.fieldname
WHERE (((datadictionary_1.tablename)<>[datadictionary].[tablename]));

and you'll get results like this:

[B]fieldname,datadictionary.tablename,datadictionary.datatype,datadictionary_1.tablename,datadictionary_1.datatype[/B]
firstname,keyholders,text,contacts,text
surname,keyholders,text,contacts,text
phone,phonelist,text,contacts,text
firstname,contacts,text,keyholders,text
surname,contacts,text,keyholders,text
phone,contacts,text,phonelist,text

- so the first line is telling you that the field 'firstname' exists in 'Keyholders' and 'contacts' (admittedly, this information is repeated the other way around on the fourth line, the other way around.

 

[cj-1289]

Thanks very much G_T_H and Mike: I'll toy around with your examples soon and see if I can implement them, and post back with what I found.

I'll note that this concept was one that my manager deemed to be impossible, so it'll be brilliant if I can show him it up and running

 

[HiTechCoach]

I would agree with your manager in that it would be impossible to justify the time to develop a new design tool that is easier that using the already available Access design tools. IMHO, you are getting close to "reinventing the wheel." I can't see a way of justifying the time/resources/expense for this project

 

[cj-1289]

HiTechCoach, I'm comparing tables automatically to save users from manually comparing tables between different sources. If the design tools had been used in an 'optimal' way when the tables were being made in the first place, this wouldn't have been necessary. But they weren't, and it is. This time/expense is a requirement for the company to run things at a decent speed in future. If I'm reinventing the wheel, then the first wheel must have been a bit square.