Create Yes/No field in New table if fields match

[Waubain]

I receive a file with individual charges that were taken out of my budget and the county and state where the charge originated. This is in tblCharges with 4 fields: AutoID(primary), Charge, County, State. I have a separate table tblCatchment with fields County, State (double primary) that fall in my budget region. Charges from other County, State areas are often placed on my file incorrectly (I can't fix that problem).



I want to create a query that makes a new table with a new field named Match. If the County and State in tblCharges = County and State in tblCatchment then Match = True, Else=False. I then reconcile the Falses.

I can do this by 2 queries, but I am trying to figure out how to do this in one step because of other processes that follow this step.

Thank you.

[boblarson]

You don't need to create a table for this. Just use the query. The query can be the basis of whatever it is you are doing that you would normally use the table for.

[Waubain]

Sorry, I misspoke. Yes, I do not plan on making a new table only a query, but that still does not explain how to create a new Yes or no variable. If my relationship is a one to many on both County and State all I get from the select query are the matches. I can run a separate query for County,State that don't match. But this is not what I am hoping for.

What I am trying to do is one query that list all variables in tblCharges with a new variable named Match. Match is propagated with a Yes if County,State match and no if they don't match.

[Waubain]

I found a solution. I added all of the variables from the tblCharges into the query. I added the following statement in a separate column.
Expr1: IIf([tblCharges]![state]=[tblCatchment]![state] And [tblCharges]![county]=[tblCatchment]![county],"yes","no")

The relationship on the tblCharges has to be set to all and only records that match in tblCatchment.