Use an If statement to fill in a field

[cvia]

Hello All!
I am having a problem, basically because of my lack of knowledge of Access (which I one day hope to rectify! ), dealing with using an if statement to fill in a field. Here is the senario:
I have two tables. One table, titled MainCategory has the name of the category, the Category ID field, MainID, and an empty field titled after (because I couldn't think up a better name). The second table that I would like to deal with is titled SubCategory. It has three fields: SubID, SubCategory, and main. Main is the foreign key that points to MainID in MainCategory. I hope this makes sense so far.
What I would like to do is, for each MainID in the MainCategory table, check to see if there are any instances of it in SubCategory in the main field. If there are, I would like to put the value "C" in the after field. If there are not, I would like the value "P" to go in it.
I am familiar with writing queries, but not so familiar with the query tool in Access. I am also familiar with pretty many programming languages, however I have very little knowledge of VB. So I am in some sort of quandry here! I am sure that it is quite possible to do this with Cold Fusion or ASP, however, I think it may be cleaner and easier to access the process using a module or query or whatever in Access.
Thanks so much!
Christine

 

[Rob.Mills]

I would use this:

iif(isnull(DLookUp("[SubID]","[SubCategory]","[Main]=" & [MainID])),"P","C")

 

[ColinEssex]

Hi

So to condense what you said.

If records in tableA = records in tableB then put a C against the record in tableA

Else

Put a P against the record in tableA

Is that the general idea?

Col

 

[cvia]

Colin: that would be pretty much it.
Rob: the answer you posted makes sense, however, where do I run this from to make it work? How is it specified that the result must go in the "after" field?
Thanks so much!
Christine

 

[Rob.Mills]

Are you trying to do this in a query or in a form? I was thinking that you wanted to see the results in a query. Am I wrong about this?

 

[cvia]

I guess what I am trying to do is populate the database. I don't care how it is done. If I do it in a query, where do I put the expression?
Sorry for the ignorance!
Christine

 

[Rob.Mills]

It sounds to me like you should set up a query that can test this regularly. If users are continually adding data then the value will be changing often and you'll have to run updates.

You can put that statement into a field in a query. Then everytime you run the query it will give you the most up to date data.

Place the iif statement I wrote in a column in the query design. Same place you would put the name of a field in the table. When you tab out of that column it will add "Expr1:" in front.

Then run the query.