Check to see if a field on one table is on a second table

[bastnpak]

I have two tables. (Table A and Table B).

I want to check if each of the fields on A are on B.

my desired result is a message : -NameField, PhNum is on A but not B.


- Address, CarMake is on both A and B


(I know how to do in excel since the headers/field names are a row that is searchable (ex:Match tableA-A1 to Row1- table B, return message to state if missing or not). but not sure where to start in Access)

[Welshgasman]

Look at DLookup() or possibly even DCount()

[Micron]

Since you've mentioned Excel I'll bet dollars to donuts that your tables are not normalized, and that really matters. You might want to check out db normalization if you're not familiar with the term.

Not sure how I'd use a domain function to aggregate over fields if I don't know if they exist or not. Perhaps WGM could elaborate for us. I'm thinking you'd need to write code that makes use of a collection object with field names (one for each table), loop over one of them and attempt to retrieve that item from the other collection. If it's not there, it will raise an error that you trap and add the item to a string or array, depending on what you want to do.

Not the simplest of exercises and definitely not worth the time if the tables are not designed properly in the first place. I suspect there are a LOT of fields in both tables if you can't easily determine if Field1 of table A is in table B. Besides, two tables would normally not have more than one field that might be named the same because the data is related, and ideally they would not have the exact same name anyway. I would bet that these tables need to be broken up into other tables.

[Welshgasman]

I was thinking that the user just wanted to see if FieldA in Table1 existed in Table2, the value that is, not the actual field name?

[bastnpak]

yes exactly

[Micron]

NameField, PhNum is on A but not B.
- Address, CarMake is on both A and B

Maybe you are correct. I took that to mean is one field also a field in another table. I'm quite in the dark with most Excel functions, so maybe the post comment about Max can do what you're thinking.

[bastnpak]

table A - Address, CarMake, NameField, PhNum, City
Table B - Address, CarMake, LastName, State

I want to be able to make search field (Table A: Address) and see if Address is on table B

I can see how doing the dcount method would work, bc if the value turns out to be an error (i.e. the field is not there to count) then I know the field is not on table B.

when i am tryin the dcount method, I seem to be getting error messages.

[bastnpak]

I thought I had it...but it is still not finding FieldNM1 on Table B (even though I know it is there)

Sub dcount_test()
Dim dc As Variant

On Error Resume Next
    dc = DCount("FieldNM1", "Table B")

    If dc > 0 Then
        MsgBox ("there")
        Else: MsgBox ("missing")
    End If

End Sub

[Welshgasman]

Well you need criteria?
How else are you meant to specify what value you are looking for?

And you cannot possibly say what those error messages are?

I do not use spaces in object names, but try "[Table B]"

[madpiet]

table A - Address, CarMake, NameField, PhNum, City
Table B - Address, CarMake, LastName, State

I want to be able to make search field (Table A: Address) and see if Address is on table B

I can see how doing the dcount method would work, bc if the value turns out to be an error (i.e. the field is not there to count) then I know the field is not on table B.

when i am tryin the dcount method, I seem to be getting error messages.

Do you mean the values in the columns or the columns themselves? (like "does table B have {all / some / any} of the columns in Table A?" ) Important distinction.

If you're looking for values missing from one set (column) or the other, you can use an outer join and if you do a count, the ones missing will return 0.

[bastnpak]

I agree with not using spaces in table names. the actual name of the table it is searching is DC1_Before. I just said table B above for easier reference

I am not really worried about the actual values. I am just using the count to see if the column name from one table is on the second table. in the full code, I am making the field name a variable and working through them. and the doing one pop up message.

so far I can get everything to work but the Dcount.

if it helps, my full code is below

'**** Variables Named *****
Dim i As Integer
Dim db As Database


Dim TB_B As TableDef
Dim TB_C As TableDef


Dim TB_Bn As String
Dim TB_Cn As String


Dim TBF_B As String
Dim TBF_C As String


Dim dc As Variant
Dim Bf_message As String




'****Tables variables defined****
Set db = CurrentDb


Set TB_B = db.TableDefs("dc1_before") 
Set TB_C = db.TableDefs("dc1_current") 


TB_Bn = TB_B.Name
TB_Cn = TB_C.Name




