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!

 

[Joe4]

Why not just join the two tables in a query and pull off what you need?

You may need to use a Left Outer Join (if you are not familiar with that, see Access help files or this here: http://office.microsoft.com/en-us/a...n-outer-join-query-in-access-HA001034555.aspx).

 

[bigmeadow24]

Well, I need everything from Table A. I only want to know if it's in Table B because any claims in Table B came in to our system through a specific channel, and if that's the case then in those cases I need to calculate a "networkhours" type thing in a different manner than if the claims came in through other channels. Doesn't running the query you suggest just transfer the problem, in that now I have to figure out which claims are in both Table A and the query, instead of Table A and Table B?

 

[Joe4]

That is why I mentioned a Left Outer Join (did you read up on that)?

If you do a Left Outer Join from Table A to Table B, you will get ALL the records from Table A, regardless or whether or not they have a matching record in Table B.

If you then try to return a field from Table B in your query, for those Table A records that have matches, it will return that field value. For those that do not, it will return a Null value.

You can also add any criteria to the query, if for any reason you want to exclude certain records.

 

[bigmeadow24]

I did read that, and tried to do it myself. What is confusing me is that Table A has ~119,000 records, Table B has even less. After creating an outer left join on the two tables and running the query, I get ~123,500 records. Where are the extra ones coming from?

 

[Joe4]

That would seem to imply that you have a one-to-many relationship between table A and table B, meaning that the field you are joining on is probably not unique in table B.

An easy way to find out which records are in this situation is to run the following code (replacing your table/field name in):

SELECT [JoinField], Count([JoinField]) as RecCount
FROM [TableB]
GROUP BY [JoinField]
HAVING Count([JoinField])>1;

 

[bigmeadow24]

Ah, that would make sense. Unfortunately the claim number is definitely not unique, in either table. Even more unfortunately, there aren't any unique fields the two tables have in common.

That's mostly why I was hoping I could use the Dcount function. I just want to know if there's a match - I don't need anything listed out or extracted or whatever.

 

[Joe4]

Making it an Aggregate Query will weed out the duplicates, and tell you the counts of how many matches you have for each, i.e.

SELECT TableA.ClaimNumber, Count(TableB.ClaimNumber) AS CountOfClaimNumber
FROM TableA LEFT JOIN TableB ON TableA.ClaimNumber = TableB.ClaimNumber
GROUP BY TableA.ClaimNumber;

 

[bigmeadow24]

Look, I appreciate the help, but again, I don't need a list of claims that match, or the count of how often they match, or anything like that. The data set I'm working with is huge, and every query I run adds time onto an already lengthy process I'm going through. All I want is to know if a claim number in Table A is also in Table B. If there's a formula like Dcount or Dlookup or something else that can get me there, I would strongly prefer that. If nothing like that exists, then so be it, and I'll make do in Excel for this issue.

 

[Joe4]

All I want is to know if a claim number in Table A is also in Table B.

It does exist, and I am giving it to you! It is fairly straightforward and simple. In the previous query, all the claims that have 0 for the counts don't have matches, all the other ones do.

If you don't like numbers and prefer the words "MATCH" and "NO MATCH" instead, then use this version:

SELECT DISTINCT TableA.ClaimNumber, IIf(NZ([TableB]![ClaimNumber],"No Match")="No Match","No Match","Match") AS MatchCheck
FROM TableA LEFT JOIN TableB ON TableA.ClaimNumber = TableB.ClaimNumber;

In the future, might I suggest instead of just trying to describe what you want verbally, which is often confusing and open to interpretation, also post a few example of the data you are working with and your expected output. Often times, examples do a much better job of showing exactly what it is you are looking for.