Comparing fields from separate tables

[bigmeadow24]

I have two tables in Access that each have a field called "Claimnumber" - I know this probably isn't ideal, but they both are part of separate data reports and I'm trying to unify the information from the two. Basically, I want a calculated field to check if a claim number from Table A occurs in Table B. If it does, I'll grab a date from another table, and if it doesn't, I want to leave the field blank.

I've tried to use Dlookup and Dcount functions to do this, or even just an Iif statement, but nothing I do works - the D functions in particular are frustrating because they seem so simple and I still can't get them to work. Part of the problem with the D functions is that I just don't "get" the syntax, or the explanations of the syntax that are online. Of course, if there's a better/easier way to do this I'd gladly use that instead. Thanks!

 

[bigmeadow24]

Sorry, I didn't mean to be short. I'm a huge fan of Excel and formulas and whatnot, and every time I come across something like this that doesn't work exactly the same in Access I get frustrated - my default approach to problems like this is to use queries as a last resort...guess I need to change that.

Anyway, I think I got what you suggested to work, so again, I appreciate the help.

 

[Joe4]

Yes, the transition from Excel to Access can be a bit daunting at first - I went through it myself. Access is very powerful, and does certain things much better than Excel, but it is not as intuitive as Excel. There is a steeper learning curve.

Queries are one of the most powerful tools in Access. It allows you to look at and analyze your data in many different ways. But there are many different types of queries, and many different ways to approach a problem.

BTW, most of the SQL code I was posting can be created using the Query Builder (except for adding the Distinct part). It is often just easier to post the SQL code, and then you can change your Query Builder to SQL View and paste the code in there.

Access uses SQL code to run its queries. All the Query Builder really does is build the SQL code behind the scenes (which you can see by switiching to SQL view).