We help IT Professionals succeed at work.

Identify match in two tables

SteveL13
SteveL13 asked
on
162 Views
Last Modified: 2014-12-04
I have two tables.  In one of them there is a field named "Property ID".  In the other one there is a field named "Account No".   Both field are text fields.

Also I have a form bound to the 1st table.  If there is a matching value in the 2nd table ("Property ID" = "Account ID") then I want a label to appear on the form indicating that there is a match.

How can I do this?
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Change "I have a form bound to the 1st table" to "I have a form bound on a query based on the 1st table", and that query can be:

SELECT Table1.*, IIf(IsNull([Table2].[Account Number]),"","Match") AS MyLabel
FROM Table1 LEFT JOIN Table2 ON Table1.PropertyID = Table2.[Account Number];

Open in new window

Author

Commented:
Where do I put this code?  I tried in the on current event of the form but that doesn't work.
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
In the "Record Source".

You will need to modify Table1 and Table2 names. MyLabel is the new field which will say "Match" if there is a match, and nothing if there isn't.

Author

Commented:
But the record source of the form is a table.
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
That's why I said:

Change "I have a form bound to the 1st table" to "I have a form bound on a query based on the 1st table"

It's simplest to add another column to the 1st table.
Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
As an aside - the recordsources for forms and reports should almost always be queries.  That allows you to:
1. Limit the rows/columns fetched from the database by using a where clause
2. Avoid the use of domain functions to "look up" values
3. Provide a permanent sort order for the recordset so rows are ordered in a predictable sequence
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.