'*****checking to see if before fields are on current table****
    For l = 0 To TB_B.Fields.Count - 1
        '***set field name to variable
        TBF_B = TB_B.Fields(l).Name
        
        'test if variable is on current table
        On Error Resume Next
            dc = DCount(" & TBF_B & ", " & TB_Cn & ")                      '<<<<this is the formula not working
        'update message to pop up after all fields checked
        If dc = Empty Then
            Bf_message = Bf_message & vbCrLf & TBF_B
        End If
    Next 'repeat for fields
    '****pop up message with results of column search****
    If Bf_message = Empty Then
            MsgBox ("All fields on " & TB_Bn & " and on " & TB_Cn)
        Else
            MsgBox ("The following fields are on " & TB_Bn & " but not " & TB_Cn & vbCrLf & Bf_message)
    End If

[madpiet]

Does that code work?

I started out thinking I would loop through each table's fields collection, but if you have to do this more than once, I'd just loop through the tables collection and grab the table name, and then through each table's fields collection and grab the TableName, FieldName and maybe FieldType and dump that to a table. Then I can just query that and be done. ([outer] join on table name, field name).

then I don't have to write any complex DAO to read tables and fields. I mean I can do it, but I've got more important things to do. <g>

[bastnpak]

the only part that is not working is the dcount. Even when I manually typed the field and table, it did not work.

I made a short tester code. The dcount is not working on this either. but 'component' is on the table DC1_Current.

could it be somthing like excel formulas that you need to say value(A1) vs just A1?

Dim dc As Variant

On Error Resume Next
    dc = DCount("Component", "DC1_Current")




    If dc > 0 Then
        MsgBox ("there")
        Else: MsgBox ("missing")
    End If

[Welshgasman]

I agree with not using spaces in table names. the actual name of the table it is searching is DC1_Before. I just said table B above for easier reference

I am not really worried about the actual values. I am just using the count to see if the column name from one table is on the second table. in the full code, I am making the field name a variable and working through them. and the doing one pop up message.

so far I can get everything to work but the Dcount.

if it helps, my full code is below

'**** Variables Named *****
Dim i As Integer
Dim db As Database


Dim TB_B As TableDef
Dim TB_C As TableDef


Dim TB_Bn As String
Dim TB_Cn As String


Dim TBF_B As String
Dim TBF_C As String


Dim dc As Variant
Dim Bf_message As String




'****Tables variables defined****
Set db = CurrentDb


Set TB_B = db.TableDefs("dc1_before") 
Set TB_C = db.TableDefs("dc1_current") 


TB_Bn = TB_B.Name
TB_Cn = TB_C.Name




'*****checking to see if before fields are on current table****
    For l = 0 To TB_B.Fields.Count - 1
        '***set field name to variable
        TBF_B = TB_B.Fields(l).Name
        
        'test if variable is on current table
        On Error Resume Next
            dc = DCount(" & TBF_B & ", " & TB_Cn & ")                      '<<<<this is the formula not working
        'update message to pop up after all fields checked
        If dc = Empty Then
            Bf_message = Bf_message & vbCrLf & TBF_B
        End If
    Next 'repeat for fields
    '****pop up message with results of column search****
    If Bf_message = Empty Then
            MsgBox ("All fields on " & TB_Bn & " and on " & TB_Cn)
        Else
            MsgBox ("The following fields are on " & TB_Bn & " but not " & TB_Cn & vbCrLf & Bf_message)
    End If

Well my offer is not going to work. I thought you were looking for values, and your response was exactly?
Quite rare to not know what fields are in what table I would have thought?
You could open second table as a recordset and iterate through the fields for the name?

Or you could use a deliberate error to see if it exists with DCount(). Error 2471 is returned when the field is not found in that table?

Do yourself a huge favour, debug.print the variables you build up and see if they are what you *think* they are, plus you can also see if it is correct.

tt="DayNo"
tt1="tblday"
? dcount(tt,tt1)
 31

If you are going to use tabledefs, you can just use the name?

[madpiet]

table A - Address, CarMake, NameField, PhNum, City
Table B - Address, CarMake, LastName, State

I want to be able to make search field (Table A: Address) and see if Address is on table B

SELECT a.Address, a.CarMake, a.NameField, a.PhNum, a.City, b.LastName, b.State
FROM TableA AS a
LEFT JOIN TableB AS b ON
(a.Address = b.Address AND a.CarMake = B.CarMake AND a.State = b.State)

?

it's kind of a mess because of the NameField... you could create a query that joined those:
FullName: [FirstName] & " " & [LastName]
and join on that...

that's about the best you're going to be able to do. Once you get those pieces sorted, you can basically use a LEFT JOIN and show all the records from the left side and the matches on the right.